Toad World® Forums

find and replace bind variables in batch, batch benchmark and rank sql stmt

question text

Hi Alexei,

  1. Is there a way to find and replace bind variables in batch?

There is currently no such a function in the product. I will create an enhancement request for this to be considered in the future.

However, I think that instead of replacing the variables with literals, we should keep them in bind variables but to populate the bind values and datatypes according to what you have described. Note that the execution plan (and the performance) of a SQL may change when bind variables are replaced with literals. So we would need a way to keep the SQL using bind variables but still would not require you to enter values for them every time.

  1. Also, is there a way to batch benchmark each one of the sql statements?

It is a normal situation to have a lots of SQL statments in a system and one would like to select the worst performers to start optimizing them first. In this product, we do not have a benchmark test to rank the SQL statements but we do provide two different approaches to find SQL statements to optimize:

(1) Look for non-optimal steps in execution plans
In this approach we use the SQL Scanner module to find SQL statements, retrieve the execution plans, and then grade each SQL according to the execution plan. There are different SQL Classification rules you can define in the Options window. You can then start optimizing those SQL statements that violated your Classification rules. There are two advantages using this approach: (i) the time to identify problematic SQL is fast, there is no need to excute the SQL at all; and (ii) it helps to find SQL that run fast but actually has room to improve (e.g. a SQL may run in 1 second but can still be optimized to run in sub-seconds. You will get advantage in optimizing it if it is executed many times a day).

(2) Look at the run time statistics
In this approach we use the SGA Inspector to inspect the SQL in the SQL Area. You can define your ordering on one or multiple statistics and then select certain number of the top SQL in your criteria. For example, you can select the top 25 SQL with the most Disk Read operation. In this approach, you can use all the performance metrice available from database to find the SQL to optimize. It is also especially useful if you know your database to be IO or CPU bound, then you can select the SQL that are most IO or CPU intensive to optimize.

  1. Also is there an api that I can access the functionality? If I can’t do the stuff via the gui interface, maybe I can write a java or pl/sql program do figure out this stuff programmatically?

I am not quite sure what you are asking for. Can you please give me more information?

Thanks,
Alex

question text

Hi Alexei,

  1. Thanks for the additional information that help me understand more what you are looking for. I have created an enhancement request for this requirement to be considered in the future.

  2. Feel free to let me know if you have any question using the Classification Rule.

  3. Thanks for the details. Now I understanding better what is requested. Unfortunately, there is no function in the product that can help do the task currently. I agree that this is a good suggestion and I will go ahead to submit an enhancement request for it.

Thanks so much for your valuable input. Please feel free to post more of them.

Please ignore, posted on the wrong thread and couldn’t find a “delete” option.

Message was edited by: Zenthar