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
analysis.
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
in the
v$sql_bind_capture and
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
via LogMiner.
The best way to collect bind variable for any SQL statement is by
using
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
use the
dbms_xplan.display package to display a sample bind
variable:
select
sql_text,
other_xml
from
$sql_plan
where
sql_id='XXXX'
and
id=0;
AWR dba_hist_sqlstat
Bind Variable Display
select
sql_text,
other_xml
from
$dba_hist_sqlplan
where
sql_id='XXXX'
and
id=0;
Or you can use the
dbms_xplan_display_awr procedure to see a sample bind
variable:
select
*
from
table(dbms_xplan.display_awr('&SQL_ID',NULL,NULL,'ADVANCED'))
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
