dbms_xplan.display_cursor

1) sql tunning asking01

  Note: example for reading execution plan,
        dbms_xplan.display_cursor('sql_id',child_number,'mode')

1@@@@ sql script
[oracle@station78 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 3 13:48:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ocm11g> !cat tmp.sql
--Test on 10R2, 11gR2

--
--1) Create the test table t.
set echo on
set feedback on
DROP TABLE t;
CREATE TABLE t
AS
SELECT rownum AS n, lpad('*',1000,'*') AS pad
  FROM dual
CONNECT BY level <= 1000;

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t');

CREATE INDEX i ON t(n);

ALTER SESSION SET workarea_size_policy = manual;
ALTER SESSION SET sort_area_size = 65536;




PAUSE
--
--2) Change session environment, generate child cursor
ALTER SYSTEM FLUSH shared_pool;
ALTER SESSION SET optimizer_mode = all_rows;
set termout off
SELECT * FROM t WHERE n > 19;
set termout on

ALTER SESSION SET optimizer_mode = first_rows_10;
set termout off
SELECT * FROM t WHERE n > 19;
set termout on



PAUSE
--
--3) Display info about last execution
--default child_number is 0
col sql_text for a40 word_wrapped
SELECT sql_id, child_number, plan_hash_value, sql_text
  FROM v$sql
 WHERE sql_text LIKE '%n > 19%';
SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',0));
SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',1));



PAUSE
--
--4) Display execution statistics (I/O)
--reflash buffer cache, see the physical read
ALTER SYSTEM FLUSH buffer_cache;
SELECT /*+ gather_plan_statistics */ count(*)
  FROM t
 WHERE mod(n,19) = 0;
col sql_text for a40 word_wrapped
SELECT sql_id, child_number, plan_hash_value, sql_text
  FROM v$sql
 WHERE sql_text LIKE '%mod(n,19)%';
SELECT * FROM table(dbms_xplan.display_cursor('&&04_sql_id',0,'runstats_last'));



PAUSE
--
--5) Use the parameter FORMAT to get different execution statistics
--UNDEFINE sql_id
--UNDEFINE child_number
DROP INDEX i;

SELECT /*+ gather_plan_statistics */ count(pad)
  FROM (SELECT rownum AS rn, pad FROM t ORDER BY n)
 WHERE rn = 1;

SELECT sql_id, child_number, plan_hash_value, sql_text
  FROM v$sql
 WHERE sql_text LIKE '%rn = 1%';

SELECT * FROM
  table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats last'));

PAUSE
SELECT * FROM
  table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_last'));

PAUSE
SELECT * FROM
  table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats last'));

PAUSE
SELECT * FROM
  table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats last'));

-- PAUSE
-- SELECT * FROM
--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats'));
--
-- PAUSE
-- SELECT * FROM
--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_tot'));
--
-- PAUSE
-- SELECT * FROM
--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats'));
--
-- PAUSE
-- SELECT * FROM
--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats'));






2@@@@ Explain Option:
Explain:
  Table 6-1. Columns of the Table Containing the Execution Plan
  Column        Description
  ------------------------------------------------------------------------------
  [1]Basics (Always Available)
  Id            The identifier of each operation (line) in the execution plan. If the number
                is prefixed by an asterisk, it means that predicate information for that line
                is available later.
  Operation     The operation to be executed. This is also known as the row source operation.
  Name          The object on which the operation is executed.

  ------------------------------------------------------------------------------
  [2]Query Optimizer Estimations
  Rows (E-Rows)   The estimated number of rows returned by the operation.
  Bytes (E-Bytes) The estimated amount of data returned by the operation.
  TempSpc         The estimated amount of temporary space used by the operation.
  Cost (%CPU)     The estimated cost of the operation. The percentage of CPU cost is given
                  in parentheses. Note that this value is cumulated through the execution
                  plan. In other words, the cost of parent operations contains the cost of
                  their child operations.
  Time            The estimated amount of time needed to execute the operation (HH:MM:SS).
 
  ------------------------------------------------------------------------------
  [3]Partitioning
  Pstart          The number of the first partition to be accessed. Set to either KEY, KEY(I),
                  KEY(MC), KEY(OR), or KEY(SQ) if unknown at parse time.
  Pstop           The number of the last partition to be accessed. Set to either KEY, KEY(I),
                  KEY(MC), KEY(OR), or KEY(SQ) if unknown at parse time.
 
  ------------------------------------------------------------------------------
  [4]Parallel and Distributed Processing
  Inst            For distributed processing, the name of the database link used by
                  the operation.
  TQ              For parallel processing, the table queue used for the communication
                  between slaves.
  IN-OUT          The relationship between parallel or distributed operations.
  PQ Distrib      For parallel processing, the distribution used by producers to send data
                  to consumers.
 
  ------------------------------------------------------------------------------
  [5]Runtime Statistics*
  Starts         The number of times a specific operation was executed.
  A-Rows         The actual number of rows returned by the operation.
  A-Time         The actual amount of time spent executing the operation (HH:MM:SS.FF).

  ------------------------------------------------------------------------------
  [6]I/O Statistics*
  Buffers        The number of logical read operations performed during the execution.
  Reads          The number of physical reads performed during the execution.
  Writes         The number of physical writes performed during the execution.

  ------------------------------------------------------------------------------
  [7]Memory Utilization Statistics
  OMem           The estimated amount of memory needed for an optimal execution.
  1Mem           The estimated amount of memory needed for one-pass execution.
  O/1/M          The number of times the execution was performed in optimal/one-pass/
                 multipass mode.
  Used-Mem       The amount of memory used by the operation during the last execution.
  Used-Tmp       The amount of temporary space used by the operation during the last
                 execution. This value must be multiplied by 1,024 to be consistent with
                 the other memory utilization columns (for example, 32K means 32MB).
  Max-Tmp        The maximum amount of temporary space used by the operation. This
                 value has to be multiplied by 1,024 to be consistent with the other memory
                 utilization columns (for example, 32K means 32MB).

  ------------------------------------------------------------------------------
  * Available only when execution statistics are enabled.







