利用NO_MERGE解決資料字典檢視訪問低效 ZT

比如在嘗試構造一個大表的時候，想要構造DBA_OBJECTS和另一張資料量不大的表DBA_SEQUENCES的笛卡兒積：

[oracle@localhost ~]$ sqlplus test/test

SQL*Plus: Release 9.2.0.4.0 - Production on 星期四 6月 4 16:39:58 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

連線到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> SET PAGES 100 LINES 128
SQL> COL PLAN_PLUS_EXP FORMAT A115
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM DBA_SEQUENCES, DBA_OBJECTS;

COUNT(*)
----------
4585276

已用時間: 00: 31: 19.33

這個查詢居然執行了半個小時之久。

SQL> SELECT COUNT(*) FROM DBA_SEQUENCES;

COUNT(*)
----------
146

已用時間: 00: 00: 00.08
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;

COUNT(*)
----------
31406

已用時間: 00: 00: 00.08

兩個檢視資料量都不算很大，笛卡兒積的結果無非也不過四百多萬，怎麼會執行這麼長時間呢：

SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM DBA_SEQUENCES, DBA_OBJECTS;

已解釋。

已用時間: 00: 00: 00.00
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | VIEW | DBA_OBJECTS | | | |
| 6 | UNION-ALL | | | | |
|* 7 | FILTER | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| OBJ$ | | | |
| 9 | NESTED LOOPS | | | | |
| 10 | TABLE ACCESS FULL | USER$ | | | |
|* 11 | INDEX RANGE SCAN | I_OBJ2 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| IND$ | | | |
|* 13 | INDEX UNIQUE SCAN | I_IND1 | | | |
| 14 | NESTED LOOPS | | | | |
| 15 | TABLE ACCESS FULL | USER$ | | | |
|* 16 | INDEX RANGE SCAN | I_LINK1 | | | |
| 17 | TABLE ACCESS FULL | USER$ | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | OBJ$ | | | |
|* 19 | INDEX RANGE SCAN | I_OBJ2 | | | |
|* 20 | INDEX UNIQUE SCAN | I_SEQ1 | | | |
-----------------------------------------------------------------------------------

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

7 - filter("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR
"SYS_ALIAS_1"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1
AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
11 - access("SYS_ALIAS_1"."OWNER#"="U"."USER#" AND "SYS_ALIAS_1"."LINKNAME" IS
NULL)
filter("SYS_ALIAS_1"."LINKNAME" IS NULL AND
"SYS_ALIAS_1"."NAME"<>'_default_auditing_options_' AND
"SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT')
12 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
13 - access("I"."OBJ#"=:B1)
16 - access("L"."OWNER#"="U"."USER#")
19 - access("U"."USER#"="O"."OWNER#")
20 - access("O"."OBJ#"="S"."OBJ#")

Note: rule based optimization

已選擇47行。

已用時間: 00: 00: 00.33

檢查執行計劃發現，由於兩個檢視的一些資料來自相同的內部CLUSTER表，於是CBO“聰明地”修改了執行計劃，將兩個檢視的查詢拆散，重新構造了執行計劃。

而事實證明這個執行計劃是十分糟糕的。由於單個檢視都是經過Oracle最佳化過的，因此單獨訪問任何一個檢視都沒有效能問題，於是利用NO_MERGE提示，避免Oracle將檢視的查詢拆散：

SQL> SET AUTOT ON
SQL> SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ COUNT(*)
2 FROM DBA_SEQUENCES A, DBA_OBJECTS B;

COUNT(*)
----------
4585276

已用時間: 00: 00: 00.60

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=204251 Card=1)
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN (CARTESIAN) (Cost=204251 Card=66724392)
3 2 VIEW OF 'DBA_OBJECTS' (Cost=26 Card=8169)
4 3 UNION-ALL
5 4 FILTER
6 5 NESTED LOOPS (Cost=12 Card=1 Bytes=135)
7 6 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=11 Card=1 Bytes=122)
8 6 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=1 Card=1 Bytes=13)
9 8 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' (Cost=2 Card=1 Bytes=26)
11 10 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE) (Cost=1 Card=8168)
12 4 HASH JOIN (Cost=14 Card=8168 Bytes=212368)
13 12 TABLE ACCESS (FULL) OF 'LINK$' (Cost=2 Card=82 Bytes=1066)
14 12 TABLE ACCESS (FULL) OF 'USER$' (Cost=11 Card=8168 Bytes=106184)
15 2 BUFFER (SORT) (Cost=204251 Card=8168)
16 15 VIEW OF 'DBA_SEQUENCES' (Cost=25 Card=8168)
17 16 NESTED LOOPS (Cost=25 Card=8168 Bytes=424736)
18 17 HASH JOIN (Cost=25 Card=8168 Bytes=318552)
19 18 TABLE ACCESS (FULL) OF 'USER$' (Cost=11 Card=8168 Bytes=106184)
20 18 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=11 Card=8168 Bytes=212368)
21 17 INDEX (UNIQUE SCAN) OF 'I_SEQ1' (UNIQUE)

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
68435 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到，二者的效率可謂天壤之別，一個需要30多分鐘，另一個執行不到1秒。從執行計劃上看，利用NO_MERGE提示後，Oracle沒有將兩個檢視的執行計劃打散，而是直接對兩個檢視訪問的結果進行笛卡兒積，而這正是我們希望的結果。

