oracle中查看執(zhí)行計劃的常用方法

本篇內(nèi)容介紹了“oracle中查看執(zhí)行計劃的常用方法”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

公司主營業(yè)務(wù)：成都網(wǎng)站設(shè)計、成都網(wǎng)站制作、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。成都創(chuàng)新互聯(lián)是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)推出高安免費做網(wǎng)站回饋大家。


本文介紹了oracle中查看執(zhí)行計劃常用的方法。
1、EXPLAIN PLAN命令
2、AUTOTRACE開關(guān)
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 |

。。。省略部分

==》真實的執(zhí)行計劃應(yīng)該是INDEX FAST FULL SCAN

3、DBMS_XPLAN

DBMS_XPLANB包的常用子程序為：

DISPLAY：配合explain plan for 使用

DISPLAY_CURSOR：適用于sqlplus剛剛執(zhí)行過的sql執(zhí)行計劃，或在存儲在shared pool中的執(zhí)行計劃。

DISPLAY_AWR：sql的執(zhí)行計劃從shared pool中aga out后，如果執(zhí)行計劃被采集到awr報告中，那么就可以使用該方法查看執(zhí)行計劃。

示例：

  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開關(guān)來說看不到相關(guān)的統(tǒng)計信息，而且要等到語句執(zhí)行完成。但獲得的執(zhí)行計劃是真實的

       2.這里format參數(shù)為advanced,相比較于參數(shù)all,多了Outline Data這部分的信息輸出

       3.這里的Rows列值為估計值，要想看到真實值可以將format參數(shù)設(shè)置為'ALLSTATS LAST'

format參數(shù)設(shè)置為'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參數(shù)相比，少了部分輸出，但是能夠看到每一步獲取的實際記錄數(shù)。

 輸入sqlid來查看執(zhí)行計劃示例：

  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、相同的執(zhí)行計劃也可以通過@?/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.

==>可以根據(jù)diagnostic_dest 參數(shù)找到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中查看執(zhí)行計劃的常用方法”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！

新聞名稱：oracle中查看執(zhí)行計劃的常用方法
分享地址：http://muchs.cn/article12/jooedc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、軟件開發(fā)、虛擬主機、網(wǎng)站收錄、網(wǎng)站制作、品牌網(wǎng)站設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)