Oracle Performance and Engineering

So far, the only way bind variable values could be obtained was by tracing a session like -

SQL> alter session set events = '10046 trace name context forever, level 8';
-- (or, level 12 - where WAITS would be trace collected too.

But with Oracle 10g tracing is not anymore trace is the only way to read the values of bind variables. Here's a quick lowdown of how to do it --

(1) SQL> Alter system set statistics_level = BASIC;
-- default is 'typical'.
-- remember when you set it to BASIC, many many things get traced. So this could be a potential overhead too.
-- this will start recording the bind variables in data dictionary
-- run the application till the statement(s) with bind variables are executed

(2) Find out the HASH_VALUE of the SQL you're looking for. E.g., to find out the HASH_VALUE of a SQL with RULE hint -

SQL> select sql_text, hash_value from v$sql a where sql_text like 'SELECT%RULE%'

(3)
SQL> select name, position, value_string from v$sql_bind_capture where hash_value = ;

Exciting, isn't it? 10g also gives a cool way of getting the *actual* EXPLAIN_PLAN from data dictionary (rather than from trace files). We will come to that later.