本文從不綁定變量與綁定變量兩種情況討論柱狀圖的作用。
一、不綁定變量的情況：
大家可以考慮下面的數據：
SQL> select owner,count(1) from th group by owner;
OWNER COUNT(1)
------------------------------ ----------
SUK 1
SYS 36216
SYSTEM 1
其中，在表的OWENR上建立有一個索引。
做完普通分析后，再來執行查詢。
SQL> analyze table th compute statistics;
Table analyzed
SQL> select * from th where owner='SYS';
已選擇36216行。
Execution Plan
-------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=12073 Bytes=
881329)
1 0 TABLE access (FULL) OF 'TH' (Cost=50 Card=12073 Bytes=8813
29)
Statistics
-------------------------------------------
0 recursive calls
0 db block gets
2894 consistent gets
0 physical reads
0 redo size
2045535 bytes sent via SQL*Net to client
27057 bytes received via SQL*Net from client
2416 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36216 rows PRocessed
SQL> select * from th where owner='SUK';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=12073 Bytes=
881329)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=12073 Bytes=8813
29)
Statistics
--------------------------------------
0 recursive calls
0 db block gets
513 consistent gets
0 physical reads
0 redo size
1133 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
只需返回一條數據，但做了全表掃描，這是不合理的執行計劃。因為，它只是知道owner列有三個不同的值，但Oracle不知道每個不同的owner分別有多少記錄，Oracle默認為這些數據的分布是完全均勻的，所以，當用owner作條件時，Oracle會認為會返回總記錄的三分之一（從執行計劃中的Card=12073可以看出來）
對表TH生成柱狀圖后在做同樣的查詢:
SQL> analyze table th compute statistics for
table for all indexes for all indexed columns;
Table analyzed
SQL> select * from th where owner='SYS';
已選擇36216行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=36216 Bytes=
2643768)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=36216 Bytes=2643
768)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2894 consistent gets
0 physical reads
0 redo size
2045535 bytes sent via SQL*Net to client
27057 bytes received via SQL*Net from client
2416 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36216 rows processed
SQL> select * from th where owner='SUK';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=73)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TH' (Cost=2 Card=1 Bytes
=73)
2 1 INDEX (RANGE SCAN) OF 'IDX_TH' (NON-UNIQUE) (Cost=1 Card
=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1133 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可見，生成了柱狀圖后，Oracle會根據數據的實際分布情況
選擇合適的執行計劃，提高性能。
-------------------------------------
二、綁定變量的情況下
下面看看在綁定變量的情況下，執行同樣的操作，會發生什么事情
SQL> analyze table th compute statistics;
表已分析。
SQL> var o varchar2(20)SQL> exec :o:='SYS'
PL/SQL 過程已成功完成。
SQL> select * from th where owner=:o;
已選擇32192行。
Execution Plan
-----------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=10731 Bytes=
783363)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=7833
63)
Statistics
-----------------------------------------
0 recursive calls
0 db block gets
2886 consistent gets
0 physical reads
0 redo size
1818406 bytes sent via SQL*Net to client
24109 bytes received via SQL*Net from client
2148 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32192 rows processed
SQL> exec :o:='SUK'
PL/SQL 過程已成功完成。
SQL> select * from th where owner=:o;
Execution Plan
------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=10731 Bytes=
783363)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=7833
63)
Statistics
------------------------------------
0 recursive calls
0 db block gets
770 consistent gets
0 physical reads
0 redo size
1151 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從以上測試可以看到，在綁定變量的情況下，如果沒有分析柱狀圖，兩個查詢都使用了相同的執行計劃--全表掃描。這也很容易理解，在第一次解析SQL的時候，會根據:o的綁定的值去窺視表數據，因為oracle不知道連接列的數據的具體分布，所以它會以為會返回三分之一的數據，所以選擇了全表掃描。在以后的執行同樣的SQL時會重用該SQL，都會使用第一次解析生成的執行計劃了。在本例中，由于沒有做柱狀圖，索引第一次執行select * from th where owner=:o時，無論:0是'SYS'還是'SUK'，都會使用全表掃描。那么，我們是否可以得出這樣的一個結論：如果對表做了柱狀圖，那么如果第一次硬解析SQL時:o的值是'SUK'時，這個sql將會使用索引掃描；如果第一次硬解析時:o的值是'SYS'時，SQL將會使用全表掃描呢？看如下的測試例子：
SQL> alter system flush shared_pool;
系統已更改。
SQL> analyze table th delete statistics;
表已分析。
SQL> analyze table th compute statistics for table
for all indexes for all indexed columns;
表已分析。
SQL> exec :o:='SYS'
PL/SQL 過程已成功完成。
SQL> select * from th where owner=:o;
已選擇32192行。
Execution Plan
---------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=10731 Bytes=
890673)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=8906
73)
Statistics
--------------------------------------
271 recursive calls
0 db block gets
2900 consistent gets
0 physical reads
0 redo size
1818406 bytes sent via SQL*Net to client
24109 bytes received via SQL*Net from client
2148 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32192 rows processed
SQL> alter system flush shared_pool;
系統已更改。
SQL> exec :o:='SUK'
PL/SQL 過程已成功完成。
SQL> select * from th where owner=:o;
Execution Plan
----------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=10731 Bytes=
890673)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=8906
73)
Statistics
----------------------------------------
529 recursive calls
0 db block gets
51 consistent gets
0 physical reads
0 redo size
1151 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
從這個結果可以看到，分析了柱狀圖后，無論:o的值是'SYS'還是'SUK'，第一次執行該sql時，使用的都是全表掃描，這與剛才我們的推論不一致了，如果真是這樣的話，使用綁定變量對表做柱狀圖還有什么意義呢？其實這應該算是ORACLE的一個BUG，在這里AUTOTRACE的結果是不對的，我們可以用10046看
第一次執行
select *
from
th where owner=:o
當:o:='SYS'時
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2148 0.09 0.17 0 2886 0 32192
------- ------ -------- ---------- ----------
total 2150 0.09 0.18 0 2886 0 32192
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Rows Row Source Operation
------- -----------------------------------
32192 TABLE ACCESS FULL TH
第一次執行
select *
from
th where owner=:o
當:o:='SUK'時
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ----------
Parse 1 0.01 0.01 0 31 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ----------
total 4 0.01 0.01 0 35 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Rows Row Source Operation
------- ---------------------------------------
1 TABLE ACCESS BY INDEX ROWID TH
1 INDEX RANGE SCAN IDX_TH (object id 7248)
從Oracle 9i開始，Oracle在對sql第一次硬解析時，會對綁定的變量值進行窺視，從而根據變量值和數據的分布決定sql的執行計劃。從以上的例子可以證明這點。
結論：
1、無論是否綁定變量，對數據分布不均的情況下柱狀圖都是很有效的
2、對數據分布不均勻的情況下，使用綁定變量可能會造成惡果，就算對表做了柱狀圖也一樣
3、使用綁定變量，sql第一次執行決定了以后同樣的sql執行的執行計劃
4、AUTOTRACE的信息不一定準確，必要時要用10046查看需要的信息
新聞熱點
疑難解答
