Toad World Blog

Custom Bind Values in SQL Optimizer

Aug 25, 2017 2:41:00 PM by Damir Vadas

Introduction

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:

  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!

 

Tags: Toad for Oracle

Damir Vadas

Written by Damir Vadas

Work with Oracle technology more than 16 years. I start as a PL/SQL and Delphi developer and very soon transformed into Oracle DBA. Several years enjoyed to work as Oracle APPS DBA, where have meet very nice PL/SQL technology solutions from Oracle.

Also, for fun start to play with Apex in early days, and developed the most valuable site in Croatia (skdd.hr, former sda.hr) in Apex 2.2. This site is repository of all Croatian's stocks, so security and performance is something that was very important. Site works smoothly from 2006 with upgrading to current 4.x release (beside some functional upgrades-not done by me).

Mine love with Apex last until today and find this tool very handy for many complex problems and projects today.

Current work is Application DBA for AMDOCS systems in one big telecom operator. Totally different approach with big and respective system. And keeping it's performance is really nice experience for me. Enjoy it.

My interesting work was as DBA on Oracle RMS, very unique and sensitive Oracle technology, which shows me where Oracle is streaming today. This activity help me understand very big project problems and how to solve that in everyday praxis.

Also in last years work as Toad for Oracle teacher, where practice with great success in 1/2 days Oracle for Toad training. Program includes separate training for developers and DBA. Here I saw real need of real users, what is really great and precious experience.

Mine blog helps me to express what really excite me a important but also to make something as proper documentation for easier solve when meet with similar problem in the future.

In all of those years, Toad was one of mine main tools and in last 5 years certainly most important one. For the end I'll accent that Toad help me to be a better DBA-but as always right tools is not enough to get success but to make easy a path to it.

Cheers!