溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
我們上面提到了，在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值，那么此后的解析都要窺探綁定變量的值計算謂詞選擇率，如果計算選擇率不在現有的游標的選擇率范圍內，就會基于窺探到的綁定變量的值重新硬解析產生一個新的游標，記錄此游標的可以代表的選擇率范圍，當然就像我們例子看到的，新游標的執行計劃可能跟之前是一樣的，只不過是選擇率的范圍更廣了。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
