Using Statspack to Record Explain Plan Details

?/rdbms/admin/sprepsql.sql
An SQL report can be executed on one SQL statement, searching for a bottleneck, where the hash value for the SQL code statement is found in the STATSPACK instance report.

[@more@]

From Oracle9i onwards, oracle stores the execution plan of each of the child
cursor loaded in the library cache. The view v$sql_plan can be queried to
retrieve this information. In addition an enhancement was made to the Oracle9i
statspack report to capture the sql plans.

Capturing a snapshot at i_snap_level>=6 will capture the execution plans of
any SQL statements present in the library cache at the time the snapshot is
taken, that have exceeded one of the SQL thresholds.

Example
~~~~~~~
To capture a snapshot at level 6, execute the following:

SQL> execute statspack.snap (i_snap_level=>6, i_modify_parameter=>'true');



In addition to the snapshot level, there are parameters which can be configured:

These parameters are used as thresholds when collecting data on SQL
statements; data will be captured on any SQL statements that breach
the specified thresholds.

For example :
~~~~~~~~~~~~~
i_executions_th : number of times statement was executed (default 100)
i_disk_reads_th : number of disk reads the statement made (default 1000)


Threshold values can be reduced so that this snapshot captures most
of the SQL statements present in the library cache at that point of time.

To verify whether there is any change in the plan at a future point of time,
take another snapshot at level 6. Using the script:

$ORACLE_HOME/rdbms/admin/sprepsql.sql

Explain plans between the two snapshots can be compared.

SQL Text
~~~~~~~~
INSERT INTO ORGANIZE_OPERATION_LOG(LOG_ID,MASTER_ID,GUEST_ID,LOG
_TIME,OPERATION_TYPE_ID) SELECT SEQ_LOG_ID.NEXTVAL, 'EAF', A.STA
FF_ID, SYSDATE, 504 FROM (SELECT STAFF.STAFF_ID, STAFF.STATUS, S
TAFF.STAFF_TYPE, MAX(PASSWORD_HISTORY.MODIFY_TIME) PWD_MODIFY_TI
ME FROM STAFF, PASSWORD_HISTORY WHERE STAFF.STAFF_ID=PASSWORD_HI
STORY.STAFF_ID GROUP BY STAFF.STAFF_ID, STAFF.STATUS, STAFF.STAF
F_TYPE) A WHERE A.STATUS IN (-1, 0) AND A.STAFF_TYPE=0 AND (SYSD
ATE-A.PWD_MODIFY_TIME)>=:B2 AND (SYSDATE-A.PWD_MODIFY_TIME)<:b1>

Known Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id

First First Plan
Snap Id Snap Time Hash Value Cost
-------- --------------- ------------ ----------
191 17 Jul 07 10:13 1211322769 0
191 17 Jul 07 10:13 2094815075 18
191 17 Jul 07 10:13 3647221266 11

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|INSERT STATEMENT |----- 1211322769 ----| | | |
|SEQUENCE | | | | |
| VIEW | | | | |
| FILTER | | | | |
| SORT GROUP BY | | | | |
| NESTED LOOPS | | | | |
| TABLE ACCESS FULL |PASSWORD_HISTORY | | | |
| TABLE ACCESS BY INDEX ROWID|STAFF | | | |
| INDEX UNIQUE SCAN |PK_STAFF | | | |
|INSERT STATEMENT |----- 2094815075 ----| | | 18 |
|SEQUENCE | | | | |
| VIEW | | 1 | 44 | 18 |
| FILTER | | | | |
| SORT GROUP BY | | 1 | 53 | 18 |
| FILTER | | | | |
| NESTED LOOPS | | 1 | 53 | 16 |
| TABLE ACCESS FULL |STAFF | 1 | 35 | 14 |
| INDEX RANGE SCAN |PK_PASSWORD_HISTORY | 1 | 18 | 2 |
|INSERT STATEMENT |----- 3647221266 ----| | | 11 |
|SEQUENCE | | | | |
| VIEW | | 1 | 44 | 11 |
| FILTER | | | | |
| SORT GROUP BY | | 1 | 31 | 11 |
| FILTER | | | | |
| HASH JOIN | | 491 | 14K| 8 |
| TABLE ACCESS FULL |STAFF | 657 | 8K| 4 |
| TABLE ACCESS FULL |PASSWORD_HISTORY | 981 | 17K| 3 |
--------------------------------------------------------------------------------

End of Report

This example containts 3 plans.

Usage Suggestion
================

Recording a snapshot of SQL execution can be useful in managing any system
change that may modify query plans.

Before any operation which can cause a plan change, (such as after large data
loads, or after gathering new optimizer statistics) capture the snapshot at
level 6 and use this as the base line for future comparisons.