20170330cursor_sharing=force改变显示宽度

[20170330]参数cursor_sharing=force改变显示宽度.txt

--//前几天遇到的问题,链接http://www.itpub.net/thread-2085766-1-1.html
--//开始以为是11.2.0.3特有的问题,下班后才知道这个问题可能来自参数cursor_sharing=force,实际上我扫描alert文件才发现是这个
--//问题.感谢zergduan的提醒,应该从参数之类入手检查.实际上当时看到这个修改马上明白这个问题(我前几天修改了这个参数全局有效
--//),以前有blog介绍.
--//alter system set cursor_sharing=force scope=memory;
--//测试说明问题:

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> select to_char(sysdate,'YYYY') from dual ;
TO_C
----
2017

2.如果修改参数:
SCOTT@test01p> alter session set cursor_sharing=force ;
Session altered.

SCOTT@test01p> select to_char(sysdate,'YYYY') from dual ;
TO_C
----
2017

SCOTT@test01p> Select to_char(sysdate,'YYYY') from dual ;
TO_CHAR(SYSDATE,'YYYY')
---------------------------------------------------------------------------
2017

--//前面第一次宽度没有改变,是因为光标已经缓存了,是软解析.
--//顺便修改一个字符s.可以发现宽度发生了改变.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4mdjy1yx5tzxh, child number 0
-------------------------------------
Select to_char(sysdate,:"SYS_B_0") from dual
Plan hash value: 1388734953
------------------------------------------------------------------
| Id  | Operation        | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DUAL@SEL$1

--//在cursor_sharing=force 的情况下,语句转换成了Select to_char(sysdate,:"SYS_B_0") from dual.

SCOTT@test01p> select to_char(sysdate,'YYYY') from dual ;
TO_C
----
2017

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4t1qqsdgws3cr, child number 0
-------------------------------------
select to_char(sysdate,'YYYY') from dual
Plan hash value: 1388734953
------------------------------------------------------------------
| Id  | Operation        | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DUAL@SEL$1


--//因为前面执行1次,语句软解析,保持原样.

SCOTT@test01p> alter session set optimizer_index_cost_adj=1;
Session altered.

SCOTT@test01p> select to_char(sysdate,'YYYY') from dual ;
TO_C
----
2017

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4t1qqsdgws3cr, child number 1
-------------------------------------
select to_char(sysdate,'YYYY') from dual

Plan hash value: 1388734953

------------------------------------------------------------------
| Id  | Operation        | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DUAL@SEL$1

--//改变了一个参数optimizer_index_cost_adj,形成了子光标,但是一旦这个语句存在光标,oracle不会转换.