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.
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
[…] Kerry Osborne-Creating Test Scripts With Bind Variables […]
[…] 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 […]
[…] 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 […]
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.
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.
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
Ansh Abhishek
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
Kerry, I’m curious if you’ve found a way to replicate the EXACT statement from the shared pool. From your example above, statement 3hcvdhu22mjqu had bind variables listed as just numerics – :1, :2, :… How do you reproduce that as a test?
When creating your own binds, their names can’t be just a number, so the generated SQL statement never has the exact same ID. I feel like that’s critical when you’re trying to replicate a problem and then prove that anything you’ve done has fixed the issue (obviously not changing the SQL statement).
For example, for a few critical statements that are suffering from bind variable peeking, we issue \GRANT select ON TO \ to flush all items from the shared pool for that object, including the statement we’re having issues with, so it can be re-hard parsed and ideally get the plan we want. I can never show that, beyond a doubt, my replicated query will be the same the client will get, due to different SQL_IDs because of different bind names.
Thx.
Dave
It’s not possible to exactly duplicate a statement that has numeric variable names in SQL*Plus (because SQL*Plus doesn’t allow numeric variable names). As you can see, I added a character (N) in front of numeric variable names in my scripts to get around the issue. My goal was not to create a script with a statement that has the same SQL_ID, although this should be doable by leaving out the comments and (assuming you don’t run into the numeric bind variable issue). My goal was to create a script that would execute the same statement with the bind variables that were used to parse it (or at least a representative set of bind variables in cases where bind peeking was disabled). I usually use these scripts to test various hints, different bind variable values, etc… to see if I can get a better plan. Once you have a better plan, there are numerous options such as creating a SQL Profile on the production version of the statement using the hint from your modified version.
I can’t imagine that a different bind variable names can have any affect whatsoever on a statement’s plan. I’d love to see an example if you run across one.
By the way, there is a way to flush a specific statement in 11g (also back ported to 10.2.0.4). See http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/
Kerry
The problem is more likely due to me not fully understanding what’s going on with bind variables.
I’m finding statements with multiple plans, 1 good 1 bad. For the “bad” ones, I retrieve the bind variables used via “SELECT * FROM TABLE(dbms_xplan.display_cursor(‘&sql_id’,&child,’+PEEKED_BINDS’))”, and if that indeed lists peeked bind variables, I go with them. With those values, I use the same method you describe for creating bind variables and run the statement, then check in the shared pool for what plan was used. I always get the “good” plan.
At this moment I’d just like to find out what’s generating the “bad” plan, but I can’t come up with any bind values for it. That’s why I went down the road of thinking I need the exact SQL_ID.
Make sense?
Dave,
I understand the thought process but I don’t think the bind variable names have anything to do with it.
Here are a couple of thoughts on what might be causing the problem:
1. Are there any environment settings that are different between the production runs (with the bad plan) and the tests you’re doing? Try looking at X$KQLFSQCE for the optimizer settings.
2. Is it possible that data is out running stats? By that I mean, could the query that gets the bad plan have a value that is significantly outside the range of max (or min) that the stats is aware of at the point when the statement is parsed? This could also account for why replying the statement later with the same set of bind variables doesn’t reproduce the bad plan.
3. It’s actually a variation on number 2. Does the bad plan happen on a regular basis (the day after switching to a new partition for example)? Could it be that stats are set to 0 rows and the first day of using the new partition causes bad plans?
By the way, what do you plan to do if you find a combination of bind variables that cause the bad plan to appear? An expedient solution might be to just force the good plan with a SQL Profile, even if it’s only a temporary solution until you figure what’s causing the instability. My create_sql_profile.sql script might be useful for that.
Kerry
You’re absolutely correct, as usual! The problem was thinking EXPLAIN PLAN / AUTOTRACE would peek at bind variables like the CBO. That’s why the shared pool updated stats were different then our manual tests.
Thanks for sharing all your scripts, as they helped identify the problem quickly!
[…] thanks to Kerry Osborne and his amazingly useful (I have no idea how many times it made my day) build_bind_vars script. Normally building a test case for a sql which has 100 bind variables is a nightmare but with that […]
I use this SQL in smenu and a variant of this script from V$SQL and AWR.
More work needed in the area of column date mismatched with timestamp.
set lines 32000 head off trimspool off pages 0 break on fdate on report set trimspool on select line from ( select decode(position, 1, '-------------------------------------' ||chr(10) || '-- Date :'||to_char(LAST_CAPTURED,'YYYY-MM-DD HH24:MI:SS')||chr(10) || '-------------------------------------' ||chr(10)|| chr(10) || 'alter session set NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'' ;' || chr(10) -- || 'alter session set NLS_TIMESTAMP_FORMAT=''YYYY-MM-DD HH24:MI:SS.FFFFFF'' ;' || chr(10) , chr(10) )|| 'variable ' || regexp_replace( name,':D*[[:digit:]]*','a') ||to_char(position) || ' ' || case DATATYPE -- varchar2 when 1 then 'varchar2(4000)' || chr(10) || 'Exec '||regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| value_string || ''';' -- number when 2 then 'number' || chr(10) || 'exec '||regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':=' || value_string || ';' -- date when 12 then 'varchar2(30)' || chr(10) || 'exec '|| regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| decode( value_string, null, to_char(anydata.accessdate(value_anydata),'YYYY-MM-DD HH24:MI:SS') ) || ''';' -- char when 96 then 'char(3072)' || chr(10) || 'exec '||regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| value_string || ''';' -- timestamp when 180 then 'varchar2(26)' || chr(10) || 'exec '||regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| decode( value_string, null, to_char(anydata.accessTimestamp(value_anydata),'YYYY-MM-DD HH24:MI:SS') ) || ''';' --to_char(anydata.accessTimestamp(value_anydata),'YYYY-MM-DD HH24:MI:SS.FFFFFF') ) || ''';' -- timestampTZ when 181 then 'varchar2(26)' || chr(10) || 'exec '||regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| decode( value_string, null, to_char(anydata.accessTimestampTZ(value_anydata),'YYYY-MM-DD HH24:MI:SS') ) || ''';' --to_char(anydata.accessTimestampTZ(value_anydata),'YYYY-MM-DD HH24:MI:SS.FFFFFF') ) || ''';' when 112 then 'CLOB' || chr(10) || 'exec '||regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| value_string || ''';' else 'Varchar2(4000)' || chr(10) || 'exec '|| regexp_replace( name,':D*[[:digit:]]*',':a') || to_char(position) || ':='''|| value_string || ''';' end line from v$sql_bind_capture where sql_id = 'as7xqqa7711zj' and child_number = '0' order by last_captured,position, child_number, position ) union all select regexp_replace(line,'(:)D*([[:digit:]]*)','1a2')||chr(10) ||'/' line from ( select regexp_replace( max(sys_connect_by_path (sql_text,'{') ), '{','') line from ( select piece, sql_text from v$sqltext_with_newlines where sql_id='as7xqqa7711zj' order by 1 ) start with piece=0 connect by piece = prior piece + 1 ) /Hi Bernard,
Thanks for sharing. I’ve also made some changes to mine to deal with data types a little better. When I get some time I’ll have a close look at your version.
Kerry
There is a drawback to such types of statement generators: it may works too nicely.
These statement generator may prevents you to reproduce and study implicit conversions. Since Sqlplus converts varchar2 bind to date type using by default the value of NLS_DATE_FORMAT, you will convert nicely the timestamp to a date type, fully enjoying index access and partition pruning on your study while production obviously does not.
The problem comes from the Java representation of date with hours,minutes,seconds which is a timestamp. Sent to Oracle it results into a datatype 180 (timestamp). And for Oracle column type DATE, which has also hour,minutes, seconds, it is the table column which is converted by the optimizer to timestamp and not the reverse(he can’t loose precision). Doing so, you loose partition pruning and index access on those binds. While using statement generator, you see nothing since these binds are nicely converted to type Date, same as column table, hence no problems.
Absolutely there are issues with this approach. The variable type issues are the biggest drawback. There is no way in SQL*Plus to mimic a Java timestamp variable for example (at least that I’m aware of).
[…] Used: http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/ […]
[…] Creating Test Scripts With Bind Variables July 23, 2009, 9:10 am […]
[…] REF：http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/ This entry was posted in Oracle Experience and tagged internal. Bookmark the permalink. ← Exadata offloading and Smart Scan […]
[…] REF：http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/ […]
Hello Kerry,
your scripts executed from sqlplus on windows environment does not work properly.
In the specific…
“spool &&sql_id\.sql”
must be replaced with
“spool &&sql_id..sql”
Take this opportunity to ask you if you shared an updated version of your script library (the last I found is my_favorite_scripts_2010.zip).
Thank you very much.
Sandro
[…] Osborne helped us to retrieve the peeked values from v$sql_plan view into this blog post, but what about the passed values ? For those ones, Tanel Poder helped us to retrieve the passed […]
Kerry,
Thank you for sharing these extremely useful scripts!
A small remark: set long 1000000 must be set to get the sql_fulltext from v$sqlarea correctly for large queries.
Nenad