綁定變量窺視是否開啟和直方圖關(guān)系

如果有直方圖信息，而且綁定變量窺視也開啟了，這里我們很好理解，在oracle 9i和oracle 10g時(shí)，sql第一次執(zhí)行會(huì)將綁定變量的值帶入到sql中，然后根據(jù)直方圖等統(tǒng)計(jì)信息來評(píng)估合理的執(zhí)行計(jì)劃，在同樣的sql下次執(zhí)行時(shí)，會(huì)直接使用之前的執(zhí)行計(jì)劃，這個(gè)也就是我

如果有直方圖信息，而且綁定變量窺視也開啟了，這里我們很好理解，在oracle 9i和oracle 10g時(shí)，sql第一次執(zhí)行會(huì)將綁定變量的值帶入到sql中，然后根據(jù)直方圖等統(tǒng)計(jì)信息來評(píng)估合理的執(zhí)行計(jì)劃，在同樣的sql下次執(zhí)行時(shí)，會(huì)直接使用之前的執(zhí)行計(jì)劃，這個(gè)也就是我們經(jīng)常所接觸的綁定變量窺視。

1） 開啟了綁定變量窺視，收集該列的直方圖：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> alter system set "_optim_peek_user_binds"=true;

System altered.

SQL> create table t002 as select * from dba_objects;

Table created.

SQL> update t002 set object_id=100 where rownum<50000;

49999 rows updated.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T002',met
hod_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='T002';

NUM_ROWS BLOCKS
---------- ----------
50328 712

SQL> select num_distinct,density,num_nulls from user_tab_columns where table_nam
e='T002' and column_name='OBJECT_ID';

NUM_DISTINCT DENSITY NUM_NULLS
------------ ---------- ----------
29 9.8243E-06 0

SQL> variable x number;
SQL> exec : x:=100;

PL/SQL procedure successfully completed.

SQL> select count(*) from t002 where object_id=:x;

COUNT(*)
----------
49999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 4yqsqnawx85ty, child number 0
-------------------------------------
select count(*) from t002 where object_id=:x

