Oracle中如何查看執(zhí)行計(jì)劃

這篇文章主要介紹了Oracle中如何查看執(zhí)行計(jì)劃，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

方法一、通過(guò)使用工具PLSQL Developer中的Explain Plan Window窗口查看SQL執(zhí)行計(jì)劃。具體參考

             https://www.cnblogs.com/Dreamer-1/p/6076440.html

方法二、通過(guò)SQL*PLUS中的autotrace命令查看

            1.登錄擁有dba權(quán)限的用戶，分別執(zhí)行

               腳本 ${ORACLE_HOME}/RDBMS/ADMIN/utlxplan.sql和

               腳本 ${ORACLE_HOME}/sqlplus/admin/plustrce.sql

               然后通過(guò)SQL*PLUS就可以查看執(zhí)行計(jì)劃了

            2.查看執(zhí)行計(jì)劃有下面四種選項(xiàng)

                1> set autotrace on    --（得到執(zhí)行計(jì)劃，并輸出結(jié)果）

                2> set autotrace traceonly    --（得到執(zhí)行計(jì)劃，但不輸出結(jié)果）

                3> set autotrace traceonly explain    --（得到執(zhí)行計(jì)劃，不輸出統(tǒng)計(jì)信息和結(jié)果，僅展現(xiàn)執(zhí)行計(jì)劃部分）

                4> set autotrace traceonly statistics    --（不輸出執(zhí)行計(jì)劃和結(jié)果，僅展現(xiàn)統(tǒng)計(jì)信息）

            3.優(yōu)缺點(diǎn)

                優(yōu)點(diǎn)：

                    1> 可以輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生多少邏輯讀，多少遞歸調(diào)用，多少次物理讀的情況）

                    2> 雖然必須要等語(yǔ)句執(zhí)行完畢后才可以執(zhí)行計(jì)劃，但是可以有traceonly開(kāi)關(guān)來(lái)控制返回結(jié)果不打屏輸出

                缺點(diǎn)：

                    1> 必須等語(yǔ)句執(zhí)行完畢后，才可以出結(jié)果

                    2> 無(wú)法看到表被訪問(wèn)了多少次

方法三、explain plan for獲取

            1.執(zhí)行步驟如下：

                SQL> set linesize 200;

                SQL> set pagesize 2000;

                SQL> explain plan for select * from emp;

                Explained

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

                PLAN_TABLE_OUTPUT

                --------------------------------------------------------------------------------

                Plan hash value: 3956160932

                --------------------------------------------------------------------------

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

                --------------------------------------------------------------------------

                |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |

                |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |

                --------------------------------------------------------------------------

                8 rows selected

            2.優(yōu)缺點(diǎn)

                優(yōu)點(diǎn)：

                    1> 無(wú)需真正執(zhí)行，快捷方便

                缺點(diǎn)：

                    1> 沒(méi)有輸出相關(guān)的統(tǒng)計(jì)信息（產(chǎn)生多少次邏輯讀，多少次物理讀，多少次遞歸調(diào)用）

                    2> 無(wú)法判斷處理了多少行

                    3> 無(wú)法判斷表被訪問(wèn)了多少次

