SQL performance tuning and optimization using SQL Optimizer

No database can process all queries expediently, but we can strive to make sure our databases process SQL as efficiently as possible. That means applying SQL performance tuning and optimization to our execution plans. Remember, that a database at rest does absolutely nothing. It is only when a SQL statement is submitted that data is put into motion. We want the time from when the SQL is submitted to the database to when the final answer is delivered to happen as quickly as possible. So what can cause that time to take longer than is acceptable? How can SQL performance tuning and optimization make that process faster?

Here are the most common causes of inefficiency:

• Insufficient computing resources
• Improperly configured computing resources
• Unsuitably configured operating system parameters
• Inappropriately configured database parameters
• Poor design of tables, indexes, partitions, shards, etc.
• Inferior application design of database access methods
• Bad SQL

Of all those, the last is the most prevalent and damaging. I often joke that a Cray supercomputer could run an infinite loop in a mere three seconds! But the sad truth is that even with today’s mature database optimizers, bad SQL can easily cripple any database’s performance. Even an Oracle Exadata machine can be slowed down by unhealthy SQL. Hence, SQL performance tuning and optimization is paramount for remedying inefficient SQL and improving database performance. Since a picture is often worth a thousand words, please remember this image when thinking about SQL performance tuning and optimization.

Inefficient SQL sits under the surface of database performance

But even with 30 year’s database experience, I need a tool to assist with such efforts.   is one of the best tools I have found. And yes, some database vendors, such as Oracle, offer SQL performance tuning and optimization tuning tools. But I cannot assume my customers are using the enterprise database version with the optional diagnostics and tuning packs. And even when they do, I have found those tools to be far more oriented toward suggesting database configuration and structural modifications, as opposed to basic SQL tuning recommendations. So I prefer SQL Optimizer since its primary focus is SQL rewrites while also offering all the other areas for improvement, including database configuration and structural modifications. So let’s examine a real world scenario where SQL Optimizer helped me with SQL performance tuning and optimization.

Once during a code review, I saw a SQL statement that was written using the old, non-ANSI join syntax. So a red flag went up immediately that this SQL might be a possible candidate for rewriting. I next ran the original query to establish that it ran slowly and to provide a baseline for comparisons. Now before diving into explain/execution plans, I considered all the equivalent rewrites to match the original SQL. I came up with four, so here are the original and those four equivalent SQL rewrites.

NON-ANSI JOIN

select a.LASTNAME, a.FIRSTNAME, b.RENTALDATE,

       d.COPYFORMAT, sum(b.TOTALCHARGE) REVENUE

from employee a, movierental b, rentalitem c, moviecopy d

where a.EMPLOYEEID = b.EMPLOYEEID

and b.RENTALID = c.RENTALID

and c.MOVIECOPYID = d.MOVIECOPYID

and a.LASTNAME = 'Smith'

and b.CUSTOMERID IN (

             select customerid

             from customer e

             where e.address like 'A%'

                 or e.address like 'F%'

                 or e.address like 'T%'

                 or e.address like 'Z%' )

group by a.LASTNAME, a.FIRSTNAME,

         b.RENTALDATE, d.COPYFORMAT

ANSI JOIN

select a.LASTNAME, a.FIRSTNAME, b.RENTALDATE,

       d.COPYFORMAT, sum(b.TOTALCHARGE) REVENUE

from employee a JOIN movierental b using(EMPLOYEEID)

              JOIN rentalitem c using (RENTALID)

               JOIN moviecopy d using(MOVIECOPYID)

where a.LASTNAME = 'Smith'

and b.CUSTOMERID IN (

             select customerid

             from customer e

             where e.address like 'A%'

                or e.address like 'F%'

                or e.address like 'T%'

                or e.address like 'Z%' )

group by a.LASTNAME, a.FIRSTNAME,

       b.RENTALDATE, d.COPYFORMAT

ANSI JOIN + IN àEXISTS CORRELATED SUBQUERY

select a.LASTNAME, a.FIRSTNAME, b.RENTALDATE,

       d.COPYFORMAT, sum(b.TOTALCHARGE) REVENUE

from employee a JOIN movierental b using(EMPLOYEEID)

               JOIN rentalitem c using (RENTALID)

               JOIN moviecopy d using(MOVIECOPYID)

where a.LASTNAME = 'Smith'

and EXISTS (

             select 1

             from customer e

             where e.CUSTOMERID = b.CUSTOMERID

               and ( e.address like 'A%'

                 or e.address like 'F%'

                 or e.address like 'T%'

               or e.address like 'Z%' ) )

group by a.LASTNAME, a.FIRSTNAME,

         b.RENTALDATE, d.COPYFORMAT

ANSI JOIN + INLINE VIEW / DERIVED TABLE

