Custom Bind Values in SQL Optimizer

Why is this important?

It is a well-known problem in Oracle database (and elsewhere) that some queries run nicely with some bind values, while in the same time they run pretty badly against some other value combinations. Oracle has done many things in their kernel engine to get the best results in what they call “SQL Plan Management”,  

However, in practice it is still not possible to get 100% success because of many things that are really unpredictable. And not only because Oracle “makes the wrong assumption”, problems may also arise because of specific factors in custom-defined databases.

For instance, I have seen “specific” application-dependent parameters (which actually disable or modify db parameters in such a way that they have a huge influence on finding the best execution plan). The human touch in all such and other cases is needed to choose/define best SQL execution plan. For these scenarios, big testing (against bigger number of binds) is a vital part of proper test preparation.

 

The process of defining bind values

Let me show in few steps how we can use the “Bind Values” feature in SQL Optimizer. The process may be briefly described as:

1. Find the best query alternative(s) with one binds set
2. Choose the “Test Run Different Bind Values” tab and select a few SQL alternatives for test
3. Add additional binds sets
4. Run against the added binds sets and check the overall results

It may not apparent, but it is very important to setup “real” bind values that represent real cases which represent real cases.

Why?

Because binds define how many data are involved in query. For instance, it is not the same if you have to get some customers orders and if you need to get all customer’s orders.

However real values sometimes might not be so easy to get (define). And when you have them, the SQL Optimizer interface may not make it easy to pass them automatically.

This is because the values may be spread in multiple dictionary views, and SQL Optimizer expects all of them to be saved in one table. For all queries that depend on values from more than one table, you would have to create new table with the needed binds sets in each record.

And this is where my article fits in.

 

Custom binds, my way

I have created a SQL script which collects binds sets based on custom filters and formats the output to be fully compatible with import option in SQL Optimizer. In this way no additional db objects are created, a welcome option for stronger production environments.

1. Script

The script which handles most of the task I called “toad_binds.sql”.It is a true Oracle script, which is called through Oracle SQL*Plus or any compatible tool. You can download it here: (Please visit the site to view this file)

The first two script parameters are inst_id and sql_id, which are self-explanatory. The other three parameters define two ways of retrieving binds sets:

1. Retrieve binds sets from awr
2. Retrieve binds sets from cursor

The implementation in commands is:

1. @toad_binds [inst_id] [sql_id] S [From] [To]
2. @toad_binds [inst_id] [sql_id] C [From] [To]

“S” and “C” define the basic execution type (Snap or Cursor) while From and To define number values (snap_id in the first case and child_no in the second case).

 

2. Practical use of the script

AWR use

@toad_binds 1 4qyy4vbps3bf9 S 238082 239326

…results in the creation of the file “toad_binds-1-4qyy4vbps3bf9-S_238082_239326.txt”. 

(Please visit the site to view this file)

Just for an example, in the file “sql_binds_hist_4qyy4vbps3bf9.txt” you may see all the binds history in 30 days.

 

Cursor use

@toad_binds 1 4qyy4vbps3bf9 C 0 1

Because the resulting file is smaller, the cursor way of execution can be seen in the “toad_binds_cursor_example.sql” file. The resulting file is in “toad_binds-1-4qyy4vbps3bf9-C_0_1.txt".

 (Please visit the site to view this file)

All values

If you want all values for each type of execution, you may define “-1” for the From and To values.

@toad_binds 1 4qyy4vbps3bf9 S -1 -1

Or

@toad_binds 1 4qyy4vbps3bf9 C -1 -1

 

This may generate a huge number of binds sets, but relieves the user from having to know the exact snap_id or child_no values.

In both cases, it is very easy to see the relation of the parameters and the generated file name.

For a deeper understanding of the script parameters use, read the content header of the script itself.

 

3. Generated file encoding problem

In SQL Optimizer 9.2.0, which was available to me in the writing of this post, direct import of a generated file such as this will result in an error.

The problem is wrong encoding of the generated script for which I could find no solution other than additionally encoding it in the editor. Manual encoding can be done very easy with Notepad++ in two steps:

UTF-8 format

UTF-8 setting

In SQL Optimizer 9.2.2 this bug is fixed, so manual encoding is not necessary.

After this, you are ready to import the UTF encoded file (“_UTF” in name)

 

4. Importing generated data

If you import data on the “fixed” file, you might get a small mistake again. In this case, the problem is that the previous binds set is already there as “Set 1” and would collide with the generated data.

To solve this small problem, just erase the first row from grid.

 

5. Finishing setup

When binds sets are correctly set, you just need to finish setup, defining scenarios in the usual way.

Finished setup

 

During execution:

Execution

After all the tests are finished, you can see the final results.

Final results

 

In this case you can really see how some binds sets result in a really “odd” execution plan, according average time in other cases.

SQL Optimizer armed with my scripts really helps you find the problematic parts and how to tune them.

Hope this helps someone.

Cheers!