Database Reference
In-Depth Information
COUNT(PAD)
----------
1
SQL> SELECT * FROM table(dbms_xplan.display_cursor('d5v0dt28fp5fh', 0, '
iostats last
'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID d5v0dt28fp5fh, 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 |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 147 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 147 |
|* 2 | VIEW | | 1 | 1000 | 1 |00:00:00.02 | 147 |
| 3 | SORT ORDER BY | | 1 | 1000 | 1000 |00:00:00.02 | 147 |
| 4 | COUNT | | 1 | | 1000 |00:00:00.01 | 145 |
| 5 | TABLE ACCESS FULL| T | 1 | 1000 | 1000 |00:00:00.01 | 145 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN"=1)
The display_awr Function
The
display_awr
function returns execution plans stored in AWR. As for the
display
function, the return value is an
instance of the
dbms_xplan_type_table
collection. The function has the following input parameters:
sql_id
specifies the SQL statement whose execution plan is returned. The parameter has no
default value.
•
plan_hash_value
specifies the hash value of the execution plan to be returned. The default
value is
NULL
. If the default value is used, all execution plans related to the SQL statement
identified by the
sql_id
parameter are returned.
•
•
db_id
specifies which database the execution plan to be returned was executed on. The
default value is
NULL
. If the default value is used, the current database is used.
•
format
specifies which information is displayed. Even though the same values as the
format
parameter of the
display
function are supported, not all information can be displayed. For
example, information about predicates is missing because AWR doesn't store it. The default
value is
typical
.
