Quick and Easy SQL Optimization with Toad for Oracle

SQL Optimization is intimidating for a lot of people. Even when you have great tools like Quest SQL Optimizer, people can be reluctant to hop into a different UI with so many powerful options. Thankfully, the Auto-Optimizer feature in Toad for Oracle makes tuning SQL a snap, and you don't have to leave the familiar Toad UI. Auto-Optimizer is available in Toad for Oracle Xpert Edition and higher.

If you're a DBA who is constantly bombarded with requests from developers to tune their SQL, this is a great way to have them tune SQL themselves. Here's how to get started.

 Load a problematic SQL statement into the Toad Editor window. Click on the Auto-Optimize icon on the Editor's toolbar.

Toad will prompt you to provide some information about this SQL statement.

You can give a hint to the optimizer about what kind of database you’re working on. The Search Depth slider tells Toad how long it should spend looking for alternatives. Slide it to left, try more things but take a longer time.

I like to limit my searches just by time instead. In this example, I can just set it to 2 minutes to double check my SQL while I go grab a cup of coffee.

If you don't want to actually run the alternatives we find, check the box "Generate rewrites only." That way we just show you the rewrite and you can pick and choose which ones you test. This is helpful for really long running queries.

Bert Scalzo does a great job explaining the other options on this screen in this blog post. I’ll just use the default settings.

Toad will take your original SQL statement and find you an alternative way to write and run that SQL sorted by elapsed time. Every alternative is guaranteed to give you the exact same result set as your original SQL, in the same order and everything. In the two minutes I let the Auto Optimizer go, I got 194 different ways you can write the original SQL statement and we tested 42 of the best candidates. Way faster than I could ever do myself manually!

In this case, my top four queries all run at the same speed, which is 60% faster than my original SQL. I'm interested in comparing these in order to figure out which is best for me. I can go and compare the alternatives found and get their Explain Plans or Execution Statistics.

So even though Alternative SQL #14 and #12 take the same amount of time to run, #14 uses less CPU. This is great because other optimizers would have thrown away #14 because it has a higher plan cost. This type of transparency makes the Auto Optimizer very powerful yet easy to use.

Even though I'm not an optimization wizard, I can find better SQL statements quickly and easily with Toad and the SQL Optimizer tools in the Xpert Edition. If I need to do more advanced tuning or index searching, I can launch the Advanced Optimizer from Toad, which opens the full SQL Optimizer for Oracle UI.

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 that with Toad for Oracle you can automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation? 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? Which edition will benefit you the most?

Whether you are currently a Toad customer or just getting started with our free 30-day trial, learn more and access Toad for Oracle 13.1.1 – Getting Started Guide.