如何快速得到真实的执行计划

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了如何快速得到真实的执行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4239字，纯文字阅读大概需要7分钟。

内容图文

准备工作： 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 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');

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.

内容总结

以上是互联网集市为您收集整理的如何快速得到真实的执行计划全部内容，希望文章能够帮你解决如何快速得到真实的执行计划所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端