利用NO_MERGE解决数据字典视图访问低效

转自:

数据库的数据字典视图的访问一般都比较迅速，因为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

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倍多。