Bind variables...
Every wonder what the bind variables are that were used to create an explain plan ??
This a way to find out what bind variables were used, so you can use those same values to try to find your performance issue.
STEPS
1) Find the sql_id for the sql statement you are trying to track down.
2) Run this query against your instance (this example is for sqlid ayuzt3rhdkjkp). The output will include a section with the peeked binds. This tells you what bind variables were used when doing the last hard parse..
select * from table(dbms_xplan.display_cursor('ayuzt3rhdkjkp',null,'typical +peeked_binds'));
...
...
...
Peeked Binds (identified by position):
--------------------------------------
1 - :1 ( NUMBER): 5
2 - :2 (NUMBER): 11111
3 - :3 (NUMBER): 2
4 - :4 (NUMBER): 44444444
...
...
*********************************************************************************
Now you want to know what happened historically ??? Run this query
Select distinct snap_id,
(select end_interval_time from sys.dba_hist_snapshot b where a.snap_id=b.snap_id) snap_date_if_known ,
plan_hash_value from sys.wrh$_sql_plan a where sql_id='ayuzt3rhdkjkp' order by snap_id;
This will give you the plan_hash_values over time.. Note that the snapshot date is not always filled in, but the snap_id will give you an idea of when the plan changed.
Now you want to know what bind variables were used with all the hard parses over time ?? Run this query.
select * from table(dbms_xplan.display_awr('ayuzt3rhdkjkp',null,null,'typical +peeked_binds'));
The second value can be a specific plan_hash_value if you wish.
This is a ANOTHER good reason to save AWR data for more than 7 days.