簡單探討一下10G下柱狀圖和執行計劃

SQL> create index idx_th on th(owner);

索引已建立。

--在OWNER上建立一個索引

SQL> select owner,count(1) from th group by owner;

OWNER COUNT(1)
------------------------------ ----------
SCOTT 9
SYS 49870

--可以看到分佈很不均稱

SQL> analyze table th compute statistics;

表已分析。

--做普通分析後，執行查詢

SQL> set autot traceonly;
SQL> select * from th where owner='SYS';

已選擇49870行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"='SYS')

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3971 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> select * from th where owner='SCOTT';

已選擇9行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"='SCOTT')

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

只需返回9條資料，但做了全表掃描，這是不合理的執行計劃。因為，它只是知道owner列有二個不同的值，但oracle不知道每個

不同的owner分別有多少記錄，oracle預設為這些資料的分佈是完全均勻的，所以，當用owner作條件時，oracle會認為會返回總

記錄的二分之一（從執行計劃中的rows=24940(49879/2)可以看出來）

對錶TH生成柱狀圖後在做同樣的查詢

SQL> analyze table th compute statistics for table for all indexes for all indexed columns;

表已分析。

SQL> select * from th where owner='SYS';

已選擇49870行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49870 | 4188K| 157 (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 49870 | 4188K| 157 (4)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"='SYS')

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3971 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> select * from th where owner='SCOTT';

已選擇9行。

執行計劃
----------------------------------------------------------
Plan hash value: 807472438

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 774 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TH | 9 | 774 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TH | 9 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OWNER"='SCOTT')

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 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;

已選擇49870行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"=:O)

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3971 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> exec :o:='SCOTT';

PL/SQL 過程已成功完成。

SQL> select * from th where owner=:o;

已選擇9行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"=:O)

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

從以上測試可以看到，在繫結變數的情況下，如果沒有分析柱狀圖，兩個查詢都使用了相同的執行計劃--全表掃描。這也很容易

理解，在第一次解析SQL的時候，會根據:o的繫結的值去窺視表資料，因為oracle不知道連線列的資料的具體分佈，所以它會以

為會返回二分之一的資料，所以選擇了全表掃描。在以後的執行同樣的SQL時會重用該SQL，都會使用第一次解析生成的執行計劃

了。在本例中，由於沒有做柱狀圖，索引第一次執行select * from th where owner=:o時，無論:0是'SYS'還是'SCOTT'，都會

使用全表掃描。那麼，我們是否可以得出這樣的一個結論：如果對錶做了柱狀圖，那麼如果第一次硬解析SQL時:o的值是'SCOTT'

時，這個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;

已選擇49870行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"=:O)

統計資訊
----------------------------------------------------------
244 recursive calls
0 db block gets
3995 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> alter system flush shared_pool;

系統已更改。

SQL> exec :o:='SCOTT';

PL/SQL 過程已成功完成。

SQL> select * from th where owner=:o;

已選擇9行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"=:O)

統計資訊
----------------------------------------------------------
446 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
9 rows processed

從這個結果可以看到，分析了柱狀圖後，無論:o的值是'SYS'還是'SCOTT'，第一次執行該sql時，使用的都是全表掃描，這與剛

才我們的推論不一致了，如果真是這樣的話，使用繫結變數對錶做柱狀圖還有什麼意義呢？其實這應該算是ORACLE的一個BUG，

在這裡AUTOTRACE的結果是不對的，我們可以用10046看

SQL> alter session set events '10046 trace name context forever, level 12';

會話已更改。

SQL> alter system flush shared_pool;

系統已更改。

SQL> exec :o:='SYS'

PL/SQL 過程已成功完成。

SQL> select * from th where owner=:o;

已選擇49870行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"=:O)

統計資訊
----------------------------------------------------------
462 recursive calls
0 db block gets
4024 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> exec :o:='SCOTT';

PL/SQL 過程已成功完成。

SQL> select * from th where owner=:o;

已選擇9行。

執行計劃
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER"=:O)

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> alter session set events '10046 trace name context off';

會話已更改。

下面看看TKPROF後的內容：

select *
from
th where owner=:o

當:o:='SYS'時

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3326 0.25 0.14 0 3971 0 49870
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3328 0.26 0.15 0 3971 0 49870

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
49870 TABLE ACCESS FULL TH (cr=3971 pr=0 pw=0 time=49941 us)

select *
from
th where owner=:o

當:o:='SCOTT'時

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 0 9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
9 TABLE ACCESS BY INDEX ROWID TH (cr=6 pr=0 pw=0 time=43 us)
9 INDEX RANGE SCAN IDX_TH (cr=3 pr=0 pw=0 time=119 us)(object id 51508)

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

以上的例子可以證明這點。

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