Creating Test Scripts With Bind Variables
Here’s a nifty little script that one of my co-workers (Jack Augustin) wrote to speed up the process of building test scripts. I thought it was a great idea, but of course I can never leave well enough alone, so I had to modify it slightly to my own taste (which by the way, I hope you will do as well). Anyway, the reason for the script is that we often get requests to improve a SQL statement that uses bind variables (sometimes lots of them). It’s pretty common to build a test script that can be run in SQL*Plus by grabbing the sql text and the values of the bind variables from the shared pool. Building the script though can be very tedious if the statements are long and particularly if there are a lot of bind variables. So this script eliminates most of that tedious work by generating a first cut at a test script based on a sql_id. The test script defines the variables, sets the values of the variables, and then executes the sql statement. A little manual editing may still be necessary, but it saves a lot of time.
Jack’s original version used V$SQL_BIND_CAPTURE to get bind variable values. I changed it to use the OTHER_XML column of V$SQL_PLAN. There are a few differences in these approaches.
- v$sql_plan has the values used when the statement was parsed.
- v$sql_plan usually has more bind variables in cases where there are a large number of bind variables. This is due to the fact that v$sql_bind_capture is controlled by _bind_capture_area_size which defaults to a value that is quite a bit smaller than the area used by v$sql_plan (thus a smaller number of bind variables).
- v$sql_bind_capture has the data type, v$sql_plan does not. (it’s got a numeric code for the data type – 1 for varchar2, 2 for number, etc…)
I called the script build_bind_vars.sql.
Well I was feeling all good about my version since it worked better on statements with lots of bind variables. But last week I was working on a system where they had turned off bind variable peeking via the _OPTIM_PEEK_USER_BINDS parameter. Thus, no bind info in the OTHER_XML field. Rats! Back to V$SQL_BIND_CAPTURE. So I made a copy of the script but changed it to get the values from V$SQL_BIND_CAPTURE.
Naturally I called this one build_bind_vars2.sql.
Note: these scripts depend on a little function called display_raw which I got from Greg Rahn. Here’s a script to create the function: create_display_raw.sql
UPDATED 01-JAN-13:
I have used this script a lot over the last several years and have modified it quite a bit. Here’s a newer version that is a little more functional (I also shortened the name to bbv.sql as I tend to do with scripts I use fairly often): bbv.sql
Now here’s an example:
> !sql
sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 23 08:51:53 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @find_sql
Enter value for sql_text: select%:4%:5%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
3hcvdhu22mjqu 0 1890474001 1 .01 76 select box_id from rule_set_rop$ where rs_obj# = :1 and ec_o
bj# = :2 and rule_id = :3 and rule_or_piece = :4 and rop_id
= :5 order by eval_id
57antna43jh0a 0 3630768429 1 .04 252 select box_id, value from rule_set_iot$ where rs_obj# = :1 a
nd ec_obj# = :2 and rule_id = :3 and rule_or_piece = :4 and
rop_id <> :5 order by rop_id
...
SQL> @build_bind_vars
Enter SQL ID ==> 3hcvdhu22mjqu
Enter Child Number ==> 0
variable N1 NUMBER
variable N2 NUMBER
variable N3 NUMBER
variable N4 NUMBER
variable N5 NUMBER
begin
end;
/
select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ where rs_obj# = :N1 and ec_obj# = :N2 and rule_id = :N3 and rule_or_piece = :N4
and rop_id = :N5 order by eval_id;
SQL> -- Oops, nothing in the section setting the variable values!
SQL> -- Let's see what's actually in the OTHER_XML field
SQL> SQL> select other_xml from v$sql_plan
2 where sql_id = '3hcvdhu22mjqu'
3 and other_xml is not null
4 /
OTHER_XML
--------------------------------------------------------------------------------
10.2.0.3 4025366187
I have been using this script a lot recently. Seems like I could keep adding things to it, but it does the job for the most part. (I still need to edit the output file occasionally to clean up split words, etc…) Also, the data type mapping is not complete in build_bind_vars2.sql, it just has types 1 and 2. Most likely I won’t get around to adding other types until I actually need them. So anyway, there it is. Let me know if you have any ideas for improving it.