春暖花开

世上无难事    (原创文章转载请注明出处)

  • 博客访问: 12494074
  • 博文数量: 5782
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-10 14:39
  • 认证徽章:
个人简介

Oracle , MySQL, SAP IQ, SAP HANA 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(5782)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: Linux

http://yangtingkun.itpub.net/post/468/485443 

 

数据库的数据字典视图的访问一般都比较迅速,因为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 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没有将两个视图的执行计划打散,而是直接对两个视图访问的结果进行笛卡儿积,而这正是我们希望的结果。

这个方法并不是特例,很多访问数据字典视图的性能问题,都可以通过这个方法来解决。

也有有人会置疑,从执行计划上看,明显第一个SQLRBO,而第二个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

可以看到,使用CBORBO情况要好很多,但是执行时间仍然是使用NO_MERGE4倍多。

阅读(2983) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册