center]如何分析執(zhí)行計(jì)劃[/center]
例1： 假設(shè)LARGE_TABLE是一個(gè)較大的表，且username列上沒有索引，則運(yùn)行下面的語句：
SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [
65001] [ANALYZED]
在這個(gè)例子中，TABLE ACCESS FULL LARGE_TABLE是第一個(gè)操作，意思是在LARGE_TABLE表上做全表掃描。當(dāng)這個(gè)操作完成之后，產(chǎn)生的row source中的數(shù)據(jù)被送往下一步驟進(jìn)行處理，在此例中，SELECT STATEMENT操作是這個(gè)查詢語句的最后一步。
Optimizer=CHOOSE 指明這個(gè)查詢的optimizer_mode，即optimizer_mode初始化參數(shù)指定的值，它并不是指語句執(zhí)行時(shí)真的使用了該優(yōu)化器。決定該語句使用何種優(yōu)化器的唯一方法是看后面的cost部分。例如，如果給出的是下面的形式，則表明使用的是CBO優(yōu)化器，此處的cost表示優(yōu)化器認(rèn)為該執(zhí)行計(jì)劃的代價(jià):
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
然而假如執(zhí)行計(jì)劃中給出的是類似下面的信息，則表明是使用RBO優(yōu)化器，因?yàn)閏ost部分的值為空，或者壓根就沒有cost部分。
SELECT STATEMENT Optimizer=CHOOSE Cost=
SELECT STATEMENT Optimizer=CHOOSE
這樣我們從Optimizer后面的信息中可以得出執(zhí)行該語句時(shí)到底用了什么樣的優(yōu)化器。特別的，如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n，則使用的是CBO優(yōu)化器；如果Optimizer=RULE，則使用的是RBO優(yōu)化器。
cost屬性的值是一個(gè)在oracle內(nèi)部用來比較各個(gè)執(zhí)行計(jì)劃所耗費(fèi)的代價(jià)的值，從而使優(yōu)化器可以選擇最好的執(zhí)行計(jì)劃。不同語句的cost值不具有可比性，只能對(duì)同一個(gè)語句的不同執(zhí)行計(jì)劃的cost值進(jìn)行比較。
[
65001] 表明該部分查詢是以并行方式運(yùn)行的。里面的數(shù)據(jù)表示這個(gè)操作是由并行查詢的一個(gè)slave進(jìn)程處理的，以便該操作可以區(qū)別于串行執(zhí)行的操作。
[ANALYZED] 表明操作中引用的對(duì)象被分析過了，在數(shù)據(jù)字典中有該對(duì)象的統(tǒng)計(jì)信息可以供CBO使用。
例2： 假定A、B、C都是不是小表，且在A表上一個(gè)組合索引：A(a.col1,a.col2) ，注意a.col1列為索引的引導(dǎo)列。
考慮下面的查詢：
select A.col4
from A , B , C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
6 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
在表做連接時(shí)，只能2個(gè)表先做連接，然后將連接后的結(jié)果作為一個(gè)row source，與剩下的表做連接，在上面的例子中，連接順序?yàn)锽與A先連接，然后再與C連接：
B <---> A <---> C
col3=10 col3=5
如果沒有執(zhí)行計(jì)劃，分析一下，上面的3個(gè)表應(yīng)該拿哪一個(gè)作為第一個(gè)驅(qū)動(dòng)表？從SQL語句看來，只有B表與C表上有限制條件，所以第一個(gè)驅(qū)動(dòng)表應(yīng)該為這2個(gè)表中的一個(gè)，到底是哪一個(gè)呢？
B表有謂詞B.col3 = 10，這樣在對(duì)B表做全表掃描的時(shí)候就將where子句中的限制條件(B.col3 = 10)用上，從而得到一個(gè)較小的row source, 所以B表應(yīng)該作為第一個(gè)驅(qū)動(dòng)表。而且這樣的話，如果再與A表做關(guān)聯(lián)，可以有效利用A表的索引(因?yàn)锳表的col1列為leading column)。
當(dāng)然上面的查詢中C表上也有謂詞(C.col3 = 5)，有人可能認(rèn)為C表作為第一個(gè)驅(qū)動(dòng)表也能獲得較好的性能。讓我們?cè)賮矸治鲆幌拢喝绻鸆表作為第一個(gè)驅(qū)動(dòng)表，則能保證驅(qū)動(dòng)表生成很小的row source，但是看看連接條件A.col2 = C.col2，此時(shí)就沒有機(jī)會(huì)利用A表的索引，因?yàn)锳表的col2列不為leading column，這樣nested loop的效率很差，從而導(dǎo)致查詢的效率很差。所以對(duì)于NL連接選擇正確的驅(qū)動(dòng)表很重要。
因此上面查詢比較好的連接順序?yàn)?B - - > A) - - > C。如果數(shù)據(jù)庫是基于代價(jià)的優(yōu)化器，它會(huì)利用計(jì)算出的代價(jià)來決定合適的驅(qū)動(dòng)表與合適的連接順序。一般來說，CBO都會(huì)選擇正確的連接順序，如果CBO選擇了比較差的連接順序，我們還可以使用ORACLE提供的hints來讓CBO采用正確的連接順序。如下所示：
select /*+ ordered */ A.col4
from B,A,C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5
既然選擇正確的驅(qū)動(dòng)表這么重要，那么讓我們來看一下執(zhí)行計(jì)劃，到底各個(gè)表之間是如何關(guān)聯(lián)的，從而得到執(zhí)行計(jì)劃中哪個(gè)表應(yīng)該為驅(qū)動(dòng)表：
在執(zhí)行計(jì)劃中，需要知道哪個(gè)操作是先執(zhí)行的，哪個(gè)操作是后執(zhí)行的，這對(duì)于判斷哪個(gè)表為驅(qū)動(dòng)表有用處。判斷之前，如果對(duì)表的訪問是通過rowid，且該rowid的值是從索引掃描中得來得，則將該索引掃描先從執(zhí)行計(jì)劃中暫時(shí)去掉。然后在執(zhí)行計(jì)劃剩下的部分中，判斷執(zhí)行順序的指導(dǎo)原則就是：最右、最上的操作先執(zhí)行。具體解釋如下：
得到去除妨礙判斷的索引掃描后的執(zhí)行計(jì)劃：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
看執(zhí)行計(jì)劃的第3列，即字母部分，每列值的左面有空格作為縮進(jìn)字符。在該列值左邊的空格越多，說明該列值的縮進(jìn)越多，該列值也越靠右。如上面的執(zhí)行計(jì)劃所示：第一列值為6的行的縮進(jìn)最多，即該行最靠右；第一列值為4、5的行的縮進(jìn)一樣，其靠右的程度也一樣，但是第一列值為4的行比第一列值為5的行靠上；談?wù)撋舷玛P(guān)系時(shí)，只對(duì)連續(xù)的、縮進(jìn)一致的行有效。
從這個(gè)圖中我們可以看到，對(duì)于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS (FULL) OF 'B'，所以這一操作先執(zhí)行，所以該操作對(duì)應(yīng)的B表為第一個(gè)驅(qū)動(dòng)表(外部表)，自然，A表就為內(nèi)部表了。從圖中還可以看出，B與A表做嵌套循環(huán)后生成了新的row source ，對(duì)該row source進(jìn)行來排序后，與C表對(duì)應(yīng)的排序了的row source(應(yīng)用了C.col3 = 5限制條件)進(jìn)行MSJ連接操作。所以從上面可以得出如下事實(shí)：B表先與A表做嵌套循環(huán)，然后將生成的row source與C表做排序—合并連接。
通過分析上面的執(zhí)行計(jì)劃，我們不能說C表一定在B、A表之后才被讀取，事實(shí)上，B表有可能與C表同時(shí)被讀入內(nèi)存，因?yàn)閷⒈碇械臄?shù)據(jù)讀入內(nèi)存的操作可能為并行的。事實(shí)上許多操作可能為交叉進(jìn)行的，因?yàn)镺RACLE讀取數(shù)據(jù)時(shí)，如果就是需要一行數(shù)據(jù)也是將該行所在的整個(gè)數(shù)據(jù)塊讀入內(nèi)存，而且還有可能為多塊讀。
看執(zhí)行計(jì)劃時(shí)，我們的關(guān)鍵不是看哪個(gè)操作先執(zhí)行，哪個(gè)操作后執(zhí)行，而是關(guān)鍵看表之間連接的順序(如得知哪個(gè)為驅(qū)動(dòng)表，這需要從操作的順序進(jìn)行判斷)、使用了何種類型的關(guān)聯(lián)及具體的存取路徑(如判斷是否利用了索引)
在從執(zhí)行計(jì)劃中判斷出哪個(gè)表為驅(qū)動(dòng)表后，根據(jù)我們的知識(shí)判斷該表作為驅(qū)動(dòng)表(就像上面判斷ABC表那樣)是否合適，如果不合適，對(duì)SQL語句進(jìn)行更改，使優(yōu)化器可以選擇正確的驅(qū)動(dòng)表。
對(duì)于RBO優(yōu)化器： 在ORACLE文檔上說：對(duì)于RBO來說，以from 子句中從右到左的順序選擇驅(qū)動(dòng)表，即最右邊的表為第一個(gè)驅(qū)動(dòng)表，這是其英文原文：All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。不過，在我做的測試中，從來也沒有驗(yàn)證過這種說法是正確的。我認(rèn)為，即使在RBO中，也是有一套規(guī)則來決定使用哪種連接類型和哪個(gè)表作為驅(qū)動(dòng)表，在選擇時(shí)肯定會(huì)考慮當(dāng)前索引的情況，還可能會(huì)考慮where 中的限制條件，但是肯定是與where中限制條件的位置無關(guān)。
測試：如果我創(chuàng)建3個(gè)表：create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
create index inx_col12A on a(col1,col2);
執(zhí)行查詢：
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
將A表上的索引inx_col12A刪除后：
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'C'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'B'
通過上面的這些例子，使我對(duì)oracle文檔上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”這句話持懷疑態(tài)度。此時(shí)，我也不能使用hints來強(qiáng)制優(yōu)化器使用nested loop，如果使用了hints，這樣就自動(dòng)使用CBO優(yōu)化器，而不是RBO優(yōu)化器了。
對(duì)于CBO優(yōu)化器： CBO根據(jù)統(tǒng)計(jì)信息選擇驅(qū)動(dòng)表，假如沒有統(tǒng)計(jì)信息，則在from 子句中從左到右的順序選擇驅(qū)動(dòng)表。這與RBO選擇的順序正好相反。這是英文原文(CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO) 。我還是沒法證實(shí)這句話的正確性。不過經(jīng)過驗(yàn)證：“如果用ordered 提示(此時(shí)肯定用CBO)，則以from 子句中按從左到右的順序選擇驅(qū)動(dòng)表”這句話是正確的。實(shí)際上在CBO中，如果有統(tǒng)計(jì)數(shù)據(jù)(即對(duì)表與索引進(jìn)行了分析)，則優(yōu)化器會(huì)自動(dòng)根據(jù)cost值決定采用哪種連接類型，并選擇合適的驅(qū)動(dòng)表，這與where子句中各個(gè)限制條件的位置沒有任何關(guān)系。如果我們要改變優(yōu)化器選擇的連接類型或驅(qū)動(dòng)表，則就需要使用hints了，具體hints的用法在后面會(huì)給予介紹。
測試：
如果我創(chuàng)建的3個(gè)表：
create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
create index inx_col12A on a(col1,col2);
執(zhí)行查詢：
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=55 Bytes=4620)
1 0 HASH JOIN (Cost=5 Card=55 Bytes=4620)
2 1 HASH JOIN (Cost=3 Card=67 Bytes=4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=82 Bytes=1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=82 Bytes=1066)
將A表上的索引inx_col12A刪除后：
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=55 Bytes=4620)
1 0 HASH JOIN (Cost=5 Card=55 Bytes=4620)
2 1 HASH JOIN (Cost=3 Card=67 Bytes=4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=82 Bytes=1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=82 Bytes=1066)
select /*+ ORDERED */A.col4
from C, A, B
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
這個(gè)查詢驗(yàn)證了通過ORDERED提示可以正確的提示優(yōu)化器選擇哪個(gè)表作為優(yōu)化器。
