ORACLE柱狀圖與執行計劃(轉)

本文從不繫結變數與繫結變數兩種情況討論柱狀圖的作用。

一、不繫結變數的情況：

考慮以下資料

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

suk@ORACLE9I> 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

suk@ORACLE9I> 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

suk@ORACLE9I> 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

suk@ORACLE9I> 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會根據資料的實際分佈情況選擇合適的執行計劃，提高效能。

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

二、繫結變數的情況下

下面看看在繫結變數的情況下，執行同樣的操作，會發生什麼事情

suk@ORACLE9I> analyze table th compute statistics;

表已分析。

suk@ORACLE9I> var o varchar2(20)suk@ORACLE9I> exec :o:='SYS'

PL/SQL 過程已成功完成。

suk@ORACLE9I> 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

suk@ORACLE9I> exec :o:='SUK'

PL/SQL 過程已成功完成。

suk@ORACLE9I> 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將會使用全表掃描呢？看如下的測試例子：

suk@ORACLE9I> alter system flush shared_pool;

系統已更改。

suk@ORACLE9I> analyze table th delete statistics;

表已分析。

suk@ORACLE9I> analyze table th compute statistics for table for all indexes for all indexed columns;

表已分析。

suk@ORACLE9I> exec :o:='SYS'

PL/SQL 過程已成功完成。

suk@ORACLE9I> 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

suk@ORACLE9I> alter system flush shared_pool;

系統已更改。

suk@ORACLE9I> exec :o:='SUK'

PL/SQL 過程已成功完成。

suk@ORACLE9I> 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)

從9i開始，oracle在對sql第一次硬解析時，會對繫結的變數值進行窺視，從而根據變數值和資料的分佈決定sql的執行計劃。從以上的例子可以證明這點。

到此為止可以可以得出如下結論：
1、無論是否繫結變數，對資料分佈不均的情況下柱狀圖都是很有效的
2、對資料分佈不均勻的情況下，使用繫結變數可能會造成惡果，就算對錶做了柱狀圖也一樣
3、使用繫結變數，sql第一次執行決定了以後同樣的sql執行的執行計劃
4、AUTOTRACE的資訊不一定準確，必要時要用10046檢視需要的資訊

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