准备工作： 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,
准备工作：
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 <div>本文来源gaodai.ma#com搞#代!码网_</div>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.ownerSCOTT@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.ownerSCOTT@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 wherezbdba.owner=zbdba1.ownerPlan 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 level27 rows selected.
这样就快速从内存中到了真实的执行计划
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：如何快速得到真实的执行计划
转载请注明原文链接：如何快速得到真实的执行计划