3@@@@ output
[oracle@station78 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 3 13:49:42 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ocm11g> @tmp.sql
SYS@ocm11g> set feedback on
SYS@ocm11g> DROP TABLE t;

Table dropped.

SYS@ocm11g> CREATE TABLE t
  2  AS
  3  SELECT rownum AS n, lpad('*',1000,'*') AS pad
  4    FROM dual
  5  CONNECT BY level <= 1000;

Table created.

SYS@ocm11g>
SYS@ocm11g> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t');

PL/SQL procedure successfully completed.

SYS@ocm11g>
SYS@ocm11g> CREATE INDEX i ON t(n);

Index created.

SYS@ocm11g>
SYS@ocm11g> ALTER SESSION SET workarea_size_policy = manual;

Session altered.

SYS@ocm11g> ALTER SESSION SET sort_area_size = 65536;

Session altered.

SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> --
SYS@ocm11g> --2) Change session environment, generate child cursor
SYS@ocm11g> ALTER SYSTEM FLUSH shared_pool;

System altered.

SYS@ocm11g> ALTER SESSION SET optimizer_mode = all_rows;

Session altered.

SYS@ocm11g> set termout off
SYS@ocm11g>
SYS@ocm11g> ALTER SESSION SET optimizer_mode = first_rows_10;

Session altered.

SYS@ocm11g> set termout off
SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> --
SYS@ocm11g> --3) Display info about last execution
SYS@ocm11g> --default child_number is 0
SYS@ocm11g> col sql_text for a40 word_wrapped
SYS@ocm11g> SELECT sql_id, child_number, plan_hash_value, sql_text
  2    FROM v$sql
  3   WHERE sql_text LIKE '%n > 19%';

SQL_ID          CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ------------ --------------- ----------------------------------------
16vk8p8cmwbk8         0    1601196873 SELECT * FROM t WHERE n > 19
16vk8p8cmwbk8         1     242607798 SELECT * FROM t WHERE n > 19
3s7f8gwf2bxw7         0     903671040 SELECT sql_id, child_number,
                       plan_hash_value, sql_text   FROM v$sql
                       WHERE sql_text LIKE '%n > 19%'


3 rows selected.

SYS@ocm11g> SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',0));
Enter value for 03_sql_id: 16vk8p8cmwbk8
old   1: SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',0))
new   1: SELECT * FROM table(dbms_xplan.display_cursor('16vk8p8cmwbk8',0))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    16vk8p8cmwbk8, child number 0
-------------------------------------
SELECT * FROM t WHERE n > 19

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     |     |    40 (100)|      |
|*  1 |  TABLE ACCESS FULL| T     |   982 |   963K|    40   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N">19)


18 rows selected.

SYS@ocm11g> SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',1));
old   1: SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',1))
new   1: SELECT * FROM table(dbms_xplan.display_cursor('16vk8p8cmwbk8',1))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    16vk8p8cmwbk8, child number 1
-------------------------------------
SELECT * FROM t WHERE n > 19

Plan hash value: 242607798

------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time       |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |       |       |     4 (100)|       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |    11 | 11055 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | I    |       |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N">19)


19 rows selected.

SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> --
SYS@ocm11g> --4) Display execution statistics (I/O)
SYS@ocm11g> --reflash buffer cache, see the physical read
SYS@ocm11g> ALTER SYSTEM FLUSH buffer_cache;

System altered.

SYS@ocm11g> SELECT /*+ gather_plan_statistics */ count(*)
  2    FROM t
  3   WHERE mod(n,19) = 0;

  COUNT(*)
----------
    52

1 row selected.

SYS@ocm11g> col sql_text for a40 word_wrapped
SYS@ocm11g> SELECT sql_id, child_number, plan_hash_value, sql_text
  2    FROM v$sql
  3   WHERE sql_text LIKE '%mod(n,19)%';

SQL_ID          CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ------------ --------------- ----------------------------------------
50zr0t09k80w3         0    2966233522 SELECT /*+ gather_plan_statistics */
                       count(*)   FROM t  WHERE mod(n,19) = 0

91307uh63jr72         0     903671040 SELECT sql_id, child_number,
                       plan_hash_value, sql_text   FROM v$sql
                       WHERE sql_text LIKE '%mod(n,19)%'


