TL;DR: You can see what the content of a bind-variable was in v$sql_bind_capture. You can then use that to generate re-play-scripts for queries. There are some caveats though.
Have a look at the "ADVANCED" option of dbms_xplan:
select * from table
( dbms_xplan.display_cursor ('sql_id',0, 'ADVANCED'));
Somewhere in the 100+ lines of output you will find the list of bind-vars and their (peeked) values. You could use those values to re-run the query with same or similar, or different values. Ideal for testing.
Intrigued as I was, I reasoned there had to be some place this information was stored, and I stumbled across v$sql_bind_capture. After a bit of experimenting, I ended up with two queries I could use in scripting:
I can now define the variables for use in an SQL script:
-- define variables
select 'Variable ' || replace ( bvc.name, ':', 'b' )
|| ' ' || bvc.datatype_string ||';'
from v$sql_bind_capture bvc
where 1=1
and sql_id = '&sql_id'
and child_number = 0
order by position, child_number, name;
And I can generate code to set the variables to the values stored:
select
--bvc.*
-- ANYDATA.AccessTimestamp(bvc.value_anydata)
-- ANYDATA.Accessdate(bvc.value_anydata)
' ' || replace ( bvc.name, ':', ':b' ) || ' := '
|| decode ( substr ( bvc.datatype_string, 1, 3)
, 'NUM' , nvl ( bvc.value_string, '''''' )
, 'VAR' , '''' || bvc.value_string || ''''
, 'DAT' , ' to_date ( ' || '''' || ANYDATA.Accessdate(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '
, 'TIM' , ' to_timestamp ( ' || '''' || ANYDATA.AccessTimestamp(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '
, bvc.value_string
) || ' ;' from v$sql_bind_capture bvc
where 1=1
-- and child_number = 1
and child_number = 0
and sql_id = '&sql_id'
order by position, name, child_number;
Notice the trickery needed to extract the date-values. Beware of your NLS settings here.
With those possibilities, I am very close to re-running the query with either the original values or with values that I can edit into a script. This will allow me to replay queries and to test fixes.
I short, once I know the sql_id, what I can now do (and largely automate!) is:
- find the offending SQL from v$sqlarea or v$sql.
- use the sql_id to find the types and values of the bind-variables.
- semi-automate the definition and the setting of the bind-vars.
- paste in the SQL and run it, and repeat-run it with various values for bind-vars or with edited varieties of the SQL.
My script to do this is called mk_rerun.sql. I'll include the script below, and you can test if for yourself using sqlcl or sql*plus. The script takes 1 argument, the sql_id:
SQL> @mk_rerun <sql_id>
It will generate a script called rerun_<sql_id>.lst that you can edit and use for re-run. With this trick, it now takes me less then 1 minute to find + test the re-running of a problem-SQL.
There are some caveats, and I want to mention two warnings:
Firstly, bind-variables are often shown as placeholders like :1, :2 etc.. and those are not valid variable-names in an sql-script. Therefore you need to edit the script to make variables start with a character. Hence I tend to stick a "b" in front of the variable-name. This changes :1 (invalid variable name) into :b1 (perfectly valid)
Secondly, Date-variables don't work in sql-scripts.
Hence you need to cast them into dates : to_date ( value, format)
There are a few other quirks you might encounter when you try to automatically re-run SQL statements, but you are well on your way now.
Happy Scripting!
In case someone wants to reuse my script: mk_rerun.sql:
set ver off
set feedb off
set head off
@set_nls
spool rerun_&1
-- try picking bind-vars from memory: define + assign
-- define variables
select 'Variable ' || replace ( bvc.name, ':', 'b' ) || ' ' || bvc.datatype_string ||';'
from v$sql_bind_capture bvc
where 1=1
and sql_id = '&1'
and child_number = 0
order by name, child_number, name;
prompt /* */
prompt /* -- now all varibles declared, will try to assign from cursor-0 */
prompt /* */
prompt BEGIN
select --bvc.sql_id
-- , bvc.child_number chld
--, bvc.name bind_variable
--, bvc.datatype_string datatype
--, bvc.value_string bind_value
--bvc.*
--ANYDATA.AccessTimestamp(bvc.value_anydata)
-- ANYDATA.Accessdate(bvc.value_anydata)
' ' || replace ( bvc.name, ':', ':b' ) || ' := '
|| decode ( substr ( bvc.datatype_string, 1, 3)
, 'NUM' , nvl ( bvc.value_string, '''''' )
, 'VAR' , '''' || bvc.value_string || ''''
, 'DAT' , ' to_date ( ' || '''' || ANYDATA.Accessdate(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '
, 'TIM' , ' to_timestamp ( ' || '''' || ANYDATA.AccessTimestamp(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '
, bvc.value_string
) || ' ;' from v$sql_bind_capture bvc
where 1=1
-- and child_number = 1
and child_number = 0
and sql_id = '&1'
order by name, child_number;
select 'END;'from dual ;
select '/' from dual ;
prompt set autotrace off
prompt /* -- */
prompt /* -- Paste formatted statement here, followed by semicolon... */
prompt /* -- */
prompt /* -- SQL goes HERE, with semicolon added! */
promp /* -- Will include explain to catch stmnt. */
prompt /* -- */
prompt /* -- use this file to edit + run stmnt with variables */
prompt /* -- SQL> ed rerun_&1.lst */
prompt /* -- SQL> @rerun_&1.lst */
prompt /* -- */
prompt /* first execute goes here, can use output from xplan to get SQL */
prompt /* please remove plan... */
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR('', 0, 'BASIC' ));
-- and repeat with autotrace on
prompt
prompt
prompt set autotrace on
prompt set timing on
prompt set linesize 150
prompt set feedback on
prompt
prompt /* -- and 2nd run with autotrace on */
prompt /
prompt set autotrace off
prompt set timing off
prompt /* -- */
prompt /* -- use this file to edit and run stmnt with variables */
prompt /* -- SQL> ed rerun_&1..lst */
prompt /* -- SQL> @rerun_&1..lst */
prompt /* -- */
spool off
set feedb on
End of script mk_rerun.sql
No comments:
Post a Comment