Database Reference
In-Depth Information
SQL> SELECT p.snap_id, s.snap_time, p.sql_id, p.hash_value, p.old_hash_value,
p.plan_hash_value, p.cost
FROM stats$sql_plan_usage p, stats$snapshot s
WHERE p.snap_id=s.snap_id
AND p.hash_value=3786124882
ORDER BY p.snap_id;
SNAP_ID SNAP_TIME SQL_ID HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE COST
------- ---------- ------------- ---------- -------------- --------------- ----
493 13. Oct 07 1yw85nghurbkk 3786124882 1455318379 4095786543 9
502 13. Oct 07 1yw85nghurbkk 3786124882 1455318379 4095786543 9
582 15. Oct 07 1yw85nghurbkk 3786124882 1455318379 3985860841 17
602 15. Oct 07 1yw85nghurbkk 3786124882 1455318379 4095786543 9
The query result contains several values for the column
PLAN_HASH_VALUE
. Hence different
execution plans were used over time. To generate the Statspack SQL report, run the script
sprepsql.sql
(or
sprsqins.sql
if Statspack data from another database was imported) and
enter any adjacent snapshot identifiers from the preceding query result.
SQL> @sprepsql
…
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 582
Begin Snapshot Id specified: 582
Enter value for end_snap: 602
End Snapshot Id specified: 602
At the point where the script asks for the statement's hash value, make sure you enter the
OLD_HASH_VALUE
if you are using Oracle10
g
.
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 1455318379
Hash Value specified is: 1455318379
…
Known Optimizer Plan(s) for this Old 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 Last Plan
Snap Id Snap Time Active Time Hash Value Cost
--------- --------------- --------------- ------------ ----------
493 13-Oct-07 21:41 15-Oct-07 15:38 4095786543 9
502 13-Oct-07 21:47 15-Oct-07 15:14 3985860841 17
