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;
   

4. 
   

5. PL/SQL procedure successfully completed.
   

6. 
   

7. SQL> exec :b :=70000;
   

8. 
   

9. PL/SQL procedure successfully completed.
   

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

11. 
    

12. Explained.
    

13. 
    

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

15. 
    

16. PLAN_TABLE_OUTPUT
    

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

18. Plan hash value: 2213771543
    

19. 
    

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
   

6. 
   

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;
   

2. 
   

3.   COUNT(*)
   

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

5.     136544
   

6. 
   

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

8. 
   

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
    

14. 
    

15. Plan hash value: 853742775
    

16. 
    

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

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

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

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

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

22. 
    

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. 。。。省略部分
   

6. 
   

7. 31206 rows selected.
   

8. 
   

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

10. 
    

11. PLAN_TABLE_OUTPUT
    

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

13. SQL_ID  7m7b6un3xtss3, child number 0
    

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

15. select status from t where owner=user
    

16. 
    

17. Plan hash value: 47527108
    

18. 
    

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. ------------------------------------------------------------------------------------------
    

26. 
    

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

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

29. 
    

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

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

32. 
    

33. Outline Data
    

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

35. 
    

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.   */
    

46. 
    

47. Predicate Information (identified by operation id):
    

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

49. 
    

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

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

52. 
    

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

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

55. 
    

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

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

58. 
    

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;
   

2. 
   

3. Session altered.
   

4. 
   

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

6. VALID
   

7. VALID
   

8. VALID
   

9. 。。。。。省略部分

10. 
    

11. 31206 rows selected.
    

12. 
    

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

14. 
    

15. PLAN_TABLE_OUTPUT
    

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

17. SQL_ID  7m7b6un3xtss3, child number 1
    

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

19. select status from t where owner=user
    

20. 
    

21. Plan hash value: 47527108
    

22. 
    

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. -------------------------------------------------------------------------------------------------------------
    

30. 
    

31. Predicate Information (identified by operation id):
    

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

33. 
    

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';
   

2. 
   

3. SQL_TEXT                                 SQL_ID        VERSION_COUNT EXECUTIONS
   

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

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

6. 
   

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

8. 
   

9. PLAN_TABLE_OUTPUT
   

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

11. SQL_ID  7m7b6un3xtss3, child number 0
    

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

13. select status from t where owner=user
    

14. 
    

15. Plan hash value: 47527108
    

16. 
    

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. ------------------------------------------------------------------------------------------
    

24. 
    

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

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

27. 
    

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

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

30. 
    

31. Outline Data
    

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

33. 
    

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.   */
    

44. 
    

45. Predicate Information (identified by operation id):
    

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

47. 
    

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

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

50. 
    

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

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

53. 
    

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';
   

2. 
   

3. no rows selected
   

4. 
   

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

6. 
   

7. PLAN_TABLE_OUTPUT
   

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

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

10. 
    

11. 
    

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

13. 
    

14. PLAN_TABLE_OUTPUT
    

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

16. SQL_ID 7m7b6un3xtss3
    

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

18. select status from t where owner=user
    

19. 
    

20. Plan hash value: 47527108
    

21. 
    

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 |
    

28. 
    

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

4、10046方式

1. SQL> alter session set tracefile_identifier=plan_10046;
   

2. 
   

3. Session altered.
   

4. 
   

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

6. 
   

7. Session altered.
   

8. 
   

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

10. VALID
    

11. VALID
    

12. VALID
    

13. 。。。省略部分
    

14. 
    

15. 31206 rows selected.
    

16. 
    

17. SQL>
    

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

19. 
    

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
   

2. 
   

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

4. 
   

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

6. 
   

7. 
   

8. SQL ID: 57fcnar0x2buq
   

9. Plan Hash: 47527108
   

10. select status
    

11. from
    

12.  t where owner=user
    

13. 
    

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
    

21. 
    

22. Misses in library cache during parse: 1
    

23. Optimizer mode: ALL_ROWS
    

24. Parsing user id: SYS
    

25. 
    

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