m new to dis…and wnted to 2 knw how to interpret the outcome of sql optimiser…nd decide which ones is best…kndly help with the working…thanx
Hi,
Basically, SQL Optimizer provides an “Auto Optimize” button to help users quickly start optimizing SQL performance. This button will generate different rewrites for your original SQL and then test run them to find the fastest running SQL among the alternatives. A trophy icon will mark the fastest SQL after Auto Optimize.
We usually suggest users to use the execution time measured as the indication for a better alternative. Looking at the plan cost only without actually running the SQL statements is not as reliable when we come to SQL optimizing. Some users may also look at other statistic values retrieved during the test run and you should find them available in the Alternative grid after the test run.
If you have further questions, feel free to post here.
Thanks,
Alex
Hello,
I have a question about SQL Optimizer for Oracle. How much does it safe to use? I mean, will it overwrite my data in the database during the optimization? Will it perfom inserts, selects and updates on the real rows during the work? Or am I wrong to understand how it works?
Hi,
When optimizing SQL statements, SQL Optimizer will retrieve execution plans from the database which is very minimal impact. In order to determine which generated alternative exhibits the best performance, the alternatives must be tested (that is run in the database). During test execution the statement is executed, and when complete, a rollback is performed so that data is not modified when testing DML statements (update, insert, etc.). You can choose to test execute all alternatives or a subset of alternatives to suit your needs.
If you choose to utilize the indexing advice feature when optimizing a SQL, SQL Optimizer will generate alternatives by creating virtual indexes. However, if you choose to test execute an indexing alternative, the indexes are created, the statement is executed, a rollback is performed and the indexes are finally dropped. During the period when the indexes exist, they are available to other SQL that may be running in the system and may affect execution plans (for better or worse). We provide a warning in the product regarding this behavior so that the user is aware of the effect. In general, SQL Optimizer will warn users of any potential database impact beyond the obvious.
Regards,
Greg