[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不会转换.