選擇率和硬解析

我們上面提到了，在v$sql_cs_histogram檢視中，如果此遊標的3個桶中出現了兩個桶中的count都有非0值，那麼此後的解析都要窺探繫結變數的值計算謂詞選擇率，如果計算選擇率不在現有的遊標的選擇率範圍內，就會基於窺探到的繫結變數的值重新硬解析產生一個新的遊標，當然這個新遊標的執行計劃可能與之前是一樣的。我們還是來看一個例子就會非常明白這種機制了。

SQL>create table t as select 1 id,a.* from dba_objects a,dba_objects b where rownum<10;

Table created.

SQL>create index t_ind on t(id);

Index created.

SQL>insert into t select 2,a.* from dba_objects a,dba_objects b where rownum<1000;

999 rows created.

SQL>insert into t select 3 ,a.* from dba_objects a,dba_objects b where rownum<10000;

9999 rows created.

SQL>insert into t select 4 ,a.* from dba_objects a,dba_objects b where rownum<100000;

99999 rows created.

SQL>insert into t select 5 ,a.* from dba_objects a,dba_objects b where rownum<1000000;

999999 rows created.

SQL>commit;

Commit complete.

SQL>begin

2 dbms_stats.gather_table_stats(user,

3 't',

4 method_opt => 'for columns status size 5',

5 cascade => true);

6

7 end;

8 /

SQL>select id,count(*) from t group by id order by id;

ID COUNT(*)

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

1 9

2 999

3 9999

4 99999

5 999999

上面的程式碼精心構造了一個例子，表t上的id欄位一共有5個唯一值，每個值的數量都不一樣，id欄位上有索引，分析了直方圖。在這種情況下，如果我們直接使用字元變數不使用繫結變數的話，id在對1,2,3,4做查詢的時候，都會使用索引掃描，這種情況下，索引掃描的成本要比全表掃描的成本低，id在對5做查詢時，會使用全表掃描，這種情況下全表掃描的成本要比索引掃描成本低。如下表格，我是透過explain工具，使用文字變數後，得出的每個執行計劃的cost，可以看到全表掃描的cost為2911，在查詢id<5的情況下，由於索引掃描的cost都小於全表掃描的cost因此執行計劃都選擇了走索引掃描，只有在查詢id等於5的時，才選擇了走全表掃描。

ID	執行計劃	COST	選擇率
1	索引掃描	4	0.0000081
2	索引掃描	16	0.000899186
3	索引掃描	139	0.008999959
4	索引掃描	1370	0.090007696
5	索引掃描	13690	0. 900085058
5	全表掃描	2911	0. 900085058

上面的表格最後一列提供了謂詞的選擇率，此處選擇率的計算公式為：

選擇率=id=?的值在表中的數量/總數量

根據上面表格的cost我們可以知道，謂詞的選擇率在0.0000081到0.090007696之間都應該選擇索引掃描，在0. 900085058的時候應該選擇全表掃描，因為id在5的時候，索引掃描的成本13690已經遠遠大於了全表掃描的成本2911。我們看看下面的例子：

SQL>var a number;

SQL>exec :a :=1;

PL/SQL procedure successfully completed.

SQL>select count(object_id) from t where id=:a;

COUNT(OBJECT_ID)

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

9

SQL>exec :a :=5;

PL/SQL procedure successfully completed.

SQL>select count(object_id) from t where id=:a;

COUNT(OBJECT_ID)

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

999999

SQL>select count(object_id) from t where id=:a;

COUNT(OBJECT_ID)

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

999999

SQL>col PREDICATE for a10

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

ORDER BY sql_id, child_number; 4

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

56g5zg95hcxc1 1 =A 0 0.810076 0.990093

