In SQL Optimizer version 9, a very helpful feature was introduced:“Test Run Different Bin
d Values”. This new feature allows user to define more than one binds set that can be used in testing of possible candidates for better alternative SQLs. To clear up at the beginning, the buzzword “binds set” is a set (one or more) of bind values for a query that you analyze. Each query of course has the same number of bind variables.
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:
- Find the best query alternative(s) with one binds set
- Choose the “Test Run Different Bind Values” tab and select a few SQL alternatives for test
- Add additional binds sets
- 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.
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.
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:
- Retrieve binds sets from awr
- Retrieve binds sets from cursor
The implementation in commands is:
- @toad_binds [inst_id] [sql_id] S [From] [To]
- @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
@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.
@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)
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
@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:
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.
After all the tests are finished, you can see the 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.