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…)

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.

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

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.

[…] 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.

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.

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.

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.

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.

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 […]

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).