經過上面的一系列的操作後我們已經讓這個cursor變得bind aware，如何讓SQL變得bind aware我們上面已經論述過，這裡不再做詳細說明。經過這些步驟後，最佳化器已經產生出了一個child_number為1的新遊標，這個遊標基於繫結變數為5的值生成，謂詞的選擇率範圍是：0.810076到0.990093。這個選擇率跟我們上面表格裡提供的選擇率的關係是：（0.810076+0.990093）/2約等於我們上面表格裡提供的選擇率0. 900085058，Oracle為選擇率稍微的預留了一些餘地，這樣很好。我們再執行id為1的查詢看看：

SQL>exec :a :=1;

PL/SQL procedure successfully completed.

SQL>select count(object_id) from t where id=:a;

COUNT(OBJECT_ID)

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

9

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

ORDER BY sql_id, child_number;

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

56g5zg95hcxc1 1 =A 0 0.810076 0.990093

56g5zg95hcxc1 2 =A 0 0.000007 0.000009

已經產生了child_number為2的子游標，是基於id為1的值產生的，選擇率範圍為：0.000007到0.000009。下面就到了本節關鍵的時刻了，我們再次查詢id為4看看會出現什麼情況。

SQL>exec :a :=4

PL/SQL procedure successfully completed.

SQL>select count(object_id) from t where id=:a;

COUNT(OBJECT_ID)

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

99999

SQL>

SQL>col PREDICATE for a10

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

4 ORDER BY sql_id, child_number;

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

56g5zg95hcxc1 1 =A 0 0.810076 0.990093

56g5zg95hcxc1 2 =A 0 0.000007 0.000009

56g5zg95hcxc1 3 =A 0 0.000007 0.099008

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware,IS_SHAREABLE

3 FROM v$sql

4 WHERE sql_id='56g5zg95hcxc1';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

0 2 13690 Y N N

1 1 13162 Y Y Y

2 1 4 Y Y N

3 1 1495 Y Y Y

最佳化器已經重新生成了一個child_numer為3的子游標，同時選擇率的範圍已經擴大了，從0.000007到0.099008，也就是現在從id為1到4都被包含在child_number為3的子游標裡了。child_number為2的子游標已經被標記為不能共享失效了，如果共享池有緊缺這塊記憶體就可以被清除出去。那是不是意味著我們查詢id為3的值時，將不用重新產生新遊標，直接可以使用child_number為3的子游標了。我們來看看：

SQL>exec :a :=3

PL/SQL procedure successfully completed.

SQL>col PREDICATE for a10

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

4 ORDER BY sql_id, child_number;

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

56g5zg95hcxc1 1 =A 0 0.810076 0.990093

56g5zg95hcxc1 2 =A 0 0.000007 0.000009

56g5zg95hcxc1 3 =A 0 0.000007 0.099008

SQL>select count(object_id) from t where id=:a;

COUNT(OBJECT_ID)

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

9999

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='56g5zg95hcxc1'

4 ORDER BY sql_id, child_number;

SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

56g5zg95hcxc1 1 =A 0 0.810076 0.990093

56g5zg95hcxc1 2 =A 0 0.000007 0.000009

56g5zg95hcxc1 3 =A 0 0.000007 0.099008

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware,IS_SHAREABLE

3 FROM v$sql

4 WHERE sql_id='56g5zg95hcxc1';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

0 2 13690 Y N N

1 1 13162 Y Y Y

2 1 4 Y Y N

3 2 1495 Y Y Y

沒有再生成新的子游標了，同時v$sql中的child_number為3的子游標的執行次數已經加1了。

從上面的示例我們可以知道，在v$sql_cs_histogram檢視中，如果此遊標的3個桶中出現了兩個桶中的count都有非0值，那麼此後的解析都要窺探繫結變數的值計算謂詞選擇率，如果計算選擇率不在現有的遊標的選擇率範圍內，就會基於窺探到的繫結變數的值重新硬解析產生一個新的遊標，記錄此遊標的可以代表的選擇率範圍，當然就像我們例子看到的，新遊標的執行計劃可能跟之前是一樣的，只不過是選擇率的範圍更廣了。

Adaptive Cursor Sharing（第二篇）

選擇率和硬解析

相關文章