Database Reference
In-Depth Information
SQL> CREATE TABLE my_plan_table
2 AS
3 SELECT cast(1 AS VARCHAR2(30)) AS plan_id, p.*
4 FROM v$sql_plan_statistics_all p
5 WHERE (sql_id, child_number) = (SELECT prev_sql_id, prev_child_number
6 FROM v$session
7 WHERE sid = sys_context('userenv','sid'));
SQL> SELECT * FROM table(dbms_xplan.display('my_plan_table', NULL, 'iostats'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2966233522
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 2 |00:00:00.01 | 10 | 4 |
| 1 | SORT AGGREGATE | | 2 | 1 | 2 |00:00:00.01 | 10 | 4 |
| 2 | TABLE ACCESS FULL| T | 2 | 1000 | 2000 |00:00:00.01 | 10 | 4 |
----------------------------------------------------------------------------------------------
Table 10-4.
Modifiers Accepted by the
format
Parameter
Value
Description
allstats
This is a shortcut for
iostats memstats
.
iostats
Controls the display of runtime statistics (columns
Starts
,
A-Rows
,
A-Time
), the estimated
number of rows (column
E-Rows
), and disk I/O statistics (columns
Buffers
,
Reads
and
Writes
).
last
Per default, the
allstats
,
iostats
,
memstats
, and
rowstats
modifiers display the cumulated
statistics of all executions. If this value is added to them, only the statistics of the last execution
are displayed. Specifying this modifier for SQL statements executed with parallel processing
doesn't work as you might expect. More information about this in the “Parallel Processing”
section in Chapter 15.
memstats
Controls the display of memory utilization statistics (columns
OMem
,
1Mem
,
O/1/M
,
Used-Mem
,
Used-Tmp
and
Max-Tmp
).
rowstats
Controls the display of row count statistics (columns
Starts
,
E-Rows and A-Rows
).
This modifier is available as of version 11.2.0.4 only.
runstats_last
Same as
iostats last
. This modifier is deprecated and available for backward
compatibility only.
runstats_tot
Same as
iostats
. This modifier is deprecated and available for backward compatibility only.
