資料庫的資料字典檢視的訪問一般都比較迅速，因為Oracle已經做了專門的最佳化工作，但是有些時候，幾個資料字典進行關聯的時候，CBO就沒有那麼智慧了，有時候會給出一些效率很低的執行計劃。

比如在嘗試構造一個大表的時候，想要構造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

連線到:
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 ptimizer=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 ptimizer=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倍多。

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

相關文章