oracle檢視執行計劃的方法

花菜土豆粉發表於2014-03-26
Oracle

檢視執行計劃的方法

  1. Explain Plan For SQL
    不實際執行SQL語句，生成的計劃未必是真實執行的計劃
    必須要有plan_table

  2. SQLPLUS AUTOTRACE
    除set autotrace traceonly explain外均實際執行SQL，但仍未必是真實計劃
    必須要有plan_table

  3. SQL TRACE
    需要啟用10046戒者SQL_TRACE
    一般用tkprof看的更清楚些，當然10046裡本身也有執行計劃資訊

  4. V$SQL和V$SQL_PLAN
    可以查詢到多個子遊標的計劃資訊了，但是看起來比較費勁

  5. Enterprise Manager
    可以圖形化顯示執行計劃，但並非所有環境有EM可用

  6. 其他第三方工具
    注意 PL/SQL developer之類工具F5看到的執行計劃未必是真實的

推薦的方法 DBMS_XPLAN

select * from table(dbms_xplan….);

dbms_xplan.display()

資料來源是Plan Table

dbms_xplan.display_cursor

資料來源是Shared pool中的遊標快取

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

推薦的使用引數為：

select * from table(dbms_xplan.display_cursor(`sqlId`,null,`ADVANCED  ALLSTATS LAST PEEKED_BINDS`));

如果sqlId為NULL，則顯示當前session的執行計劃。

select * from table(dbms_xplan.display_cursor(null,null,`ADVANCED  ALLSTATS LAST PEEKED_BINDS`));

其中format的解釋如下：

IOSTATS: Assuming that basic plan statistics are
  ---                 collected when SQL statements are executed (either by
  ---                 using the gather_plan_statistics hint or by setting the
  ---                 parameter statistics_level to ALL), this format will show
  ---                 IO statistics for all (or only for the last as shown below)
  ---                 executions of the cursor.
  ---
  ---        MEMSTATS: Assuming that PGA memory management is enabled (i.e
  ---                  pga_aggregate_target parameter is set to a non 0 value),
  ---                  this format allows to display memory management
  ---                  statistics (e.g. execution mode of the operator, how
  ---                  much memory was used, number of bytes spilled to
  ---                  disk, ...). These statistics only apply to memory
  ---                  intensive operations like hash-joins, sort or some bitmap
  ---                  operators.
  ---
  ---        ROWSTATS: Assuming that basic plan statistics are
  ---                  collected when SQL statements are executed (either by
  ---                  using the gather_plan_statistics hint or by setting the
  ---                  parameter statistics_level to ALL), this format will show
  ---                  row count statistics for all (or only for the last as
  ---                  shown below) executions of the cursor.
  ---
  ---        ALLSTATS: A shortcut for `IOSTATS MEMSTATS ROWSTATS`
  ---
  ---        LAST: By default, plan statistics are shown for all executions of
  ---              the cursor. The keyword LAST can be specified to see only
  ---              the statistics for the last execution.
  ---
  ---        PEEKED_BINDS：顯示解析時使用的繫結變數。

dbms_xplan.display_awr

資料來源是AWR倉庫基表WRH$_SQL_PLAN

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 CON_ID                         NUMBER(38)              IN     DEFAULT

dbms_xplan.display_sqlset

資料來源是SQL Set檢視

以上內容主要整理自maclean的oracle執行計劃教學視訊和ppt：
www.askmaclean.com/archives/read-sql-execution-plan.html

相關文章