甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

QQ :532337155
电话：028-86922220
 
成都网站建设设计

将想法与焦点和您一起共享

oracle中查看执行计划的常用方法

本篇内容介绍了“oracle中查看执行计划的常用方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

公司主营业务：成都网站设计、成都网站制作、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。成都创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。成都创新互联推出高安免费做网站回馈大家。


本文介绍了oracle中查看执行计划常用的方法。
1、EXPLAIN PLAN命令
2、AUTOTRACE开关
3、DBMS_XPLAN
4、10046事件

1、EXPLAIN PLAN命令

  1. SQL> var a number;

  2. SQL> var b number;

  3. SQL> exec :a :=0;

  5. PL/SQL procedure successfully completed.

  7. SQL> exec :b :=70000;

  9. PL/SQL procedure successfully completed.

  10. SQL> explain plan for select count(*) from t where object_id between :a and :b;

  12. Explained.

  14. SQL> select * from table(dbms_xplan.display);

  16. PLAN_TABLE_OUTPUT

  17. --------------------------------------------------------------------------------

  18. Plan hash value: 2213771543

  20. ----------------------------------------------------------------------------

  21. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

  22. ----------------------------------------------------------------------------

  23. | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

  24. | 1 | SORT AGGREGATE | | 1 | 5 | | |

  25. |* 2 | FILTER | | | | | |

  26. |* 3 | INDEX RANGE SCAN| T_IDX | 180 | 900 | 2 (0)| 00:00:01 |

  27. ----------------------------------------------------------------------------

  28. 。。。。省略部分

  1. SQL> set autot traceonly

  2. SQL> select count(*) from t where object_id between :a and :b;

  3. Execution Plan

  4. ----------------------------------------------------------

  5. Plan hash value: 2213771543

  7. ----------------------------------------------------------------------------

  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

  9. ----------------------------------------------------------------------------

  10. | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

  11. | 1 | SORT AGGREGATE | | 1 | 5 | | |

  12. |* 2 | FILTER | | | | | |

  13. |* 3 | INDEX RANGE SCAN| T_IDX | 180 | 900 | 2 (0)| 00:00:01 |

  14. 。。。省略部分

  1. SQL> select count(*) from t where object_id between :a and :b;

  3.   COUNT(*)

  4. ----------

  5.     136544

  7. SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

  9. PLAN_TABLE_OUTPUT

  10. --------------------------------------------------------------------------------

  11. SQL_ID  9cgwqzzvtw8wc, child number 0

  12. -------------------------------------

  13.  select count(*) from t where object_id between :a and :b

  15. Plan hash value: 853742775

  17. --------------------------------------------------------------------------------

  18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

  19. --------------------------------------------------------------------------------

  20. | 0 | SELECT STATEMENT | | | | 45 (100)| |

  21. | 1 | SORT AGGREGATE | | 1 | 5 | | |

  23. PLAN_TABLE_OUTPUT

  24. --------------------------------------------------------------------------------

  25. |* 2 | FILTER | | | | | |

  26. |* 3 | INDEX FAST FULL SCAN| T_IDX | 50561 | 246K| 45 (0)| 00:00:01 |

。。。省略部分

==》真实的执行计划应该是INDEX FAST FULL SCAN

3、DBMS_XPLAN

DBMS_XPLANB包的常用子程序为：

DISPLAY：配合explain plan for 使用

DISPLAY_CURSOR：适用于sqlplus刚刚执行过的sql执行计划，或在存储在shared pool中的执行计划。

DISPLAY_AWR：sql的执行计划从shared pool中aga out后，如果执行计划被采集到awr报告中，那么就可以使用该方法查看执行计划。