這個方法並不是特例，很多訪問資料字典檢視的效能問題，都可以透過這個方法來解決。

也有有人會置疑，從執行計劃上看，明顯第一個SQL是RBO，而第二個SQL由於加上了HINT，最佳化器採用CBO，是否由於CBO優於RBO導致效率的提升。

事實上，採用CBO，確實可以提高訪問效率，至少得到的執行計劃沒有那麼離譜，但是與NO_MERGE提示的效果相比，還是要遜色不少：

SQL> SELECT /*+ ALL_ROWS */ COUNT(*) FROM DBA_SEQUENCES, DBA_OBJECTS;

COUNT(*)
----------
4585276

已用時間: 00: 00: 02.78

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=50919 Card=1 Bytes=52)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=50919 Card=66724392 Bytes=3469668384)
3 2 TABLE ACCESS (FULL) OF 'USER$' (Cost=11 Card=8168 Bytes=106184)
4 2 HASH JOIN (Cost=40483 Card=66724392 Bytes=2602251288)
5 4 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=11 Card=8168 Bytes=212368)
6 4 MERGE JOIN (CARTESIAN) (Cost=32702 Card=66724392 Bytes=867417096)
7 6 VIEW OF 'DBA_OBJECTS' (Cost=26 Card=8169)
8 7 UNION-ALL
9 8 FILTER
10 9 NESTED LOOPS (Cost=12 Card=1 Bytes=135)
11 10 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=11 Card=1 Bytes=122)
12 10 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=1 Card=1 Bytes=13)
13 12 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
14 9 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' (Cost=2 Card=1 Bytes=26)
15 14 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE) (Cost=1 Card=8168)
16 8 HASH JOIN (Cost=14 Card=8168 Bytes=212368)
17 16 TABLE ACCESS (FULL) OF 'LINK$' (Cost=2 Card=82 Bytes=1066)
18 16 TABLE ACCESS (FULL) OF 'USER$' (Cost=11 Card=8168 Bytes=106184)
19 6 BUFFER (SORT) (Cost=32702 Card=8168 Bytes=106184)
20 19 INDEX (FAST FULL SCAN) OF 'I_SEQ1' (UNIQUE) (Cost=4 Card=8168 Bytes=106184)

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
68436 consistent gets
1 physical reads
0 redo size
379 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到，使用CBO比RBO情況要好很多，但是執行時間仍然是使用NO_MERGE的4倍多。