首页 > 代码库 > 如何快速得到真实的执行计划
如何快速得到真实的执行计划
准备工作:
通常我们对于执行时间很长的sql查看执行计划:
create table zbdba as select * from dba_objects; create table zbdba1 as select * from dba_objects; create index zbdba_owner on zbdba(owner); create index zbdba1_owner on zbdba1(owner); exec dbms_stats.gather_table_stats(user, 'ZBDBA', method_opt => 'FOR ALL COLUMNS SIZE 1'); exec dbms_stats.gather_table_stats(user, 'ZBDBA1', method_opt => 'FOR ALL COLUMNS SIZE 1');
通常我们对于执行时间很长的sql查看执行计划:
explain plan for select zbdba.object_name from zbdba,zbdba1 where zbdba.owner=zbdba1.owner SCOTT@orcl11g>select plan_table_output from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1287183320 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 207M| 7330M| | 1198 (55)| 00:00:15 | |* 1 | HASH JOIN | | 207M| 7330M| 1272K| 1198 (55)| 00:00:15 | | 2 | INDEX FAST FULL SCAN| ZBDBA1_OWNER | 72068 | 422K| | 48 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | ZBDBA | 72067 | 2181K| | 288 (1)| 00:00:04 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ZBDBA"."OWNER"="ZBDBA1"."OWNER") 15 rows selected.
但是这种执行计划不一定是准确的。那我们怎么能才能快速的得到准确的执行计划呢?
真实的执行计划就是已经执行的sql
那么
select zbdba.object_name from zbdba,zbdba1 where zbdba.owner=zbdba1.owner; 你不需要一直等,ctrl+c中断即可 SCOTT@orcl11g>select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select zbdba.object_name from zbdba,zbdba1 where zbdba.owner=zbdba1.owner%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER ------------------------------------------------------------ -------------------------- ---------- ------------ select zbdba.object_name from zbdba,zbdba1 where zbdba.owner fr4g7ypwx5krq 2043857654 0 =zbdba1.owner SCOTT@orcl11g>select * from table(dbms_xplan.display_cursor('fr4g7ypwx5krq',0,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ SQL_ID fr4g7ypwx5krq, child number 0 ------------------------------------- select zbdba.object_name from zbdba,zbdba1 where zbdba.owner=zbdba1.owner Plan hash value: 1287183320 ---------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | |* 1 | HASH JOIN | | 207M| 3024K| 1862K| 7066K (0)| | 2 | INDEX FAST FULL SCAN| ZBDBA1_OWNER | 72068 | | | | | 3 | TABLE ACCESS FULL | ZBDBA | 72067 | | | | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ZBDBA"."OWNER"="ZBDBA1"."OWNER") Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 27 rows selected.这样就快速从内存中到了真实的执行计划
如何快速得到真实的执行计划
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。