示例：

  1. SQL> select status from t where owner=user;

  2. VALID

  3. VALID

  4. VALID

  5. 。。。省略部分

  7. 31206 rows selected.

  9. SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

  11. PLAN_TABLE_OUTPUT

  12. -----------------------------------------------------------------------------------------

  13. SQL_ID  7m7b6un3xtss3, child number 0

  14. -------------------------------------

  15. select status from t where owner=user

  17. Plan hash value: 47527108

  19. ------------------------------------------------------------------------------------------

  20. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

  21. ------------------------------------------------------------------------------------------

  22. | 0 | SELECT STATEMENT | | | | 13 (100)| |

  23. | 1 | TABLE ACCESS BY INDEX ROWID| T | 2255 | 27060 | 13 (0)| 00:00:01 |

  24. |* 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0)| 00:00:01 |

  25. ------------------------------------------------------------------------------------------

  27. Query Block Name / Object Alias (identified by operation id):

  28. -------------------------------------------------------------

  30.    1 - SEL$1 / T@SEL$1

  31.    2 - SEL$1 / T@SEL$1

  33. Outline Data

  34. -------------

  36.   /*+

  37.       BEGIN_OUTLINE_DATA

  38.       IGNORE_OPTIM_EMBEDDED_HINTS

  39.       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

  40.       DB_VERSION('11.2.0.1')

  41.       ALL_ROWS

  42.       OUTLINE_LEAF(@"SEL$1")

  43.       INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "DESC_T_INX")

  44.       END_OUTLINE_DATA

  45.   */

  47. Predicate Information (identified by operation id):

  48. ---------------------------------------------------

  50.    2 - access("T"."SYS_NC00016$"=SYS_OP_DESCEND(USER@!))

  51.        filter(SYS_OP_UNDESCEND("T"."SYS_NC00016$")=USER@!)

  53. Column Projection Information (identified by operation id):

  54. -----------------------------------------------------------

  56.    1 - "STATUS"[VARCHAR2,7]

  57.    2 - "T".ROWID[ROWID,10], "T"."SYS_NC00016$"[RAW,46]

  59. 46 rows selected.

==>1.相比AUTOTRACE开关来说看不到相关的统计信息，而且要等到语句执行完成。但获得的执行计划是真实的

       2.这里format参数为advanced,相比较于参数all,多了Outline Data这部分的信息输出

       3.这里的Rows列值为估计值，要想看到真实值可以将format参数设置为'ALLSTATS LAST'

format参数设置为'ALLSTATS LAST'示例：

  1. SQL> alter session set statistics_level =all;

  3. Session altered.

  5.  SQL> select status from t where owner=user;

  6. VALID

  7. VALID

  8. VALID

  9. 。。。。。省略部分

  11. 31206 rows selected.

  13. SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

  15. PLAN_TABLE_OUTPUT

  16. --------------------------------------------------------------------------------------------------------------

  17. SQL_ID  7m7b6un3xtss3, child number 1

  18. -------------------------------------

  19. select status from t where owner=user

  21. Plan hash value: 47527108

  23. -------------------------------------------------------------------------------------------------------------

  24. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

  25. -------------------------------------------------------------------------------------------------------------

  26. | 0 | SELECT STATEMENT | | 1 | | 31206 |00:00:00.05 | 5555 | 829 |

  27. | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2255 | 31206 |00:00:00.05 | 5555 | 829 |

  28. |* 2 | INDEX RANGE SCAN | DESC_T_INX | 1 | 70 | 31206 |00:00:00.02 | 2190 | 114 |

  29. -------------------------------------------------------------------------------------------------------------

  31. Predicate Information (identified by operation id):

  32. ---------------------------------------------------

  34.    2 - access("T"."SYS_NC00016$"=SYS_OP_DESCEND(USER@!))

  35.        filter(SYS_OP_UNDESCEND("T"."SYS_NC00016$")=USER@!)

==》和advanced参数相比，少了部分输出，但是能够看到每一步获取的实际记录数。

 输入sqlid来查看执行计划示例：

  1. SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select status from t where owner=user';

  3. SQL_TEXT                                 SQL_ID        VERSION_COUNT EXECUTIONS

  4. ---------------------------------------- ------------- ------------- ----------

  5. select status from t where owner=user 7m7b6un3xtss3             2          3

  7. SQL> select * from table(dbms_xplan.display_cursor('7m7b6un3xtss3',0,'advanced'));

  9. PLAN_TABLE_OUTPUT

  10. --------------------------------------------------------------------------------

  11. SQL_ID  7m7b6un3xtss3, child number 0

  12. -------------------------------------

  13. select status from t where owner=user

  15. Plan hash value: 47527108

  17. ------------------------------------------------------------------------------------------

  18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

  19. ------------------------------------------------------------------------------------------

  20. | 0 | SELECT STATEMENT | | | | 13 (100)| |

  21. | 1 | TABLE ACCESS BY INDEX ROWID| T | 2255 | 27060 | 13 (0)| 00:00:01 |

  22. |* 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0)| 00:00:01 |

  23. ------------------------------------------------------------------------------------------

  25. Query Block Name / Object Alias (identified by operation id):

  26. -------------------------------------------------------------

  28.    1 - SEL$1 / T@SEL$1

  29.    2 - SEL$1 / T@SEL$1

  31. Outline Data

  32. -------------

  34.   /*+

  35.       BEGIN_OUTLINE_DATA

  36.       IGNORE_OPTIM_EMBEDDED_HINTS

  37.       OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

  38.       DB_VERSION('11.2.0.1')

  39.       ALL_ROWS

  40.       OUTLINE_LEAF(@"SEL$1")

  41.       INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "DESC_T_INX")

  42.       END_OUTLINE_DATA

  43.   */

  45. Predicate Information (identified by operation id):

  46. ---------------------------------------------------

  48.    2 - access("T"."SYS_NC00016$"=SYS_OP_DESCEND(USER@!))

  49.        filter(SYS_OP_UNDESCEND("T"."SYS_NC00016$")=USER@!)

  51. Column Projection Information (identified by operation id):

  52. -----------------------------------------------------------

  54.    1 - "STATUS"[VARCHAR2,7]

  55.    2 - "T".ROWID[ROWID,10], "T"."SYS_NC00016$"[RAW,46]

