SQL query optimization: Level up your SQL performance tuning

Can’t find time for SQL performance tuning? Reliant on overstretched DBAs who don’t have a spare minute? Don’t let your queries slow down the database. Learn how to optimize SQL query — in minutes.

This is the second blog in a series that focuses on how Quest® Toad® can display SQL execution plans and the usefulness of that information. SQL performance tuning using Toad for Oracle Base Explain Plan was the first blog. This second article will include:

• Illustrating using Explain Plans covered in the prior blog SQL Performance Tuning using Toad for Oracle Base edition
• A simple but useful trick, with a hint to find the best SQL performance
• Using SQL Optimizer with Toad for Oracle Xpert edition to do the same thing (find the best SQL performance for a given SQL statement) – but automatically

If you’ve got a sluggish database, the quickest and easiest way to solve this problem is with SQL Optimizer. This feature is included in Quest Toad for Oracle Xpert Edition and above. This tool analyzes, rewrites, and evaluates SQL statements located within database objects, files or collections of SQL statements. It also provides replacement code that includes the optimized statement. Plus, it provides:

• A complete solution for index optimization and analysis of plan changes
• Index recommendations for multiple SQL statements
• Index impact analysis simulations
• SQL execution plan alternatives

Oh yeah, I get excited about this feature because SQL Optimizer takes SQL tuning to a whole other level. Best practices for query optimization are built right in, and risk is mitigated when you choose pre-written index recommendations. Keep reading. I’ll compare SQL tuning in Toad for Oracle Base Edition with advanced SQL tuning in Toad for Oracle Xpert Edition. You might find that the base edition takes care of your needs just fine. But, it’s always great to know that there’s another solution out there in the same toolset that’s just an upgrade away. 

Most asked question: Got any easy SQL performance tuning tips?

I get a lot of questions on SQL performance tuning. Something we do in class that starts with a simple optimization hint is to try different leading tables in a multi-table SQL statement.

The more rows you can eliminate early in the explain plan process, the faster the SQL will perform.

One of the lines of thought I like to repeat in my SQL Performance Tuning class is the more rows you can eliminate early in the explain plan process, the faster the SQL will perform. There is not time to go into a lot of detail on understanding explain plans, but basically you follow the most indented part of the explain plan out…I call this going up the explain plan tree.

Single table SQL statements are rather easy to tune. You review the WHERE clause statements and see which WHERE clause statement eliminates most of the rows and make sure it is being selected as the inner most step. Make sure to display Access Predicates and Filter Predicates (illustrated in the prior blog).

Multi table SQL: it’s a bit more complicated

Multi table SQL statements has a bit more processing in the explain plan. The Oracle Optimizer does a valid attempt, using available statistics, to assemble the explain plan so the most rows to be eliminated are accessed first. The other thing the optimizer does is do the table joins first.

In the related links below, there are some links for how to read explain plans and what some of the content means.

LEADING hint: to find the best performing SQL for a particular table order

Using the LEADING hint, you can tell the optimizer in which order to process the tables. I use this technique to shuffle around the tables to find the best performing SQL for a particular table order. If the performance of this SQL isn’t good enough, I start with this one and tune from there. My class goes into great detail on how indexes work, how sometimes too many indexes are the issue (Toad for Oracle actually does the index monitoring I illustrate in class), and how to code for better performance.

Figure 1: Toad for Oracle LEADING hint

This illustration shows the LEADING hint in use. I dragged and dropped it from Code Snippets (again covered in the prior blog). Now, you list the tables in the hint in the order in which you want them considered.

Figure 2: Best performing SQL using LEADING hint

Here I am shuffling the table names around in the LEADING hint and re-executing the SQL. Along the bottom will show the execution time of each.

Figure 3: Using Query Viewer

Notice the Query Viewer tab along the bottom third of Toad for Oracle. This display shows the SQL you have been executing and its runtime. You click on one and it will also show you the SQL.

Figure 4: Accessing fastest SQL

You right-click on the SQL in the Query Viewer and Toad for Oracle allows you to copy it to the clipboard, to the editor window, or to an editor window in a new tab. I have also used F8, SQL Recall, to track multiple changes to a SQL statement.

SQL Optimizer: auto optimize SQL, even robust SQL with subqueries

This shuffling around business is easy with simple SQL like the example here. But what if you have more robust SQL with subqueries perhaps. In class, we go into detail on how to code this SQL using inline views and other useful techniques. SQL Optimizer also performs this step automatically!

Figure 5: SQL Optimizer button

With your SQL in the editor window, you select this button from the tool bar or from menu item Database àOptimize àAuto Optimize SQL.

Figure 6: SQL Optimize Setup Panel

A panel appears and gives you some control over how much optimization you desire, how long to let the SQL run before trying another option, etc.

IF the SQL executes for a long time, you can select the Generate rewrites only option and try the ones when you have time to monitor each.

The Refactor SQL option does some aggressive rewrites of your code. This is what we learn in class, you will want to do this especially if your SQL contains sub-queries or inline views.

Figure 7: SQL optimization in action

In just a few seconds, SQL Optimizer had discovered 35 different variations of my SQL and began executing each. You can clearly see in the elapsed time column the best performing SQL.

Let’s take a look at a couple of the options SQL Optimizer found.

Figure 8: SQL optimization in action

Now, you can click on the Name column and review the alternative suggestions. SQL Optimizer uses a variety of hints but also does refactoring if you allow it. Refactoring allows for SQL rewrite. This particular example tries to improve performance by telling the SQL to use a particular index.

Figure 9: SQL optimization in action

I picked this one to illustrate because it is a poor performing one. In our example above, you notice that my poorly performing example also lead with tables A and C. The LEADING hint is a newer version of the ORDERED hint. The LEADING hint allows you to list the tables in the order you wish them to be processed in the hint itself. The ORDERED hint takes the tables in the order they appear in the WHERE clause. The USE_NL (nested loop hint) is one we play with in class. This specifics not only the join order (tables A and C) but to use a nested-loop join condition.

Just an fyi…there is no WHERE clause in this query that relates table A and C together so you will always get a cartisian join! This is why this option performs poorly.

Like my manual method of simply using the LEADING or ORDERED hint and shuffling the table order, SQL Optimizer does this for us along with considering dozens of other options, in its Quest for the best SQL Performance for this sql statement. Pun Intended.

I hope you enjoyed this blog. The Toad for Oracle Xpert edition is rich with features that assist in SQL performance tuning. The SQL Optimizer will greatly assist you in finding the optimal SQL Performance for your SQL statement. Thanks for reading! – Dan.

Watch the related 7-minute video:

Related information:

Webinar: Boost Database Performance with SQL Optimizer in Toad® for Oracle Xpert Edition

Webinar: Banish Bad Code with Toad® for Oracle Pro and Xpert Editions

Blog: SQL performance tuning using Toad® for Oracle Base Explain Plan

Blog: Lucky Breaks While Performance Tuning Oracle

Blog: 9 blogs to help tune your most challenging SQL Statements

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

Blog: The performance teeter-totter—Toad Turnpike: Real stories from the road

Quest Blog: Why SQL Performance Tuning is the Most Important Database Management Skill to Have

Quest Blog: 10 Database Tuning Best Practices

Dan Hotka has several course offerings in and around SQL Performance Tuning using the Toad for Oracle suite.

Have questions or comments?

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

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!