Plan hash value: 3014849763

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 158 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T002 | 50065 | 195K| 158 (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T002@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

30 rows selected.

SQL> exec : x:=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from t002 where object_id=:x;

COUNT(*)
----------
0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 4yqsqnawx85ty, child number 0
-------------------------------------
select count(*) from t002 where object_id=:x

Plan hash value: 3014849763

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 158 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T002 | 50065 | 195K| 158 (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T002@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

30 rows selected.

這里比較好理解，由于開啟了綁定變量窺視，然后該列又有直方圖信息，所以第一次執(zhí)行時(shí)會(huì)把具體值帶入，然后根據(jù)具體值的直方圖信息來評(píng)估rows，這里的通過謂詞過濾后估算返回的rows是50065，關(guān)于有直方圖的情況下估算rows的方式，非常復(fù)雜，小魚自己也沒有過多的深究，后面有機(jī)會(huì)整理相應(yīng)的文章來分享。

2）如果開啟了綁定變量窺視，但是沒有收集直方圖：
SQL> alter system set "_optim_peek_user_binds"=true;

System altered.

SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T002',met
hod_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL>variable y number;
SQL>exec : y:=100

PL/SQL procedure successfully completed

SQL> select count(*) from t002 where object_id=:y;

COUNT(*)
----------
49999

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

SQL_ID 86ngbvm962n14, child number 0
-------------------------------------
select count(*) from t002 where object_id=:y

Plan hash value: 3014849763

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 158 (100)|
| 1 | SORT AGGREGATE | | 1 | 4 | |
|* 2 | TABLE ACCESS FULL| T002 | 1290 | 5160 | 158 (1)| 00:00:02
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T002@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=:Y)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT(*)[22]

30 rows selected.

這里我們來算算這個(gè)rows 1290是如何估算出來的

這里介紹最簡單的如何計(jì)算rows，selectivity、density的公式：（下列計(jì)算公式在該列沒有直方圖前提下）

小魚之前介紹cbo優(yōu)化器的基本知識(shí)里面提過一個(gè)selectivity可選擇率這個(gè)概念

可選擇率selectivity=釋放指定謂詞條件返回結(jié)果集的記錄數(shù)/未施加任何謂詞條件的原始結(jié)果集的記錄數(shù)，可選擇率越大，那么cbo估算返回的rows也越大。

那么集的勢rows=selectivity*未施加任何謂詞條件的原始結(jié)果集的記錄數(shù)

那么這個(gè)可選擇率selectivity如何計(jì)算了，在列的統(tǒng)計(jì)信息中num_nulls為0時(shí)，selectivity=1/num_distinct

SQL> select num_distinct,num_nulls,density,num_buckets from dba_tab_columns wher
e table_name='T002' and column_name='OBJECT_ID';

NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS
------------ ---------- ---------- -----------
39 0 .025641026 1

SQL> select num_rows from user_tables where table_name='T002';

NUM_ROWS
----------
50328

SQL> select 1/39 from dual;

1/39
----------
.025641026

SQL> select 50328*1/39 from dual;

50328*1/39
----------
1290.46154

這里我們通過統(tǒng)計(jì)信息發(fā)現(xiàn)計(jì)算而來的1290跟執(zhí)行計(jì)劃的rows 1290完全一致

列沒有直方圖，而且num_nulls為0時(shí):
Selectivity_without_null=(1/num_distinct),也就是列的統(tǒng)計(jì)信息中num_nulls為0時(shí)，列的選擇率是1/num_distinct，此時(shí)density也是等于1/num_distinct

列沒有直方圖，但是num_nulls又不為0時(shí):
selectivity_with_null=(1/num_distinct)*(num_rows-num_nulls)/(num_rows)，而density還是等于1/num_distinct

對(duì)于第一點(diǎn)num_nulls為0,列沒有直方圖，selectivity選擇率和density上面已經(jīng)進(jìn)行了驗(yàn)證，下面稍微擴(kuò)展點(diǎn)，來驗(yàn)證下num_nulls不為0，沒有直方圖時(shí)，可選擇率selectivity、rows和density關(guān)系

SQL> update t002 set object_id=null where rownum<1000;
SQL> commit;
SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T002',method_opt=>'for all columns size 1');

SQL> select NUM_ROWS, --表中的記錄數(shù)
2 BLOCKS, --表中數(shù)據(jù)所占的數(shù)據(jù)塊數(shù)
3 EMPTY_BLOCKS, --表中的空塊數(shù)
4 AVG_SPACE, --數(shù)據(jù)塊中平均的使用空間
5 CHAIN_CNT, --表中行連接和行遷移的數(shù)量
6 AVG_ROW_LEN --每條記錄的平均長度
7 from dba_tables
8 where owner=&owner and table_name=&tabname;
Enter value for owner: 'XIAOYU'
Enter value for tabname: 'T002'
old 8: where owner=&owner and table_name=&tabname
new 8: where owner='XIAOYU' and table_name='T002'

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
50328 712 0 0 0 91

SQL> SELECT column_name,
2 num_distinct,
3 num_nulls,
4 density,
5 num_buckets,
6 low_value,
7 high_value
8 FROM dba_tab_col_statistics
9 WHERE owner = &owner
10 AND table_name = &tabname
11 AND column_name IN (&col1);
Enter value for owner: 'XIAOYU'
old 9: WHERE owner = &owner
new 9: WHERE owner = 'XIAOYU'
Enter value for tabname: 'T002'
old 10: AND table_name = &tabname
new 10: AND table_name = 'T002'
Enter value for col1: 'OBJECT_ID'
old 11: AND column_name IN (&col1)
new 11: AND column_name IN ('OBJECT_ID')

COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS LOW_VALUE HIGH_VALUE
------------------------------ ------------ ---------- ---------- ----------- --
---------------------------- ------------------------------
OBJECT_ID 44 943 .022727273 1 C2
02 C3064A3F

SQL> select count(*) from t002 where object_Id=100;

COUNT(*)
----------
49000

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 3yaw02xfsf7c8, child number 0
-------------------------------------
select count(*) from t002 where object_Id=100

Plan hash value: 3014849763

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 158 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T002 | 1122 | 3366 | 158 (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=100)


19 rows selected.

來算算選擇率selectivity_with_null=(1/num_distinct)*(num_rows-num_nulls)/(num_rows)

SQL> select (50328-943)/50328*1/44 from dual;

(50328-943)/50328*1/44
----------------------
.02230143

算算density=1/num_distinct也跟dba_tab_columns中值一樣

SQL> select 1/44 from dual;

1/44
----------
.022727273

根據(jù)選擇率selectivity_with_null跟執(zhí)行計(jì)劃的預(yù)估的rows也是相符的，這個(gè)地方要注意rows是嚴(yán)格根據(jù)num_rows*selectivity的，而不是num_rows*density，因?yàn)樵跊]直方圖時(shí)density計(jì)算方式始終是1/num_distinct

SQL> select 0.02230143* 50328 from dual;

0.02230143*50328
----------------
1122.38637

細(xì)心點(diǎn)我們發(fā)覺上面計(jì)算selectivity的方式可以直接簡化為：
（1/num_distinct）*(num_rows-num_nulls)/num_rows,如果num_null為0， 此時(shí)（1/num_distinct）*(num_rows-num_nulls)/num_rows就直接等于1/num_distinct

3）關(guān)閉綁定變量窺視，也不收集直方圖：
SQL> create table t003 as select * from dba_objects;

Table created.

SQL> alter system set "_optim_peek_user_binds"=false;

System altered.

SQL> update t003 set object_id=10000 where object_id<50000;

48524 rows updated.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T003',method
_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='T003';

NUM_ROWS
----------
50325

SQL> select num_distinct,density,num_nulls from user_tab_columns where table_nam
e='T003' and column_name='OBJECT_ID';

NUM_DISTINCT DENSITY NUM_NULLS
------------ ---------- ----------
184 .005434783 0

