Statspack - Secrets of the Oracle Database - page 333

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 |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 3985860841 ----| | | 17 |
|HASH JOIN | | 105 | 17K| 17 |
| TABLE ACCESS FULL |JOBS | 19 | 513 | 3 |
| HASH JOIN | | 105 | 14K| 14 |
| TABLE ACCESS FULL |EMPLOYEES | 107 | 1K| 3 |
| HASH JOIN | | 106 | 13K| 10 |
| HASH JOIN | | 27 | 1K| 7 |
| TABLE ACCESS FULL |LOCATIONS | 23 | 1K| 3 |
| TABLE ACCESS FULL |DEPARTMENTS | 27 | 513 | 3 |
| TABLE ACCESS FULL |EMPLOYEES | 107 | 6K| 3 |
|SELECT STATEMENT |----- 4095786543 ----| | | 9 |
|NESTED LOOPS | | 105 | 17K| 9 |
| NESTED LOOPS | | 105 | 12K| 7 |
| NESTED LOOPS | | 105 | 9K| 6 |
| NESTED LOOPS | | 106 | 8K| 4 |
| TABLE ACCESS FULL |EMPLOYEES | 107 | 6K| 3 |
| TABLE ACCESS BY INDEX ROWID |DEPARTMENTS | 1 | 19 | 1 |
| INDEX UNIQUE SCAN |DEPT_ID_PK | 1 | | 0 |
| TABLE ACCESS BY INDEX ROWID |EMPLOYEES | 1 | 12 | 1 |
| INDEX UNIQUE SCAN |EMP_EMP_ID_PK | 1 | | 0 |
| TABLE ACCESS BY INDEX ROWID |JOBS | 1 | 27 | 1 |
| INDEX UNIQUE SCAN |JOB_ID_PK | 1 | | 0 |
| TABLE ACCESS BY INDEX ROWID |LOCATIONS | 1 | 48 | 1 |
| INDEX UNIQUE SCAN |LOC_ID_PK | 1 | | 0 |
--------------------------------------------------------------------------------
The script retrieves all the execution plans for the statement with the old hash value spec-
ified. Since execution plans may change when upgrading the DBMS software to Oracle10 g , I
recommend capturing Statspack snapshots at level 6 or higher prior to an upgrade.
Oracle Corporation provides the script spup10.sql for upgrading a Statspack repository to
Oracle10 g , but states that the upgrade is not guaranteed to work. My limited experience with
this script and Oracle10 g Release 2 is that repository upgrades do not succeed. To preserve
Statspack snapshots captured with Oracle9 i , export the schema PERFSTAT using the export
utility ( exp ), before upgrading to Oracle10 g . If necessary, an Oracle9 i export dump may be
imported into an Oracle9 i test database to run reports (see “Importing Statspack Data from
Another Database” later in this chapter).