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中查看執行計劃的常用方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！