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