Database Reference
In-Depth Information
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 |
------------------------------------------------------------------------------------
The
display_awr
function isn't limited to being used with one parameter identifying a SQL statement. For this
reason, later in this chapter I cover the
dbms_xplan
package, exploring all possibilities, including a description of the
generated output.
Statspack stores execution plans in the
stats$sql_plan
repository table when a level equal to or greater than 6 is
used for taking the snapshots. Even though no specific function is provided by the
dbms_xplan
package to query that
repository table, it's possible to take advantage of the
display
function to show the execution plans it contains.
An example can be found in the
display_statspack.sql
script.
In addition, for both AWR and Statspack, Oracle Database provides useful scripts for highlighting execution plan
changes and resource consumption variation during a period of time for a specific SQL statement. Their names are
awrsqrpt.sql
and
sprepsql.sql
, respectively. You find them under the directory
$ORACLE_HOME/rdbms/admin
. The
following is an excerpt of the output generated by the
awrsqrpt.sql
script. According to the output, the execution
plan of the SQL statement changed during the analyzed period. The average elapsed time went from about 8.3
seconds (16,577/2/1,000) for the first one to about 3.7 seconds (14,736/4/1,000) for the second one:
SQL ID: 1hqjydsjbvmwq DB/Inst: DBM11203/DBM11203 Snaps: 576-577
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID = ...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 2446651477 16,577 2 577 577
2 265338492 14,736 4 577 577
--- ----------------------------------------------------------------------------
Plan 1(PHV: 2446651477)
-----------------------
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 16,577 8,288.6 50.2
CPU Time (ms) 16,071 8,035.3 50.9
Executions 2 N/A N/A
Buffer Gets 163,606 81,803.0 90.1
Disk Reads 161,900 80,950.0 96.0
Parse Calls 2 1.0 1.0
