How to view the execution plan in Oracle

Time:2020-2-12

How to view the execution plan

  1. Explain Plan For SQL
    If the SQL statement is not executed, the generated plan may not be the actual execution plan
    Plan table is required

  2. SQLPLUS AUTOTRACE
    Except for set AutoTrace traceonly explain, SQL is actually executed, but it is not necessarily a real plan
    Plan table is required

  3. SQL TRACE
    Need to enable 10046 abstainer SQL < trace
    Generally, tkprof can be used to see more clearly. Of course, 10046 has its own implementation plan information

  4. V $SQL and V $SQL & plan
    You can query the plan information of multiple child cursors, but it seems to be quite laborious

  5. Enterprise Manager
    Execution plans can be graphically displayed, but not all environments have em available

  6. Other third party tools
    Note that the execution plan seen by tools like PL / SQL developer F5 may not be true

Recommended method DBMS? Xplan

select * from table(dbms_xplan….);

dbms_xplan.display()

Data source is plan table

dbms_xplan.display_cursor

The data source is the cursor cache in the 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

The recommended parameters are:

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

If sqlid is null, the execution plan of the current session is displayed.

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

The format is explained as follows:

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_bindings: displays the binding variables used for parsing.

dbms_xplan.display_awr

The data source is AWR warehouse base table 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

Data source is SQL set view


The above content is mainly organized from MacLean’s Oracle execution plan teaching video and ppt:
www.askmaclean.com/archives/read-sql-execution-plan.html