Home > Uncategorized > Oracle: How to easily display the execution plan of a statement located in the shared pool (command line style)

Oracle: How to easily display the execution plan of a statement located in the shared pool (command line style)

Problem description:

You want to review the execution plan of a statement currently or previously executed by a session and Oracle Enterprise Manager is not use. Querying V$SQL_PLAN would give you the requested information, but not quiet comfortable.

Problem resolution:

As with “EXPLAIN PLAN” generated execution plans, you can use DBMS_XPLAN in order to display the execution plan for a statement in the shared pool. To be more specific, the function DISPLAY_CURSOR will provide you the information by passing the sql_id and cursor child number of the statement in concern:

   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

In addition DBMS_XPLAN.DISPLAY_CURSOR can provide you various plan statistics such as I/O, memory and timing by specifying the format:

  • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
  • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).
  • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
  • ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

In addition following keywords can be added to above standard formats in order to customize the output. These keywords must be separated by a comma or a space:

  • ROWS – if relevant, shows the number of rows estimated by the optimizer
  • BYTES – if relevant, shows the number of bytes estimated by the optimizer
  • COST – if relevant, shows optimizer cost information
  • PARTITION – if relevant, shows partition pruning information
  • PARALLEL – if relevant, shows PX information (distribution method and table queue information)
  • PREDICATE – if relevant, shows the predicate section
  • PROJECTION -if relevant, shows the projection section
  • ALIAS – if relevant, shows the “Query Block Name / Object Alias” section
  • REMOTE – if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
  • NOTE – if relevant, shows the note section of the explain plan
  • 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 (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators.
  • ALLSTATS – A shortcut for ‘IOSTATS MEMSTATS’
  • 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.


  • Display the execution for your the last executed statement of your session

    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
    SQL_ID	dt0hqkzcdq5ny, child NUMBER 1
    SELECT * FROM TABLE(dbms_xplan.display_cursor)
    Plan hash VALUE: 3713220770
    | Id  | Operation			  | Name	   | ROWS  | Bytes | Cost (%CPU)| TIME	   |
    |   0 | SELECT STATEMENT		  |		   |	   |	   |	24 (100)|	   |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |	 4 |	 8 |	24   (0)| 00:00:01 |
       - cardinality feedback used FOR this statement
    17 ROWS selected.


  • Display the execution plan for a specific statement executed by another session:

    SQL> SELECT sid, sql_id, sql_child_number FROM v$session WHERE sid = 1934;
    ---------- ------------- ----------------
          1934 2rk5134pp7s3g		1
    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('2rk5134pp7s3g', 1));
    SQL_ID	2rk5134pp7s3g, child NUMBER 1
    UPDATE myschema.bycicle SET comment = :comment
    WHERE id = :id
    Plan hash VALUE: 960803932
    | Id  | Operation	   | Name	    | ROWS  | Bytes | Cost (%CPU)| TIME     |
    |   0 | UPDATE STATEMENT   |		    |	    |	    |	  1 (100)|	    |
    |   1 |  UPDATE 	   | BYCICLE	    |	    |	    |		 |	    |
    |*  2 |   INDEX UNIQUE SCAN| BYCICLE#ID     |	  1 |	167 |	  1   (0)| 00:00:01 |
    Predicate Information (IDENTIFIED BY operation id):
       2 - access("ID"=:ID)
    20 ROWS selected.
  • Display execution plan for all children of a specific SQL_ID:

    SELECT * FROM TABLE(dbms_xplan.display_cursor('2rk5134pp7s3g'));


Categories: Uncategorized Tags:
  1. No comments yet.
  1. No trackbacks yet.

Connect with Facebook