Question: I need to see historical SQL with the
bind variables used. Does AWR or STATSPACK store bind variable
information? Does Oracle store SQL bind variable values anywhere?
Answer: Only the PGA stores specific bind
variable and cursor values, and these are not kept for historical
The extra cost AWR allows you to see a "representative" sample
bind variable value, but all bind variable values are NOT present.
In Oracle 10g and beyond, bind variables are periodically captured
dba_hist_sqlbind views, but these are bind variables or
ONLY for a single, specific session, and not all historical session
bind variables for a SQL statement are kept.
This is because the amount of disk storage to keep a history of
all bind variable values would be huge, especially for database that
process thousands of transactions per second.
These limited bind values can be found in some AWR and ASH tables
as well as the v$sql_plan view. Also, DML statements will
have bind variable information in the archived redo logs available
The best way to collect bind variable for any SQL statement is by
TKPROF (a 10046 trace).
v$sql_plan Bind Variable Display
The v$sql_plan only shows Systemwide values and it does
not contain session-level bind variable values. However, you can
dbms_xplan.display package to display a sample bind
Bind Variable Display
Or you can use the
dbms_xplan_display_awr procedure to see a sample bind
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.