[20140802]cursor_sharing=similar.txt
--晚上看了http://www.dbaxiaoyu.com/archives/2248,在 cursor_sharing='similar'的情况下,会出现N多子光标(如果查询字段有直方
--图的情况下).实际上oracle在以后的版本会淘汰调cursor_sharing=similar的情况.
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> alter session set cursor_sharing='similar';
Session altered.
SCOTT@test01p> create table t as select * from dba_objects;
Table created.
SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
------------------- ---------------
OBJECT_ID NONE
--并没有建立直方图在字段OBJECT_ID.
select /*TEST*/ object_name from t where object_id=10;
select /*TEST*/ object_name from t where object_id=11;
select /*TEST*/ object_name from t where object_id=12;
SCOTT@test01p> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0" 1 000007FF5AFC6B98
--version_count=1
SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 for columns object_id size 254',no_invalidate=>false);
PL/SQL procedure successfully completed.
SCOTT@test01p> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
-------------------- ---------------
OBJECT_ID HYBRID
--HYBRID 第1次看到????
SCOTT@test01p> alter system flush SHARED_POOL;
System altered.
select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;
SCOTT@test01p> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0" 1 000007FF59630E70
-- 12c下cursor_sharing='similar',并没有出现子光标并不奇怪,说不定oracle已经淘汰了cursor_sharing='similar'这种情况,实际上
-- 我猜测使用的是cursor_sharing='force'.
-- 在11g下重复测试:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter session set cursor_sharing='similar';
Session altered.
SCOTT@test> create table t as select * from dba_objects;
Table created.
SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.
SCOTT@test> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
-------------------- ---------------
OBJECT_ID NONE
select /*TEST*/ object_name from t where object_id=10;
select /*TEST*/ object_name from t where object_id=11;
select /*TEST*/ object_name from t where object_id=12;
SCOTT@test> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0" 1 00000000B136C1D0
--version_count=1
SCOTT@test> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 for columns object_id size 254',no_invalidate=>false);
PL/SQL procedure successfully completed.
SCOTT@test> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
-------------------- ---------------
OBJECT_ID HEIGHT BALANCED
--建立了直方图。
select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;
SCOTT@test> select sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_TEXT VERSION_COUNT ADDRESS
------------------------------------------------------------- ------------- ----------------
select /*TEST*/ object_name from t where object_id=:"SYS_B_0" 1 00000000B1AA9010
-- 11.2.0.3版本cursor_sharing='similar',并没有出现子光标并不奇怪,说不定oracle已经淘汰了cursor_sharing='similar'这种情况,实际上
-- 我猜测使用的是cursor_sharing='force'.作者出现问题的是11.2.0.1版本。估计oracle已经淘汰cursor_sharing='similar'的情况。
-- 再多做一些测试，在10.2.0.4下重复测试:
10G> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
10G> alter session set cursor_sharing='similar';
Session altered.
10G> create table t as select * from dba_objects;
Table created.
10G> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 for columns object_id size 254',no_invalidate=>false);
PL/SQL procedure successfully completed.
10G> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
-------------------- ---------------
OBJECT_ID HEIGHT BALANCED
--建立了直方图。
select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;
10G> select sql_id,sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_ID SQL_TEXT VERSION_COUNT ADDRESS
------------- ------------------------------------------------------------- ------------- ----------------
17a488u6rzrjr select /*TEST*/ object_name from t where object_id=:"SYS_B_0" 3 000000007C993A28
--可以发现出现了子光标，version_COUNT=3.如果大量的这些文字变量执行，会产生了大量的子光标。
10G> @share 17a488u6rzrjr
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''17a488u6rzrjr''',
SQL_TEXT = select /*TEST*/ object_name from t where object_id=:"SYS_B_0"
SQL_ID = 17a488u6rzrjr
ADDRESS = 000000007C993A28
CHILD_ADDRESS = 00000000772D5918
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select /*TEST*/ object_name from t where object_id=:"SYS_B_0"
SQL_ID = 17a488u6rzrjr
ADDRESS = 000000007C993A28
CHILD_ADDRESS = 0000000077164660
CHILD_NUMBER = 1
--------------------------------------------------
SQL_TEXT = select /*TEST*/ object_name from t where object_id=:"SYS_B_0"
SQL_ID = 17a488u6rzrjr
ADDRESS = 000000007C993A28
CHILD_ADDRESS = 000000007C8CD510
CHILD_NUMBER = 2
--------------------------------------------------
PL/SQL procedure successfully completed.
--奇怪！并没有看见说明不能共享的原因。
--总结：
至少11.2.0.3已经淘汰了cursor_sharing='similar'，实际上使用的是force,11G下force+ACS组合很好地解决以前的问题。避免这种情况最佳方法是：
1. 很好合理的使用绑定变量。我一直认为这个是衡量一个项目好坏的一个重要的标准。
2.在需要的字段建立直方图，而不是由oracle的自动统计收集来分析数据，或者修改缺省method_opt参数。
==补充测试：10.2.0.4下：
10G> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
10G> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1 ',no_invalidate=>false);
PL/SQL procedure successfully completed.
10G> select column_name,histogram from DBA_tab_cols where owner=user and table_name='T' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
-------------------- ---------------
OBJECT_ID NONE
select /*TEST*/ object_name from t where object_id=210;
select /*TEST*/ object_name from t where object_id=211;
select /*TEST*/ object_name from t where object_id=212;
10G> select sql_id,sql_text,version_count,address from v$sqlarea where sql_text like 'select /*TEST*/ object_name from t%';
SQL_ID SQL_TEXT VERSION_COUNT ADDRESS
------------- ------------------------------------------------------------ ------------- ----------------
17a488u6rzrjr select /*TEST*/ object_name from t where object_id=:"SYS_B_0" 1 000000007EAF6A60
--可见没有建立直方图没有问题。