Database Reference
In-Depth Information
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 1hqjydsjbvmwq, child number 0
-------------------------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID =
P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'
Plan hash value: 265338492
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | HASH JOIN | | 913K| 26M| 139 (33)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 4 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 8075K| 123 (27)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 123 (27)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="P"."PROMO_ID")
3 - filter("PROMO_SUBCATEGORY"='online discount')
The
display_cursor
function isn't limited to being used with two parameters identifying a child cursor. For this
reason, later in this chapter I cover the
dbms_xplan
package, exploring all possibilities, including a description of the
generated output.
Automatic Workload Repository and Statspack
When a snapshot is taken, Automatic Workload Repository (AWR) and Statspack are able to collect execution
plans. To get execution plans, queries against the dynamic performance views described in the previous section are
executed. Once available, the execution plans may be displayed in reports, by Oracle Enterprise Manager or other
tools. For both AWR and Statspack, the repository table storing the execution plans has a structure very similar to the
one of the
v$sql_plan
view. Because of this, the techniques described in the previous section apply to it as well.
The execution plans stored in AWR are available through the
dba_hist_sql_plan
view (from version 12.1
onward,
cdb_hist_sql_plan
is also available). To query them, the
dbms_xplan
package provides the
display_awr
function. As for the other functions of this package, its use is straightforward. The following query is an example (note
that the parameter passed to the
display_awr
function identifies the SQL statement through its
sql_id)
:
SQL> SELECT * FROM table(dbms_xplan.display_awr('1hqjydsjbvmwq'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 1hqjydsjbvmwq
--------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID =
P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'
