Apr 27, 2021 1:30:00 AM by Dan Hotka
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:
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:
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.
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.
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 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.
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.
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
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.
Improve your code quality
Want to save hours, increase accuracy and enjoy easier workdays? We’re offering our top three Toad editions so you can try Toad free for 30 days. Which Toad edition is right for you? Find out with our feature matrix.
Are you already in a trial? Talk to sales or buy now online.
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.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers and lots of experienced users.
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!
Written by Dan Hotka
Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.
Dan's most recent book is Toad for Oracle Unleashed