SQL Bind Capture – Part 3

I needed to find out which variables were producing a poor execution plan. Short of enabling 10046 trace with binds enabled, I found the following is a good approximation.

Basically:

1. Find the PLAN_HASH_VALUE for the SQL_ID with the poor plan
2. Pass this value to V$SQL_BIND_CAPTURE and print the variables associated with this version (represented by CHILD_NUMBER)

select name || ' = ' || value_string from v$sql_bind_capture sbc, v$sql s
  where plan_hash_value = 1229663947
    and sbc.sql_id = s.sql_id
    and sbc.child_number = s.child_number
/