方法四、statistics_level = all 或者 /*+ gather_plan_statistics */

            1.執(zhí)行步驟

                1> 通過(guò)statistics_level = all

                    SQL> set linesize 500;

                    SQL> set pagesize 1000;

                    SQL> alter session set statistics_level = all;

                    會(huì)話已更改。

                    SQL> select count(*) from emp;

                      COUNT(*)

                    ----------

                            14

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

                    PLAN_TABLE_OUTPUT

                    ------------------------------------------------------------------------------------------------

                    SQL_ID  g59vz2u4cu404, child number 0

                    -------------------------------------

                    select count(*) from emp

                    Plan hash value: 2937609675

                    -------------------------------------------------------------------------------------

                    | Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

                    -------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |       1 |

                    |   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |       1 |

                    |   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |       1 |

                    -------------------------------------------------------------------------------------

                    已選擇14行。

                2> 通過(guò) /*+ gather_plan_statistics*/

                    SQL> set linesize 200;

                    SQL> set pagesize 500;

                    SQL> select /*+gather_plan_statistics*/ count(*) from emp;

                      COUNT(*)

                    ----------

                            14

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

                    PLAN_TABLE_OUTPUT

                    -------------------------------------

                    SQL_ID  537ffv2mq5375, child number 0

                    -------------------------------------

                    select /*+gather_plan_statistics*/ count(*) from emp

                    Plan hash value: 2937609675

                    -------------------------------------------------------------------------------------

                    | Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

                    -------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |       1 |

                    |   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |       1 |

                    |   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |       1 |

                    -------------------------------------------------------------------------------------

                    已選擇14行。

            2.關(guān)鍵字解讀

                1> Starts：該SQL執(zhí)行的次數(shù)

                2> E-Rows：執(zhí)行計(jì)劃預(yù)計(jì)的行數(shù)。

                3> A-Rows：實(shí)際返回的行數(shù)。A-Rows跟E-Rows做比較，就可以確定哪一步執(zhí)行計(jì)劃出了問(wèn)題。

                4> A-Time：每一步實(shí)際執(zhí)執(zhí)行的時(shí)間（HH : MM :SS.FF），根據(jù)這一行可以知道該SQL耗時(shí)在了哪個(gè)地方

                5> Buffers：每一步執(zhí)行的邏輯讀或一致性讀

            3.優(yōu)缺點(diǎn)

                優(yōu)點(diǎn)

                    1> 可以清晰的從Starts得出表被訪問(wèn)多少

                    2> 可以清晰的從E-Rows和A-Rows中得到預(yù)測(cè)的行數(shù)和真實(shí)的行數(shù)，從而可以準(zhǔn)確判斷Oracle評(píng)估是否正確

                    3> 雖然沒(méi)有專門(mén)的輸出運(yùn)行時(shí)的相關(guān)統(tǒng)計(jì)信息，但是執(zhí)行計(jì)劃中的buffers就是真實(shí)的邏輯讀的多少

                缺點(diǎn)

                    1> 必須等語(yǔ)句真正執(zhí)行完畢后，才可以得出結(jié)果

                    2> 無(wú)法控制記錄輸屏打出，不像autotace由traceonly可以不將結(jié)果打屏輸出

                    3> 看不出遞歸調(diào)用次數(shù)，看不出物理讀的多少（不過(guò)邏輯讀才是重點(diǎn)）

方法五、通過(guò)dbms_xplan.display_cursor(&sql_id)輸入sql_id參數(shù)獲取

            1.執(zhí)行步驟

                SQL> select sql_id from sys.v_$sql t where t.sql_text like '%select count(*) from emp';

                SQL_ID

                -------------

                g59vz2u4cu404

                SQL> select * from table(dbms_xplan.display_cursor('g59vz2u4cu404'));

                PLAN_TABLE_OUTPUT  

                --------------------------------------------------------------------------------------------

                SQL_ID  g59vz2u4cu404, child number 0

                -------------------------------------

                select count(*) from emp

                Plan hash value: 2937609675

                -------------------------------------------------------------------

                | Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

                -------------------------------------------------------------------

                |   0 | SELECT STATEMENT |        |       |     1 (100)|          |

                |   1 |  SORT AGGREGATE  |        |     1 |            |          |

                |   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |

                -------------------------------------------------------------------

                已選擇14行。

            2.優(yōu)缺點(diǎn)

                優(yōu)點(diǎn)：

                    1> 知道sql_id立即可得到執(zhí)行計(jì)劃，和explain plan for一樣無(wú)需執(zhí)行

                    2> 可以得到真實(shí)的執(zhí)行計(jì)劃

                缺點(diǎn)：

                    1> 沒(méi)有輸出運(yùn)行的相關(guān)統(tǒng)計(jì)信息（產(chǎn)生的物理讀，邏輯讀，遞歸調(diào)用次數(shù)）

                    2> 無(wú)法判斷處理了多少行

                    3> 無(wú)法判斷表被訪問(wèn)了多少次

