oracle的執行計劃居然出錯[轉帖]

今天同事發給我一個sql，說查詢不到結果，sql本身沒有錯誤。而且在其他伺服器上執行可以得到結果。

環境如下：
SQL> select * from v$version;

[@more@]

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

表結構：
SQL> desc plt_plat
Name Null? Type
-------------------------------------- -------- ----------------
ID NOT NULL CHAR(24)
PLAT_FATHER CHAR(24)
PLAT_CLASS CHAR(1)
PLAT_GRADE NUMBER(2)
PLAT_NAME NOT NULL VARCHAR2(30)
PLAT_LEVEL VARCHAR2(10)
PLAT_DESC VARCHAR2(500)
PLAT_ROOT CHAR(24)
PLAT_CODE VARCHAR2(30)

查詢語句：
select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
,province.id,province.plat_name,province.plat_class
from plt_plat pp,
plt_plat city,
plt_plat province
where pp.plat_father=city.id
and city.plat_father=province.id
and pp.plat_class=4

由於這張表是物化檢視複製生成的，首先我檢查了物件的狀態，然後檢查了物化檢視的指令碼，並重新重新整理了這張物化檢視。錯誤依舊。和其他伺服器上的表進行表結構的對比，沒有發現錯誤。檢查表中的資料，發現和其他伺服器上的完全一致。使用語句analyze table plt_plat validate structure cascade檢查表和索引結構，未發現異常。

懷疑是否是錯誤的統計資訊造成的，執行語句analyze table plt_plat delete statistics;，結果發現得到了正確的結果。

然後重新收集統計資訊analyze table plt_plat compute statistics，錯誤又出現了。

懷疑和執行路徑有關：

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=132)
1 0 HASH JOIN (Cost=7 Card=1 Bytes=132)
2 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=1 Bytes=72)
3 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=330 Bytes=19800)

感覺很奇怪，怎麼三張表的連線，執行計劃裡面只有兩張表關聯。

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

149 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'PLT_PLAT'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
7 6 INDEX (UNIQUE SCAN) OF 'PK_PLT_PLAT' (UNIQUE)

採用rule模式，執行計劃是正確的。

下面是在其他伺服器上相同表（也是透過物化檢視複製生成的）上執行查詢的結果。

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

149 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=76 Bytes=12692)
1 0 HASH JOIN (Cost=8 Card=76 Bytes=12692)
2 1 HASH JOIN (Cost=5 Card=76 Bytes=9728)
3 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=2 Card=76 Bytes=4864)
4 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=2 Card=304 Bytes=19456)
5 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=2 Card=304 Bytes=11856)

錯誤已經很明顯了，oracle的執行計劃出錯。

是什麼原因造成的執行計劃出錯呢？仔細檢查初始化引數後發現，query_rewrite_enabled的值是TRUE。

一直沒有注意這個引數，是因為的建立物化檢視複製的時候並沒有指定ENABLE QUERY REWRITE子句，在PLT_PLAT表上也沒有建立其它物化檢視，而且初始化引數query_rewrite_integrity的值是enforced，這是最嚴格的設定，沒有想到在這種情況下oracle仍然使用了query rewrite，而且查詢重寫後得到的結果也不正確。

這樣看來oracle的查詢重寫、統計資訊的收集以及CBO執行策略還是有些問題的。

下面簡要描述一下不同情況下會出現什麼問題。

1.將查詢重寫關閉，無論是否有統計資訊，也無論統計資訊生成的方式，查詢都不會出錯。

SQL> alter session set query_rewrite_enabled = false;

Session altered.

SQL> set autot on exp

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

149 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=84 Bytes=14028)
1 0 HASH JOIN (Cost=11 Card=84 Bytes=14028)
2 1 HASH JOIN (Cost=7 Card=84 Bytes=10752)
3 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=84 Bytes=5376)
4 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=21376)
5 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=13026)

SQL> analyze table plt_plat delete statistics;

Table analyzed.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

149 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'PLT_PLAT'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
6 5 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)

2.開啟查詢重寫，基於rule的查詢結果正確，如果沒有統計資訊，all_rows和first_rows結果都是錯誤的。

SQL> alter session set query_rewrite_enabled = true;

Session altered.

SQL> analyze table plt_plat delete statistics;

Table analyzed.

SQL> alter session set optimizer_mode = rule;

Session altered.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

149 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'PLT_PLAT'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
6 5 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)

SQL> alter session set optimizer_mode = first_rows;

Session altered.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=13 Bytes=2132)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT' (Cost=2 Card=1 Bytes=72)
2 1 NESTED LOOPS (Cost=29 Card=13 Bytes=2132)
3 2 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=13 Bytes=1196)
4 2 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE) (Cost=1 Card=1)

SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=13 Bytes=2132)
1 0 HASH JOIN (Cost=7 Card=13 Bytes=2132)
2 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=13 Bytes=1196)
3 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=1307 Bytes=94104)

3.開啟查詢重寫，只有使用帶FOR ALL (INDEXED) COLUMNS子句的ANALYZE TABLE去收集統計資訊，CBO的結果才是正確的，使用其他方式收集統計資訊，CBO的查詢結果都是錯誤的。

SQL> analyze table plt_plat delete statistics;

Table analyzed.

SQL> alter session set optimizer_mode = choose;

Session altered.

SQL> analyze table plt_plat compute statistics;

Table analyzed.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=155 Bytes=20460)
1 0 HASH JOIN (Cost=7 Card=155 Bytes=20460)
2 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=84 Bytes=6048)
3 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=20040)

SQL> analyze table plt_plat delete statistics;

Table analyzed.

SQL> exec dbms_stats.gather_table_stats(user, 'PLT_PLAT', method_opt => 'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=84 Bytes=11928)
1 0 HASH JOIN (Cost=7 Card=84 Bytes=11928)
2 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=151 Bytes=11778)
3 1 TABLE ACCESS (FULL) OF 'PLT_PLAT' (Cost=3 Card=334 Bytes=21376)

SQL> exec dbms_stats.delete_table_stats(user, 'PLT_PLAT');

PL/SQL procedure successfully completed.

SQL> analyze table plt_plat compute statistics for all columns;

Table analyzed.

SQL> select pp.id, pp.plat_name, pp.plat_class, city.id, city.plat_name, city.plat_class
2 ,province.id,province.plat_name,province.plat_class
3 from plt_plat pp,
4 plt_plat city,
5 plt_plat province
6 where pp.plat_father=city.id
7 and city.plat_father=province.id
8 and pp.plat_class=4
9 ;

149 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'PLT_PLAT'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'PLT_PLAT'
6 5 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'IND_PLT_PLAT_PLAT_FATHER' (NON-UNIQUE)