[20140802]cursor_sharing=similar.txt

[20140802]cursor_sharing=similar.txt

--晚上看了在 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
--可見沒有建立直方圖沒有問題。