cursor_sharing=similar 與直方圖

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE很多MOS文件和書籍都描述，cursor_sharing=similar時，如果謂詞列上直方圖，那麼ORACLE將認為是unsafe的，從而重新硬解析SQL語句，但今天遇到1個問題，發現其和直方圖無必然關係，而是和謂詞列上是否有統計資訊直接相關

測試1：使用method_opt => 'for all columns size 1'收集了統計資訊

SQL> @traceon 10046 12 fl2

[MESSAGE] EVENT:10046 LEVEL:12

[MESSAGE] GREP:fl2 TRACEFILE:/oracle10/admin/SOURCE10/udump/source10_ora_15709.trc

SQL> SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a;

Enter value for a: 1

old 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>1

oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

16923

1 row selected.

SQL> /

Enter value for a: 2

old 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>2

oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

15384

1 row selected.

SQL> /

Enter value for a: 3

old 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>3

oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

13845

1 row selected.

SQL> /

Enter value for a: 4

old 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq2 */COUNT(*) FROM test WHERE ID>4

oacflg=10 fl2=0300 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

12306

1 row selected.

SQL> SELECT parse_calls,executions,loads,version_count FROM v$sqlstats WHERE sql_text LIKE '%zq2%'

2 ;

PARSE_CALLS EXECUTIONS LOADS VERSION_COUNT

----------- ---------- ---------- -------------

4 4 4 4

SQL> SELECT sql_id,plan_hash_value FROM v$sql WHERE sql_text LIKE '%zq2%';

SQL_ID PLAN_HASH_VALUE

------------- ---------------

fph8hajpxnj20 3508397080

可以看到，SQL執行了4次，解析了4次，VERSION_COUNT為4

遊標不能重用的原因為：0x0300，按照MOS文件

High Version Count with CURSOR_SHARING = SIMILAR or FORCE [ID 261020.1]

if the field named oacflg2 or flg2 has a value 300 this means the literal was replaces (100) and the literal is unsafe (200).

ORACLE認為這種繫結是不安全的（unsafe），導致繫結失敗，使得本來應該共享的遊標沒有共享，從而導致硬解析

Unsafe Literals or Peeked Bind Variables [ID 377847.1]

Note that, prior to 11g, unsafe literals are NOT covered by 'bind mismatch' in V$SQL_SHARED_CURSOR as this is for user bind metadata mismatches.

因為cursor_sharing=similar，SQL的字面值在替換後相同，其是1個父遊標（對應替換後的SQL語句），下帶N個子遊標（對應subheap 0）

這裡4次執行的執行計劃是相同的，硬解析是完全沒有必要的

一般來說，在cursor_sharing=similar時，當查詢相關的列在存在直方圖的情況下，並且運算條件非“=”的情況下，ORACLE會認為是unsafe的。

目前的情況是，已經未收集直方圖，DBA_HISTOGRAMS中的2條記錄是在收集列統計資訊的情況下的最小值（2條資料，一條代表min value，一條代表max value）

測試2：使用DBMS_STATS.DELETE_COLUMN_STATS(NULL,'TEST','id')刪除列的統計資訊

SQL> @traceon 10046 12 fl2

[MESSAGE] EVENT:10046 LEVEL:12

[MESSAGE] GREP:fl2 TRACEFILE:/oracle10/admin/SOURCE10/udump/source10_ora_16394.trc

SQL> SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a;

Enter value for a: 1

old 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>1

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

16923

1 row selected.

SQL> /

Enter value for a: 2

old 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>2

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

15384

1 row selected.

SQL> /

Enter value for a: 3

old 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>3

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

13845

1 row selected.

SQL> /

Enter value for a: 4

old 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq4 */COUNT(*) FROM test WHERE ID>4

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

12306

SQL> SELECT column_name,endpoint_number,endpoint_value

2 FROM DBA_HISTOGRAMS

3 WHERE TABLE_NAME = 'TEST'

4 AND WNER = 'SYS'

5 ORDER BY 2, 3;

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

NAME 0 5.0563E+35

NAME 1 5.0563E+35

SQL> SELECT parse_calls,executions,loads,version_count FROM v$sqlstats WHERE sql_text LIKE '%zq4%';

PARSE_CALLS EXECUTIONS LOADS VERSION_COUNT

----------- ---------- ---------- -------------

4 4 1 1

SQL> SELECT sql_id,plan_hash_value FROM v$sql WHERE sql_text LIKE '%zq4%';

SQL_ID PLAN_HASH_VALUE

------------- ---------------

