In SQL Optimizer for SQL Server we have built a very useful test run wizard (see my blog post Test Running a Tuned SQL is Not An Easy Job”) to help users select the best SQL alternatives for their specific SQL performance problems. We will soon be releasing a similar test run wizard in the Oracle version of SQL Optimizer. In this blog post, I’ll provide an example of how the Oracle test run wizard can be used.
In the above screen inside the red box you will notice that the wizard is asking how the SQL is used. Some people may not be aware that a SQL that is being executed inside a PL/SQL block and a SQL being executed as a dynamic SQL sent from a client program may have different performance. I don't know if this is a bug or Oracle SQL optimizer is expected to handle them differently. The following is an example to show a SQL statement that is executed by both methods with significant performance difference. Although they have the same cost after execution, they have different actual plans.
The following screen shows you the execution plan and performance difference between these execution methods. Be sure to select the right option to test run your SQL statement next time for those SQL with significant performance variations in different programming tools or sources.
Start the discussion at forums.toadworld.com