Within Toad, you can optimize SQL that has been identified as an issue. This is a self-contained functionality and nothing is required by the respective database vendors (Oracle, MS SQL Server). SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement and then executes the alternatives to test each statement's performance. This will provide you with execution times and runtime statistics that allow you to find the best SQL statement for your database environment.

If you have used this feature within Toad, you may be most familiar with the use case of being reactive; i.e., a SQL statement is currently running and taking a very long time. Or, you are developing SQL for the first time and want to see if it is optimal. These are the viable use cases for optimizing SQL that are most widely known.

Another use case is to proactively scan for SQL that may not be running. This is the “Scan SQL” feature within Toad SQL Optimizer. You can scan a variety of sources for SQL as follows: 


  • Data dictionary for functions, PL/SQL, packages, procedures, triggers and views


  • Source Code residing in Text/Binary files, Oracle SQL*Plus scripts and, yes, COBOL programming source code


  • From the SGA 


  • From Foglight Performance Investigator

Once this scan is run, a report is produced which will categorize your imbedded SQL statements as Complex and/or Problematic, as seen below. While these SQL statements may not necessarily need to be optimized, they certainly present an opportunity for further investigation on a finite set of statements.

Only by sending them to be optimized will you be able to see if they can be improved. 


After sending the specific SQL to be optimized, you will get your best alternatives based upon your tuning goals (Elapsed Time is the default): 


How to get the most out of Toad for Oracle

Most developers and DBAs use Toad for Oracle to reduce time and effort to develop and manage Oracle databases. But did you know Toad can now find and control sensitive data across all your Oracle databases? What else can Toad do that you didn’t know about? 

Want to save hours, increase accuracy and enjoy easier workdays? Compare our top three Toad editions and try Toad free for 30 days. Are you already in a trial? Talk to sales or buy now online.


Related information

Blog: SQL Optimizer for Oracle – a success story

Blog: What is automated SQL tuning? Time saving DBA tips.

Blog Collection: SQL Optimizer for Oracle

Blog Collection: SQL Optimizer for SQL Sever

Blog: Quick and Easy SQL Optimization with Toad for Oracle

Blog: 10 Database Performance Tuning Best Practices

Video: Optimize SQL and PL/SQL using Optimize Current SQL


Have questions, comments, or an idea for a new product feature?

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers and lots of experienced users. 


About the Author

Jeff Surretsky

Jeffrey Surretsky has been working at Quest since 2000 as a Sales Engineer focusing on a wide variety of solutions including Foglight, SharePlex and the TOAD Family of Products. Before working at Quest, he was a DBA for various organizations spanning a diverse range of industries including but not limited to Insurance and Telephony . With almost 35 years of experience in the Information Technology industry, Jeffrey has a Bachelor’s Degree in Computer Science and a Masters Degree in Management of Information Systems.

Start the discussion at forums.toadworld.com