Toad for Oracle tuning

SQL Optimizer SQL Optimizer for Oracle
1

Hi,
I am using Toad for Oracle, while i try to optimize the SQL Statements i get a result saying that the original statement is the best.
Once when I optimize i get different alternate statements, when i execute all i am getting the above said result. I am getting this result for all the SQL statements which i enter.

Please help me on this matter.

Regards,
Mayur.

2

Hi Mayur,

During the optimization process, the SQL Optimizer will first do rewrite to generate different SQL alternatives and then execute them to see if there is any alternatives run faster than the original. There are a few options that will increase the likelihood to find a better alternative:

  1. Intelligence Level (Options | Tuning Lab | Optimizer) - The higher the level, the more quotas will be allocated for the rewrite process and more Oracle hints will be applied, hence potentially more alternatives can be generated.

  2. “Auto Select SQL Rewrites for Execution options” (Options | Tuning Lab | Execution | Auto Execution) - These options control what SQL alternatives are selected to execute. You may change the settings to have more alternatives selected for execute. Or you can manually choose to Execute All.

  3. “Cancel scenarios that exceed the original SQL run time by (%)” (Options | Tuning Lab | Execution | Execution Criteria) - This percentage control when an alternative will be canceled for execution. The current release has an incorrect default of 100% making the cancellation earlier than one would like (e.g. Network travel time is not considered). For SQL that run faster than original but close in performance, the default value may cause it canceled too early. I would suggest increasing it to 120%. (In future release, we will adjust the default value.)

While the above may increase the chance of finding better alternatives, there may be cases where your SQL statement is already the best in performance. Cases like if the SQL is simple and there is not much room to rewrite it; or that your amount of data is small making not much difference in the run time of any alternatives (sometimes we see this when a testing database is used where tables are empty or with only a few records). You may try more complicated SQL that has more table joins or subqueries in the WHERE clause. You may also try running your SQL in the production database or another database with more data in it (Note that if you are using version 7.2, in the Batch Optimizer, you can optimize a SQL in one database and execute the alternatives in another database).

Hope the above help.