2 rows selected.

SYS@ocm11g> SELECT * FROM table(dbms_xplan.display_cursor('&&04_sql_id',0,'runstats_last'));
Enter value for 04_sql_id: 50zr0t09k80w3
old   1: SELECT * FROM table(dbms_xplan.display_cursor('&&04_sql_id',0,'runstats_last'))
new   1: SELECT * FROM table(dbms_xplan.display_cursor('50zr0t09k80w3',0,'runstats_last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    50zr0t09k80w3, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(*)    FROM t    WHERE mod(n,19)
= 0

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation       | Name | Starts | E-Rows | A-Rows |     A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |        |       1 |00:00:00.01 |    147 |     144 |
|   1 |  SORT AGGREGATE    |      |     1 |      1 |       1 |00:00:00.01 |    147 |     144 |
|*  2 |   TABLE ACCESS FULL| T      |     1 |     10 |      52 |00:00:00.01 |    147 |     144 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(MOD("N",19)=0)


20 rows selected.

SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> --
SYS@ocm11g> --5) Use the parameter FORMAT to get different execution statistics
SYS@ocm11g> --UNDEFINE sql_id
SYS@ocm11g> --UNDEFINE child_number
SYS@ocm11g> DROP INDEX i;

Index dropped.

SYS@ocm11g>
SYS@ocm11g> SELECT /*+ gather_plan_statistics */ count(pad)
  2    FROM (SELECT rownum AS rn, pad FROM t ORDER BY n)
  3   WHERE rn = 1;

COUNT(PAD)
----------
     1

1 row selected.

SYS@ocm11g>
SYS@ocm11g> SELECT sql_id, child_number, plan_hash_value, sql_text
  2    FROM v$sql
  3   WHERE sql_text LIKE '%rn = 1%';

SQL_ID          CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ------------ --------------- ----------------------------------------
2nqg014kk20xm         0    2545006537 SELECT /*+ gather_plan_statistics */
                       count(pad)    FROM (SELECT rownum AS rn,
                       pad FROM t ORDER BY n)  WHERE rn = 1

2dyc3byvhk8d3         0     903671040 SELECT sql_id, child_number,
                       plan_hash_value, sql_text   FROM v$sql
                       WHERE sql_text LIKE '%rn = 1%'


2 rows selected.

SYS@ocm11g>
SYS@ocm11g> SELECT * FROM
  2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats last'));
Enter value for 05_sql_id: 2nqg014kk20xm
Enter value for 05_child_number: 0
old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats last'))
new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'iostats last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    2nqg014kk20xm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum
AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads    | Writes |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |       149 |    125 |    125 |
|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |       149 |    125 |    125 |
|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |       149 |    125 |    125 |
|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |       149 |    125 |    125 |
|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |       147 |      0 |      0 |
|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |       147 |      0 |      0 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN"=1)


23 rows selected.

SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> SELECT * FROM
  2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_last'));
old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_last'))
new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'runstats_last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    2nqg014kk20xm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum
AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads    | Writes |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |       149 |    125 |    125 |
|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |       149 |    125 |    125 |
|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |       149 |    125 |    125 |
|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |       149 |    125 |    125 |
|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |       147 |      0 |      0 |
|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |       147 |      0 |      0 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN"=1)


23 rows selected.

SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> SELECT * FROM
  2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats last'));
old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats last'))
new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'memstats last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    2nqg014kk20xm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum
AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |    OMem |    1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |         |         |        |      |
|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |         |         |        |      |
|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |         |         |        |      |
|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |    1125K|     558K|49152  (0)|    1024 |
|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |         |         |        |      |
|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |         |         |        |      |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN"=1)


23 rows selected.

SYS@ocm11g>
SYS@ocm11g> PAUSE

SYS@ocm11g> SELECT * FROM
  2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats last'));
old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats last'))
new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'allstats last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID    2nqg014kk20xm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum
AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads    | Writes |  OMem |  1Mem
 | Used-Mem | Used-Tmp|

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

|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |       149 |    125 |    125 |     |
 |        |          |

|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |       149 |    125 |    125 |     |
 |        |          |

|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |       149 |    125 |    125 |     |
 |        |          |

|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |       149 |    125 |    125 |  1125K|   558
K|49152  (0)|     1024 |

|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |       147 |      0 |      0 |     |
 |        |          |

|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |       147 |      0 |      0 |     |
 |        |          |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN"=1)


23 rows selected.

SYS@ocm11g>
SYS@ocm11g> -- PAUSE
SYS@ocm11g> -- SELECT * FROM
SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats'));
SYS@ocm11g> --
SYS@ocm11g> -- PAUSE
SYS@ocm11g> -- SELECT * FROM
SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_tot'));
SYS@ocm11g> --
SYS@ocm11g> -- PAUSE
SYS@ocm11g> -- SELECT * FROM
SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats'));
SYS@ocm11g> --
SYS@ocm11g> -- PAUSE
SYS@ocm11g> -- SELECT * FROM
SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats'));
SYS@ocm11g>