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
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 -------------------------------------------------------------------------------- <other_xml><info type="db_version">10.2.0.3</info><info type="parse_schema"><![C DATA["SYS"]]></info><info type="plan_hash">4025366187</info><outline_data><hint> <![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ ENABLE('10.2.0.3')]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint ><![CDATA[INDEX(@"SEL$1" "RULE_SET_ROP$"@"SEL$1" ("RULE_SET_ROP$"."RS_OBJ#" "RUL E_SET_ROP$"."EC_OBJ#" "RULE_SET_ROP$"."RULE_ID" "RULE_SET_ROP$"."RULE_OR_PIECE" "RULE_SET_ROP$"."ROP_ID" "RULE_SET_ROP$"."EVAL_ID"))]]></hint></outline_data></o ther_xml> 1 row selected. SQL> -- Ah, no bind data in the OTHER_XML column, we'll have to get values from v$sql_bind_capture SQL> @build_bind_vars2 Enter SQL ID ==> 3hcvdhu22mjqu Enter Child Number ==> variable N1 NUMBER variable N2 NUMBER variable N3 NUMBER variable N4 NUMBER variable N5 NUMBER begin :N1 := 8809; :N2 := 8794; :N3 := 0; :N4 := 0; :N5 := 0; 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> -- OK, let's try executing the script SQL> @3hcvdhu22mjqu PL/SQL procedure successfully completed. no rows selected SQL> -- Well it looks like it worked, let's see if we can find the test statement SQL> @find_sql Enter value for sql_text: %test 3hcvdhu22mjqu% Enter value for sql_id: SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT ------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------ 7ndfkkg214gd1 0 4025366187 1 .01 85 select /* test 3hcvdhu22mjqu */ box_id from rule_set_rop$ wh ere rs_obj# = :N1 and ec_obj# = :N2 and rule_id = :N3 and r ule_or_piece = :N4 and rop_id = :N5 order by eval_id 1 row selected. |
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.
osborne:
Oh, I meant to mention that Dion Cho has good post regarding v$sql_bind_capture here:
http://dioncho.wordpress.com/2009/05/07/tracking-the-bind-value/
And Jonathan Lewis had a short post which generated a fair amount of discussion here:
http://jonathanlewis.wordpress.com/2008/07/24/bind-capture/
Kerry
July 23, 2009, 5:27 pmBlogroll Report 17/07/2009 – 24/07/2009 « Coskan’s Approach to Oracle:
[...] Kerry Osborne-Creating Test Scripts With Bind Variables [...]
July 27, 2009, 9:29 amSQL_PLAN – other_xml « OraStory:
[...] other posts regarding OTHER_XML: Kerry Osborne using OTHER_XML to create test scripts with bind variables Randolf Geist using OTHER_XML to get outlines / create [...]
December 31, 2009, 6:15 amKerry Osborne’s Oracle Blog » Blog Archive GATHER_PLAN_STATISTICS - Kerry Osborne’s Oracle Blog:
[...] have a script that helps generate the test script (see this post: Creating Test Scripts with Bind Variables). But there are definite drawbacks to this approach. It can be difficult to duplicate the exact [...]
February 9, 2010, 12:02 pmJimH:
On the script build_bind_vars.sql, would you be able to post/share the enkitec.display_raw() function? (The Oracle sqltxplain util has the sqlt$a.boil_raw() function - just curious on your approach). Thanks, Jim.
April 1, 2010, 11:42 amosborne:
create or replace function enkitec.display_raw (rawval raw, type varchar2)
return varchar2
is
/* as suggested by Greg Rahn */
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = ‘NUMBER’) then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = ‘VARCHAR2′) then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = ‘DATE’) then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd,’dd-mon-yyyy’);
elsif (type = ‘NVARCHAR2′) then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = ‘ROWID’) then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = ‘CHAR’) then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return ‘UNKNOWN DATATYPE’;
end if;
end;
/
Also I added a link in the text of the post.
April 1, 2010, 1:27 pmAnsh abhishek:
Hi
Your blog is very inspiring …it is like a boon for perf guys . thank you
I am not able to find build_bind_vars2.sql on your blog.
Regards
June 18, 2010, 3:19 pmAnsh Abhishek
osborne:
Anish,
Thanks for the kind words. Try the link now. (the script was actually there but somehow the link had gotten messed up in the post) Should be fixed now.
Kerry
June 18, 2010, 4:35 pm