97dm6rb1bf8ah 2901380809

可以看到，當刪除了ID列統計資訊，以徹底刪除ID列的直方圖後，SQL能如期望中的共享遊標

根據MOS文件

QUERIES HAVING HUGE VERSION COUNTS WHEN CURSOR_SHARING=SIMILAR [ID 731468.1]

Note that the usual reason for seeing high version counts with CURSOR_SHARING=SIMILAR is that there is histogram data on some column/s involved in predicates where the value of the

replaced literal in the predicate varies a lot (eg: on a primary key columns).

In this case we only share the cursor if the values are identical.

Be sure to avoid histogram data on such columns.

Unsafe Literals or Peeked Bind Variables [ID 377847.1]

With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of '=' or '!='

OR

Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, =, <=, LIKE) are unsafe).

這裡的描述具有迷惑性，直方圖資訊的來源是sys.histgrm$，min/max value的來源為sys.Hist_Head$，這2張表都是DBA_HISTOGRAMS的基表，按照描述理解，刪除直方圖資訊（sys.histgrm$）後，SQL就應該認為是safe的，應該共享遊標，而實際情況下，其參考的是sys.Hist_Head$中的資料（sys.Hist_Head$為dba_tab_cols的基表），也就是列統計資料。

測試3：重新收集統計資訊，包括直方圖，然後清除sys.Hist_Head$中的列統計

收集直方圖，手工刪除sys.Hist_Head$中的資料（列統計）（非客戶測試環境）

dbms_stats.gather_table_stats(NULL,'TEST',method_opt => 'for all columns',cascade => TRUE);

DELETE sys.Hist_Head$ WHERE obj#=14023

SQL> @traceon 10046 12 fl2

[MESSAGE] EVENT:10046 LEVEL:12

[MESSAGE] GREP:fl2 TRACEFILE:/oracle10/admin/SOURCE10/udump/source10_ora_16835.trc

SQL> SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a;

Enter value for a: 1

old 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>1

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

16923

1 row selected.

SQL> /

Enter value for a: 2

old 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>2

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

15384

1 row selected.

SQL> /

Enter value for a: 3

old 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>3

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

13845

1 row selected.

SQL> /

Enter value for a: 4

old 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>&a

new 1: SELECT /*+zq6 */COUNT(*) FROM test WHERE ID>4

oacflg=10 fl2=0100 frm=00 csi=00 siz=24 ff=0

COUNT(*)

----------

12306

1 row selected.

SQL> SELECT column_name,endpoint_number,endpoint_value

2 FROM DBA_HISTOGRAMS

3 WHERE TABLE_NAME = 'TEST'

4 AND WNER = 'SYS'

5 AND column_name='ID'

6 ORDER BY 2, 3;

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

ID 1538 0

ID 3077 1

ID 4616 2

ID 6155 3

ID 7694 4

ID 9233 5

ID 10772 6

ID 12310 7

ID 13848 8

ID 15386 9

ID 16924 10

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ --------------- --------------

ID 18462 11

ID 20000 12

SQL> SELECT A.LOW_VALUE, A.HIGH_VALUE, A.AVG_COL_LEN, A.HISTOGRAM

2 FROM DBA_TAB_COLUMNS A

3 WHERE TABLE_NAME = 'TEST'

4 AND WNER = 'SYS';

LOW_VALUE HIGH_VALUE AVG_COL_LEN HISTOGRAM

-------------------- -------------------- ----------- ---------------

NONE

是否認為SQL的變數是unsafe的，很大一部分是參考列統計

ORACLE未提供不收集列統計資訊的子句，只有method_opt選項，可以明確指定一個在查詢謂詞中不會使用的列給他分析，避免其對其他列生成列統計資訊，但是，列統計資訊（如density）是CBO成本計算的重要來源，缺失列的統計可能導致執行計劃突變

還可以修改cursor_sharing = force，但force可能導致一些原來值傾斜的列執行計劃不正常，修改前應該先檢查SQL的子游標的執行計劃是否相同：

select hash_value,plan_hash_value,count(*) from v$sql

where sql_text like '%:"SYS_B_0"%'

group by hash_value,plan_hash_value;

如果全部相同，才能修改。

如果部分不同，可以修改後，對這些特定的SQL使用cursor_sharing_exact 提示或者opt_param提示

cursor_sharing = similar將在12G被廢除

ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-676570/，如需轉載，請註明出處，否則將追究法律責任。

cursor_sharing=similar 與 直方圖

相關文章

cursor_sharing=similar 與直方圖