select a.LASTNAME, a.FIRSTNAME, b.RENTALDATE,

       d.COPYFORMAT, sum(b.TOTALCHARGE) REVENUE

from employee a JOIN movierental b using(EMPLOYEEID)

               JOIN rentalitem c using (RENTALID)

              JOIN moviecopy d using(MOVIECOPYID),

       ( select customerid

         from customer e

         where e.address like 'A%'

             or e.address like 'F%'

             or e.address like 'T%'

             or e.address like 'Z%' ) e        

where a.LASTNAME = 'Smith'

and b.CUSTOMERID = e.CUSTOMERID            

group by a.LASTNAME, a.FIRSTNAME,

         b.RENTALDATE, d.COPYFORMAT

ANSI JOIN + COMMON TABLE EXPRESSION (CTE)

with f as ( select customerid

           from customer e

           where e.address like 'A%'

              or e.address like 'F%'

              or e.address like 'T%'

               or e.address like 'Z%' )

select a.LASTNAME, a.FIRSTNAME, b.RENTALDATE,

       d.COPYFORMAT, sum(b.TOTALCHARGE) REVENUE

from employee a JOIN movierental b using(EMPLOYEEID)

               JOIN rentalitem c using (RENTALID)

               JOIN moviecopy d using(MOVIECOPYID),

               f    

where a.LASTNAME = 'Smith'

and b.CUSTOMERID = f.CUSTOMERID            

group by a.LASTNAME, a.FIRSTNAME,

         b.RENTALDATE, d.COPYFORMAT

Wow – my brain hurts already. But we’re nowhere near done, because each of these alternatives could possibly be augmented by optimizer hints, thereby offering many thousands of alternatives per rewrite.

How do you know which rewrite and which optimizer hints to choose that will run best and work with the existing data structures? Are there modifications to existing data structures which would result in plans that run faster? Are there new data structures to be added which would improve run times? Are there existing data structures which could be dropped to obtain better results (or more safely just ignored for this query)? It would be at least a week’s worth of manual efforts to answer all these questions while hoping that we don’t miss anything from the many thousands of alternatives.

SQL Optimizer makes short work of this rather complicated scenario. I’m not going to show every step of the SQL performance tuning and optimization process, but rather how I attacked one major rewrite option to find its best minor rewrite options with hints. Then from there, I would have to repeat for each major rewrite alternative, and simply choose the best run time among the final options for each major rewrite. It’s much easier than it sounds.

In my screenshots, Oracle 19c running on Microsoft Windows 2019 Server.

I’ll use the common table expression rewrite for demonstration for two reasons. First, it’s the newest syntax option which many people would like to focus on due to that newness. Second, because I was hoping it would be the best alternative of the four major SQL rewrite options. However, full transparency, for the test data loaded the run times are identical between the original and four major rewrites; all five coding styles essentially result in the exact same performance. But this might not always be true. You always need to verify the results for yourself. In this case, trust the Oracle optimizer to do its job, but verify that we provide it with the best SQL code for it to optimize.

Here is the explain plan for the CTE query shown in Toad for Oracle. I see multiple table access full scans, multiple nested loops, and only two indexes used. So I suspect we can do better.

Now I’ll admit that I’m very lazy, so I want to do this as quickly as possible. SQL Optimizer can be run as a standalone product with tons of advanced features and capabilities. But SQL Optimizer also is integrated into the Toad product line, Xpert Edition and up. To vastly streamline my workflow for this use case, SQL Optimizer is integrated into Toad for Oracle Xpert Edition. So, I can simply open the right-hand mouse menu in the SQL Editor and choose Optimize à Auto Optimize SQL.

SQL Optimizer integrated into Toad for Oracle

That will open a pop-up window with some very basic options for you to choose settings appropriate to your SQL performance tuning and optimization goals. Here, I’ve selected that it’s a general purpose type OLTP database, search half as hard as the maximum allowed, limit the search to 10 minutes, run the SQL rewrites to time and rank them, and do some common refactor rewrites during the process.

Auto optimize SQL dialog box asks for settings to start SQL performance tuning and optimization actions

Note that SQL Optimizer tested 1,274 rewrites and concluded that the basic CTE SQL code was as good as possible. When I performed this check on all four major SQL rewrite versions from above, I found out that the original SQL with an index hint produced the fastest run time. It took me less than 30 minutes to arrive at this solution with a high degree of confidence. Plus there was very little manual effort to reach my conclusion. That’s exactly what a good SQL performance tuning and optimization tool should do for you.

SQL Optimizer tested 1,274 SQL performance tuning and optimization rewrites

Next time we’ll look at using the advanced standalone version of the product. In the meantime, you can download a trial from the Quest website.

Related information:

Blog: SQL query optimization: Level up your SQL performance tuning

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!