AWR报告的TOP SQL里截取到的。CPU消耗近30秒。SQL文本如下：
SELECT DISTINCT AB10.BAE004 AS AB10_BAE004,
AB10.AAB033 AS AB10_AAB033,
AB10.BAE203 AS AB10_BAE203,
AB10.BAZ010 AS AB10_BAZ010,
AB10.BAE265 AS AB10_BAE265,
AB10.BAE050 AS AB10_BAE050,
AB10.BAE175 AS AB10_BAE175,
AB10.AAE009 AS AB10_AAE009,
AB10.AAE010 AS AB10_AAE010,
AB10.AAE008 AS AB10_AAE008,
AB10.AAE020 AS AB10_AAE020,
AB10.BAE115 AS AB10_BAE115,
AB10.AAE011 AS AB10_AAE011,
AB10.AAE036 AS AB10_AAE036,
AB10.BAB024 AS AB10_BAB024,
AB10.BAE308 AS AB10_BAE308,
AB10.BAE309 AS AB10_BAE309,
AB10.BAE310 AS AB10_BAE310,
AB10.BAE324 AS AB10_BAE324,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END) AS AB09_AAE140
FROM AB10, AB09
WHERE AB10.BAE203 = AB09.BAE203
AND AB09.AAB033 = AB10.AAB033
AND NVL(AB09.AAE100, '0') = '1'
AND NVL(AB09.BAE159, '0') = '0'
AND AB10.BAE175 IN ('1', '2')
AND AB10.BAZ010 = 36915418
AND AB10.AAB033 = 3
查询所引用AB10,AB09都是表，而非视图。
用SQLPLUS登录执行，速度非常快，仅0.07秒，查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));
Plan hash value: 2652663149
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 1 | 1 |00:00:00.02 | 72 |
|* 2 | TABLE ACCESS BY INDEX ROWID | AB09 | 1 | 1 | 1788 |00:00:00.02 | 72 |
| 3 | NESTED LOOPS | | 1 | 5 | 1790 |00:00:00.30 | 15 |
|* 4 | TABLE ACCESS BY INDEX ROWID| AB10 | 1 | 3 | 1 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_AB10_BAZ010_AAB033 | 1 | 3 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | IDX_AB09_BAE203 | 1 | 11 | 1788 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((NVL("AB09"."BAE159",'0')='0' AND NVL("AB09"."AAE100",'0')='1' AND
"AB09"."AAB033"="AB10"."AAB033"))
4 - filter(("AB10"."BAE175"='1' OR "AB10"."BAE175"='2'))
5 - access("AB10"."BAZ010"=36915418)
filter(TO_NUMBER("AB10"."AAB033")=3)
6 - access("AB10"."BAE203"="AB09"."BAE203")
而查看这个SQL在AWR报告中的计划，太令人惊讶了，执行计划明显变了，更不可思议的是ID从11到14多出了AB02表的执行计划信息，而查询中根本没有引用这个表。
SQL> select * from table(dbms_xplan.display_awr('b1vkyc3yj7rcs',null,null,'all'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
--------
SQL_ID b1vkyc3yj7rcs
--------------------
SELECT DISTINCT AB10.BAE004 AS AB10_BAE004, AB10.AAB033 AS AB10_AAB033,
AB10.BAE203 AS AB10_BAE203, AB10.BAZ010 AS AB10_BAZ010, AB10.BAE265 AS AB10_BAE265,
AB10.BAE050 AS AB10_BAE050, AB10.BAE175 AS AB10_BAE175, AB10.AAE009 AS
AB10_AAE009, AB10.AAE010 AS AB10_AAE010, AB10.AAE008 AS AB10_AAE008,
AB10.AAE020 AS AB10_AAE020, AB10.BAE115 AS AB10_BAE115, AB10.AAE011 AS AB10_AAE011,
AB10.AAE036 AS AB10_AAE036, AB10.BAB024 AS AB10_BAB024, AB10.BAE308 AS
AB10_BAE308, AB10.BAE309 AS AB10_BAE309, AB10.BAE310 AS AB10_BAE310,
AB10.BAE324 AS AB10_BAE324, (CASE WHEN aae140 IN ('31','35','37','3A','3B') THEN '31' ELSE
aae140 END) AS AB09_AAE140 FROM AB10,AB09 WHERE AB10.BAE203 = AB09.BAE203 AND
AB09.AAB033 = AB10.AAB033 AND NVL(AB09.AAE100, '0') = '1' AND NVL(AB09.BAE159, '0') =
'0' AND AB10.BAE175 IN ('1','2') and AB10.baz010=36915418 and ab10.aab033=3
Plan hash value: 3088278305
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | HASH UNIQUE | | 1 | 259 | 15 (7)| 00:00:01 |
| 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | AB09 | 11 | 418 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 259 | 14 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 221 | 12 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| AB10 | 1 | 177 | 7 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | IDX_AB10_BAZ010_AAB033 | 3 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| AB09 | 1 | 44 | 5 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | IDX_AB09_BAE203 | 11 | | 2 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | IDX_AB09_BAE203 | 11 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | AB02 | 1 | 29 | 3 (0)| 00:00:01 |
| 12 | INDEX UNIQUE SCAN | PK_AB02 | 1 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | AB02 | 1 | 29 | 3 (0)| 00:00:01 |
| 14 | INDEX UNIQUE SCAN | PK_AB02 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5D81F689
3 - SEL$5D81F689 / AB09@SEL$4
6 - SEL$5D81F689 / AB10@SEL$2
7 - SEL$5D81F689 / AB10@SEL$2
8 - SEL$5D81F689 / AB09@SEL$7
9 - SEL$5D81F689 / AB09@SEL$7
10 - SEL$5D81F689 / AB09@SEL$4
11 - SEL$CF5359D5 / AB02@SEL$9
12 - SEL$CF5359D5 / AB02@SEL$9
13 - SEL$ABDE6DFF / AB02@SEL$6
14 - SEL$ABDE6DFF / [email=AB02@SEL$6]AB02@SEL$6[/email]
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
已用时间: 00: 00: 00.02
后经查实，是VPD权限惹的祸，附件了一个AB02的子查询。
[ 本帖最后由 wei-xh 于 2010-6-29 12:46 编辑 ]
SELECT DISTINCT AB10.BAE004 AS AB10_BAE004,
AB10.AAB033 AS AB10_AAB033,
AB10.BAE203 AS AB10_BAE203,
AB10.BAZ010 AS AB10_BAZ010,
AB10.BAE265 AS AB10_BAE265,
AB10.BAE050 AS AB10_BAE050,
AB10.BAE175 AS AB10_BAE175,
AB10.AAE009 AS AB10_AAE009,
AB10.AAE010 AS AB10_AAE010,
AB10.AAE008 AS AB10_AAE008,
AB10.AAE020 AS AB10_AAE020,
AB10.BAE115 AS AB10_BAE115,
AB10.AAE011 AS AB10_AAE011,
AB10.AAE036 AS AB10_AAE036,
AB10.BAB024 AS AB10_BAB024,
AB10.BAE308 AS AB10_BAE308,
AB10.BAE309 AS AB10_BAE309,
AB10.BAE310 AS AB10_BAE310,
AB10.BAE324 AS AB10_BAE324,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END) AS AB09_AAE140
FROM AB10, AB09
WHERE AB10.BAE203 = AB09.BAE203
AND AB09.AAB033 = AB10.AAB033
AND NVL(AB09.AAE100, '0') = '1'
AND NVL(AB09.BAE159, '0') = '0'
AND AB10.BAE175 IN ('1', '2')
AND AB10.BAZ010 = 36915418
AND AB10.AAB033 = 3
查询所引用AB10,AB09都是表，而非视图。
用SQLPLUS登录执行，速度非常快，仅0.07秒，查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));
Plan hash value: 2652663149
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 1 | 1 |00:00:00.02 | 72 |
|* 2 | TABLE ACCESS BY INDEX ROWID | AB09 | 1 | 1 | 1788 |00:00:00.02 | 72 |
| 3 | NESTED LOOPS | | 1 | 5 | 1790 |00:00:00.30 | 15 |
|* 4 | TABLE ACCESS BY INDEX ROWID| AB10 | 1 | 3 | 1 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_AB10_BAZ010_AAB033 | 1 | 3 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | IDX_AB09_BAE203 | 1 | 11 | 1788 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((NVL("AB09"."BAE159",'0')='0' AND NVL("AB09"."AAE100",'0')='1' AND
"AB09"."AAB033"="AB10"."AAB033"))
4 - filter(("AB10"."BAE175"='1' OR "AB10"."BAE175"='2'))
5 - access("AB10"."BAZ010"=36915418)
filter(TO_NUMBER("AB10"."AAB033")=3)
6 - access("AB10"."BAE203"="AB09"."BAE203")
而查看这个SQL在AWR报告中的计划，太令人惊讶了，执行计划明显变了，更不可思议的是ID从11到14多出了AB02表的执行计划信息，而查询中根本没有引用这个表。
SQL> select * from table(dbms_xplan.display_awr('b1vkyc3yj7rcs',null,null,'all'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
--------
SQL_ID b1vkyc3yj7rcs
--------------------
SELECT DISTINCT AB10.BAE004 AS AB10_BAE004, AB10.AAB033 AS AB10_AAB033,
AB10.BAE203 AS AB10_BAE203, AB10.BAZ010 AS AB10_BAZ010, AB10.BAE265 AS AB10_BAE265,
AB10.BAE050 AS AB10_BAE050, AB10.BAE175 AS AB10_BAE175, AB10.AAE009 AS
AB10_AAE009, AB10.AAE010 AS AB10_AAE010, AB10.AAE008 AS AB10_AAE008,
AB10.AAE020 AS AB10_AAE020, AB10.BAE115 AS AB10_BAE115, AB10.AAE011 AS AB10_AAE011,
AB10.AAE036 AS AB10_AAE036, AB10.BAB024 AS AB10_BAB024, AB10.BAE308 AS
AB10_BAE308, AB10.BAE309 AS AB10_BAE309, AB10.BAE310 AS AB10_BAE310,
AB10.BAE324 AS AB10_BAE324, (CASE WHEN aae140 IN ('31','35','37','3A','3B') THEN '31' ELSE
aae140 END) AS AB09_AAE140 FROM AB10,AB09 WHERE AB10.BAE203 = AB09.BAE203 AND
AB09.AAB033 = AB10.AAB033 AND NVL(AB09.AAE100, '0') = '1' AND NVL(AB09.BAE159, '0') =
'0' AND AB10.BAE175 IN ('1','2') and AB10.baz010=36915418 and ab10.aab033=3
Plan hash value: 3088278305
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | HASH UNIQUE | | 1 | 259 | 15 (7)| 00:00:01 |
| 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | AB09 | 11 | 418 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 259 | 14 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 221 | 12 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| AB10 | 1 | 177 | 7 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | IDX_AB10_BAZ010_AAB033 | 3 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| AB09 | 1 | 44 | 5 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | IDX_AB09_BAE203 | 11 | | 2 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | IDX_AB09_BAE203 | 11 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | AB02 | 1 | 29 | 3 (0)| 00:00:01 |
| 12 | INDEX UNIQUE SCAN | PK_AB02 | 1 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | AB02 | 1 | 29 | 3 (0)| 00:00:01 |
| 14 | INDEX UNIQUE SCAN | PK_AB02 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5D81F689
3 - SEL$5D81F689 / AB09@SEL$4
6 - SEL$5D81F689 / AB10@SEL$2
7 - SEL$5D81F689 / AB10@SEL$2
8 - SEL$5D81F689 / AB09@SEL$7
9 - SEL$5D81F689 / AB09@SEL$7
10 - SEL$5D81F689 / AB09@SEL$4
11 - SEL$CF5359D5 / AB02@SEL$9
12 - SEL$CF5359D5 / AB02@SEL$9
13 - SEL$ABDE6DFF / AB02@SEL$6
14 - SEL$ABDE6DFF / [email=AB02@SEL$6]AB02@SEL$6[/email]
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
已用时间: 00: 00: 00.02
后经查实，是VPD权限惹的祸，附件了一个AB02的子查询。
[ 本帖最后由 wei-xh 于 2010-6-29 12:46 编辑 ]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-667964/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
接到阿里的面试电话了
请登录后发表评论
登录
全部评论