SQL> variable a number;
SQL> exec : a:=10000;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from t003 where object_id=:a;

COUNT(OBJECT_NAME)
------------------
48524

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID dq92pjhyfrg1n, child number 0
-------------------------------------
select count(object_name) from t003 where object_id=:a

Plan hash value: 3872854764

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)| |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
|* 2 | TABLE ACCESS FULL| T003 | 274 | 7946 | 154 (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T003@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:A)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT("OBJECT_NAME")[22]
2 - "OBJECT_NAME"[VARCHAR2,128]

31 rows selected.

SQL> exec : a:=10;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from t003 where object_id=:a;

COUNT(OBJECT_NAME)
------------------
0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID dq92pjhyfrg1n, child number 0
-------------------------------------
select count(object_name) from t003 where object_id=:a

Plan hash value: 3872854764

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)| |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
|* 2 | TABLE ACCESS FULL| T003 | 274 | 7946 | 154 (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T003@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:A)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT("OBJECT_NAME")[22]
2 - "OBJECT_NAME"[VARCHAR2,128]

31 rows selected.

關(guān)閉綁定變量，沒有直方圖時(shí):
可選擇率selectivity=1/num_distinct*(num_rows-num_nulls)/num_rows

SQL> select 50325*1/184 from dual;

50325*1/184
-----------
273.505435

SQL> select 50325*0.005434783 from dual;

50325*0.005434783
-----------------
273.505454

特別注意點(diǎn)：
而且我們查看v$sql時(shí)發(fā)現(xiàn)，關(guān)閉綁定變量窺視并不是不共享sql，而是說sql第一次執(zhí)行時(shí)不帶入具體值，這個(gè)如果大家沒有測試過可能會(huì)想當(dāng)然的以為是關(guān)閉綁定變量窺視是不共享綁定的sql語句，其實(shí)綁定變量窺視真正含義是sql第一次執(zhí)行時(shí)帶入綁定具體值，而如果關(guān)閉了綁定變量窺視，則不會(huì)帶入具體值，那么由于不帶入具體值，直方圖也不會(huì)影響selectivity計(jì)算

SQL> select child_number,sql_id,sql_text from v$sql where sql_text like 'select
count(object_name) from t003 where object_id=:a%';

CHILD_NUMBER SQL_ID
------------ -------------
SQL_TEXT
--------------------------------------------------------------------------------
------------------------------------------------------------
0 dq92pjhyfrg1n
select count(object_name) from t003 where object_id=:a

4）關(guān)閉綁定變量窺視，收集直方圖：
SQL> alter system set "_optim_peek_user_binds"=false;

System altered.

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T003',method
_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='T003';

NUM_ROWS
----------
50325

SQL> select num_distinct,density,num_nulls from user_tab_columns where table_nam
e='T003' and column_name='OBJECT_ID';

NUM_DISTINCT DENSITY NUM_NULLS
------------ ---------- ----------
197 .000010034 0

SQL> variable b number;
SQL> exec : b:=10000;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from t003 where object_id=:b;

COUNT(OBJECT_NAME)
------------------
48524

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 9qunh3ms4kjzw, child number 0
-------------------------------------
select count(object_name) from t003 where object_id=:b

Plan hash value: 3872854764

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)| |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
|* 2 | TABLE ACCESS FULL| T003 | 255 | 7395 | 154 (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T003@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:B)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT("OBJECT_NAME")[22]
2 - "OBJECT_NAME"[VARCHAR2,128]

31 rows selected.

SQL> exec : b:=10;

PL/SQL procedure successfully completed.

SQL> select count(object_name) from t003 where object_id=:b;

COUNT(OBJECT_NAME)
------------------
0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 9qunh3ms4kjzw, child number 0
-------------------------------------
select count(object_name) from t003 where object_id=:b

Plan hash value: 3872854764

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)| |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
|* 2 | TABLE ACCESS FULL| T003 | 255 | 7395 | 154 (1)| 00:00:02 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / T003@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=:B)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) COUNT("OBJECT_NAME")[22]
2 - "OBJECT_NAME"[VARCHAR2,128]

31 rows selected.

如果有直方圖，但是關(guān)閉綁定變量窺視，由于無法把綁定變量的值帶入sql語句中，此時(shí)selectivity計(jì)算方式還是1/num_distinct*(num_rows-num_nulls)/num_rows
SQL> select 50325*1/197 from dual;

50325*1/197
-----------
255.456853

在關(guān)閉綁定變量窺視后，sql語句還是會(huì)軟解析，只是綁定變量的sql語句第一次執(zhí)行時(shí)無法帶入到sql語句中，selectivity計(jì)算無法應(yīng)用到直方圖信息，所以此時(shí)有無直方圖對(duì)于這類綁定變量的sql語句沒有影響。

關(guān)于有直方圖時(shí)，字段的可選擇、density等如何計(jì)算，小魚后續(xù)會(huì)整理文檔來分享。

原文地址：綁定變量窺視是否開啟和直方圖關(guān)系, 感謝原作者分享。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com