Oracle SQL执行计划变更的问题(二)
30392 56s18gn1k19yp 947531627 2012-02-24 05:00:23
30391 56s18gn1k19yp 947531627 2012-02-24 04:00:11
30388 56s18gn1k19yp 947531627 2012-02-24 01:00:46
30383 56s18gn1k19yp 947531627 2012-02-23 20:00:15
30380 56s18gn1k19yp 947531627 2012-02-23 17:00:56
30377 56s18gn1k19yp 947531627 2012-02-23 14:00:37
30377 56s18gn1k19yp 2658265176 2012-02-23 14:00:37
我们注意到最近一次3月1号20点左右，执行计划发生了变化。
具体查看这两种执行计划有什么区别：
select sql_id,plan_hash_value,id,operation,options,object_owner,object_name,depth,cost,timestamp
from DBA_HIST_SQL_PLAN
where sql_id ='56s18gn1k19yp'
and plan_hash_value in (947531627,2658265176);
SQL> select plan_hash_value,id,operation,options,object_name,depth,cost,timestamp
2 from DBA_HIST_SQL_PLAN www.2cto.com
3 where sql_id ='56s18gn1k19yp'
4 and plan_hash_value in (947531627,2658265176);
PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP
--------------- --- -------------------- -------------------- ------------------------------- ----- ---------- -------------------
947531627 0 SELECT STATEMENT 0 84 2011-09-27 04:33:34
947531627 1 COUNT STOPKEY 1 2011-09-27 04:33:34
947531627 2 VIEW 2 84 2011-09-27 04:33:34
947531627 3 SORT ORDER BY STOPKEY 3 84 2011-09-27 04:33:34
947531627 4 TABLE ACCESS BY INDEX ROWID BLOG_USER 4 3 2011-09-27 04:33:34
947531627 5 NESTED LOOPS 5 83 2011-09-27 04:33:34
947531627 6 HASH JOIN OUTER 6 17 2011-09-27 04:33:34
947531627 7 TABLE ACCESS BY INDEX ROWID CIRCLE_PAPER_MAIN 7 13 2011-09-27 04:33:34
947531627 8 INDEX RANGE SCAN IDX_CIRCLE_PAPER_MAIN_CID 8 3 2011-09-27 04:33:34
947531627 9 TABLE ACCESS BY INDEX ROWID CIRCLE_DISCUSS_CLASS 7 3 2011-09-27 04:33:34 www.2cto.com
947531627 10 INDEX RANGE SCAN IDX_CIRCLE_DISCUSS_CLASS_CID 8 1 2011-09-27 04:33:34
947531627 11 INDEX RANGE SCAN IDX_BLOG_USER_BLOGID 6 2 2011-09-27 04:33:34
2658265176 0 SELECT STATEMENT 0 9516 2012-02-22 15:54:33
2658265176 1 COUNT STOPKEY 1 2012-02-22 15:54:33
2658265176 2 VIEW 2 9516 2012-02-22 15:54:33
2658265176 3 SORT ORDER BY STOPKEY 3 9516 2012-02-22 15:54:33
2658265176 4 HASH JOIN RIGHT OUTER 4 8181 2012-02-22 15:54:33
2658265176 5 TABLE ACCESS BY INDEX ROWID CIRCLE_DISCUSS_CLASS 5 3 2012-02-22 15:54:33
2658265176 6 INDEX RANGE SCAN IDX_CIRCLE_DISCUSS_CLASS_CID 6 1 2012-02-22 15:54:33
2658265176 7 HASH JOIN 5 8177 2012-02-22 15:54:33
2658265176 8 TABLE ACCESS FULL CIRCLE_PAPER_MAIN 6 953 2012-02-22