方法六、10046 trace跟蹤

            1.執(zhí)行步驟 

                Step1：alter session setevents '10046 trace name context forever,level 12'; （開(kāi)啟跟蹤）

                Step2：執(zhí)行sql

                Step3：alter session setevents '10046 trace name context off';   （關(guān)閉跟蹤）

                Step4：步驟4：找到跟蹤后產(chǎn)生的文件

                Step5：tkprof  trc文件  目標(biāo)文件 sys=no sort=prsela,exeela,fchela  （格式化命令）

                SQL> set autot off

                SQL> alter session set statistics_level=typical;

                Session altered.

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

                Session altered.

                SQL> select count(*) from test;

                  COUNT(*) 

                ----------

                         7

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

                Session altered.

                SQL> select d.value

                  2  || '/'

                  3  || LOWER (RTRIM(i.INSTANCE, CHR(0)))

                  4  || '_ora_'

                  5  || p.spid

                  6  || '.trc' trace_file_name

                  7  from (select p.spid

                  8        from v$mystat m,v$session s, v$process p

                  9        where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

                 10        (select t.INSTANCE

                 11         FROM v$thread t,v$parameter v

                 12         WHERE v.name='thread'

                 13         AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

                 14         (select value

                 15         from v$parameter

                 16         where name='user_dump_dest') d;

                TRACE_FILE_NAME

                -------------------------------------------------------------------

                /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc

                SQL> host

                [oracle@ora12c ~]$ tkprof /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc /home/oracle/10046.txt sys=no sort=prsela,exeela,fchela

                TKPROF: Release 12.1.0.1.0 - Development on Fri Jan 20 08:22:25 2017

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

                SQL ID: 7b2twsn8vgfsc Plan Hash: 784602781

                select count(*)   from test

                call     count       cpu    elapsed       disk      query    current        rows

                ------- ------  -------- ---------- ---------- ---------- ----------  ----------

                Parse        1      0.00       0.00          3          3          2           0

                Execute      1      0.00       0.00          0          0          0           0

                Fetch        2      0.00       0.00          4         66          0           1

                ------- ------  -------- ---------- ---------- ---------- ----------  ----------

                total        4      0.00       0.00          7         69          2           1

                Misses in library cache during parse: 1

                Optimizer mode: ALL_ROWS

                Parsing user id: 103  

                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max)  Row Source Operation

                ---------- ---------- ----------  ---------------------------------------------------

                         1          1          1  SORT AGGREGATE (cr=66 pr=4 pw=0 time=298 us)

                         7          7          7   PARTITION RANGE ALL PARTITION: 1 3 (cr=66 pr=4 pw=0 time=397 us cost=39 size=0 card=11)

                         7          7          7    TABLE ACCESS FULL TEST PARTITION: 1 3 (cr=66 pr=4 pw=0 time=290 us cost=39 size=0 card=11)

                Elapsed times include waiting on following events:

                  Event waited on                             Times   Max. Wait  Total Waited

                  ----------------------------------------   Waited  ----------  ------------

                  db file sequential read                         4        0.00          0.00

                  SQL*Net message to client                       2        0.00          0.00

                  db file scattered read                          1        0.00          0.00

                  SQL*Net message from client                     2        7.03          7.03

            2.優(yōu)缺點(diǎn)

                優(yōu)點(diǎn)：

                    1> 可以看出SQL語(yǔ)句對(duì)應(yīng)的等待事件

                    2> 如果SQL語(yǔ)句中有函數(shù)，SQL中有SQL，將會(huì)都被列出，無(wú)處遁形

                    3> 可以方便的看出處理的行數(shù)，邏輯物理讀

                    4> 可以跟蹤整個(gè)程序包

                缺點(diǎn)：    

                    1> 步驟繁瑣，比較麻煩

                    2> 無(wú)法判斷表被訪問(wèn)了多少次

                    3> 執(zhí)行計(jì)劃中的謂詞部分不能清晰的展現(xiàn)出來(lái)

方法七、awrsqlrpt.sql

            1.執(zhí)行步驟  

               Step1：@?/rdbms/admin/awrsqrpt.sql

                Step2：選擇你要的斷點(diǎn)（begin snap 和end snap)

                Step3：輸入sql_id

適用場(chǎng)合分析

    1.如果某SQL執(zhí)行非常長(zhǎng)時(shí)間才會(huì)出結(jié)果，甚至慢到返回不了結(jié)果，這時(shí)候看執(zhí)行計(jì)劃就只能用方法explain plan for；

    2.跟蹤某條SQL最簡(jiǎn)單的方法是方法explain plan for ，其次就是方法autotrace；

    3.如果想觀察到某條SQL有多條執(zhí)行計(jì)劃的情況，只能用方法dbms_xplan.display_cursor(sql_id)和方法awrsqlrpt.sql；

    4.如果SQL中含有多函數(shù)，函數(shù)中套有SQL等多層遞歸調(diào)用，想準(zhǔn)確分析，只能使用方法10046 trace；

    5.要想確?？吹秸鎸?shí)的執(zhí)行計(jì)劃，不能用方法plsql developer和方法explain plan for；

    6.要想獲取表被訪問(wèn)的次數(shù)，只能使用方法statistics_level（/*+ gather_plan_statistics */）；

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Oracle中如何查看執(zhí)行計(jì)劃”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!