display_awr 示例：

  1. SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select status from t where owner=user';

  3. no rows selected

  5. SQL> select * from table(dbms_xplan.display_cursor('7m7b6un3xtss3',0,'advanced'));

  7. PLAN_TABLE_OUTPUT

  8. -------------------------------------------------------------------------

  9. SQL_ID: 7m7b6un3xtss3, child number: 0 cannot be found

  12. SQL> select * from table(dbms_xplan.display_awr('7m7b6un3xtss3'));

  14. PLAN_TABLE_OUTPUT

  15. -------------------------------------------------------------------------

  16. SQL_ID 7m7b6un3xtss3

  17. --------------------

  18. select status from t where owner=user

  20. Plan hash value: 47527108

  22. --------------------------------------------------------------------------

  23. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

  24. --------------------------------------------------------------------------

  25. | 0 | SELECT STATEMENT | | | | 13 (100)| |

  26. | 1 | TABLE ACCESS BY INDEX ROWID| T | 2255 | 27060 | 13 (0)| 00:00:01 |

  27. | 2 | INDEX RANGE SCAN | DESC_T_INX | 70 | | 10 (0)| 00:00:01 |

==>1、相同的执行计划也可以通过@?/rdbms/admin/awrsqrpt 命令获取。

4、10046方式

  1. SQL> alter session set tracefile_identifier=plan_10046;

  3. Session altered.

  5. SQL> alter session set events '10046 trace name context forever,level 12';

  7. Session altered.

  9.  SQL> select status from t where owner=user;

  10. VALID

  11. VALID

  12. VALID

  13. 。。。省略部分

  15. 31206 rows selected.

  17. SQL>

  18. SQL> alter session set events '10046 trace name context off';

  20. Session altered.

==>可以根据diagnostic_dest 参数找到plan_10046的文件。

使用tkprof进行输出：

  1. [ora11@ora12c ~]$ tkprof /ora11_10/ora11/diag/rdbms/ora11/ora11/trace/ora11_ora_26758_PLAN_10046.trc PLAN_10046.trc

  3. TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 15 21:14:49 2017

  5. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

  8. SQL ID: 57fcnar0x2buq

  9. Plan Hash: 47527108

  10. select status

  11. from

  12.  t where owner=user

  14. call count cpu    elapsed       disk      query current rows

  15. ------- ------  -------- ---------- ---------- ---------- ----------  ----------

  16. Parse        1      0.00       0.00         11         57          0           0

  17. Execute 1      0.00       0.00          0          0          0           0

  18. Fetch     2082      0.13       0.13       1007       5555          0       31206

  19. ------- ------  -------- ---------- ---------- ---------- ----------  ----------

  20. total     2084      0.14       0.13       1018       5612          0       31206

  22. Misses in library cache during parse: 1

  23. Optimizer mode: ALL_ROWS

  24. Parsing user id: SYS

  26. Rows Row Source Operation

  27. -------  ---------------------------------------------------

  28.   31206 TABLE ACCESS BY INDEX ROWID T (cr=5555 pr=1007 pw=0 time=76792 us cost=13 size=27060 card=2255)

  29.   31206 INDEX RANGE SCAN DESC_T_INX (cr=2190 pr=114 pw=0 time=29158 us cost=10 size=0 card=70)(object id 99885)

“oracle中查看执行计划的常用方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！


新闻名称：oracle中查看执行计划的常用方法
文章位置：http://chengdu.cdxwcx.cn/article/jooedc.html

其他资讯