SQL performance tuning is the focus of this blog and video tutorial series. First in a three-part series, we'll highlight features in our different editions of Toad for Oracle, starting with Base (Explain Plan), then Xpert and concluding with DBA edition.
This article will include:
- Getting an Explain Plan.
- Adjusting Explain Plan output.
- Using DBMS_XPLAN output
- Using Oracle Optimizer hints with Toad for Oracle
What is SQL performance tuning?
The Oracle database functions totally on SQL being submitted by your application process (application SQL) and by Oracle itself to process your submitted SQL (dictionary SQL or recursive SQL). Toad for Oracle exposes the execution plan for any given SQL statement. There is far more content on this topic than what I can share in this short blog post, but it goes to say that Toad for Oracle is the best SQL Performance Tuning tool available, in my humble opinion.
The faster this application SQL executes, the more quickly data is returned to your application. SQL performance is affected by a number of factors that include data distribution, coding style, and available other resources such as indexing and partitioning.
This series of blog posts will focus on how Toad can display SQL execution plans and the usefulness of this information. In the Related Information at the bottom of this blog, I have included links to better understand the Explain Plan process and the contents of the Explain Plans.
Getting an Explain Plan
Toad has always had a nice Explain Plan.
Every SQL statement has an execution plan. The cost-based optimizer arrives at this (I spend a half a day on this in my SQL Performance Tuning class) via statistics and math. The Explain Plan is a visualization of this execution plan. Most tools, including Toad, uses a PLAN_TABLE to present the steps of this Explain Plan. This is a physical table that needs to exist for these tools to use. Toad has its own, but I prefer not to use it and instead use the one associated with the Oracle database, and Toad lets me do this easily.
Toad for Oracle Options Panel
To change this to the one Oracle now provides (Oracle10 made it a virtual table, prior to that, it was a script that needed to be run…), go to Toad Options (button or on the menu bar View à Toad Options and pick Oracle General. Change the Explain Plan from TOAD_PLAN_TABLE to just PLAN_TABLE and you are good to go. There are subtle changes in this PLAN_TABLE between versions of Oracle. The Toad version works but is rather generic. I prefer to use one associated with the database.
Toad Explain Plan Button
Click the Explain Plan button and you will get a nice graphical Explain Plan for your SQL statement.
Toad Explain Plan
This illustration shows the default graphical tree Explain Plan. Notice the cost numbers and related objects automatically appear. The steps are numbered in the order in which they are executed.
Adjusting Explain Plan output
Toad is a very flexible tool. There are various useful Explain Plan options as well.
Toad Explain Plan Content Panel
Right mouse click on the Explain Plan and select Adjust Content from the drop down menu (see drop down menu illustration below). This panel allows you to add/change/hide PLAN_TABLE columns.
I always display Access Predicates and Filter Predicates as this will show your SQL where clause items. IF you use the QB_NAME hint, these labels show up in the QBlock Name column. The as column will make these items their own column in the Explain Plan display. This is your call.
Toad Explain Plan Adjusted Content Panel
Now notice the where clauses appear as well as the SEL$1 QBlock Name (this is the default internal row set name that is passed up the Explain Plan tree). I find it necessary to coordinate the where clause of your SQL with the lines in the Explain Plan that it is associated with.
QBlock name is useful when working with SQL that has a lot of sub queries in it. Access predicates generally access physical objects like a table or an index. Filter predicates generally are filtering out data from the internal row set being developed. This internal row set becomes the rows that will be eventually returned to your application.
Using DBMS_XPLAN Output
Toad Explain Plan Menu Options
The drop down menu on the left is what you first get when you right mouse click on the Explain Plan itself.
Another important item to check here is Load cached planif possible. This will retrieve the Explain Plan that this SQL last executed with, if this Explain Plan is available.
This illustration is showing something I usually do as well, which is switch over to the character-mode Explain Plan provided by Oracle Corp. To do this select Display mode then from that menu, select DBMS_Xplan.
Toad Explain Plan DBMS_XPLAN Display
If you are used to seeing this display, easy enough for Toad to produce this as well. This style of Explain Plan also gives the where clause items, but is not near as flexible (is not flexible at all) as the above-mentioned graphical Explain Plan that is the default Explain Plan with Toad for Oracle.
Using Oracle Optimizer hints with Toad for Oracle
Oracle Optimizer hints are directives to the Oracle Cost-based Optimizer to treat your SQL differently than the behavior the optimizer came up with on its own. Toad for Oracle has been the best with these hints as there is a whole category just for hints in Code Snippets…SQL Optimizer hints.
You simply drag and drop these hints from this panel into your SQL right after the SELECT, INSERT, UPDATE, or DELETE clause. From Toad Options, you can even add additional optimizer hints such as your favorite ones that might not appear in this list.
The Code Snippets panel is displayed from the menu option View èCode Snippets. You change the category to SQL Optimizer hints. The push pin in the upper right corner of this panel allows for the Code Snippets panel to auto hide along the right hand side of Toad for Oracle.
Optimizer Hints via Code Snippets
The Toad for Oracle Base edition is rich with features that assist in SQL performance tuning. I hope you enjoyed this blog. Keep watch for additional blogs on this topic that feature SQL performance tuning with advanced editions of Toad for Oracle.
Did you know there’s 3 ways to get Toad for Oracle Base?Subscription / eStore: buy up to 10 licenses at a time, get auto update, support, announcements/invites to education Term / Perpetual: Talk to our professionals: demos, custom solutions, volume discounts Not ready to buy? Get Toad for Oracle a 3rd way … |
Watch an 8-minute video tutorial on SQL performance tuning using Toad for Oracle Base Explain Plan
Related information:
Blog: Toad SQL Editor Code Templates
Blog: Toad Explain Plan More Advanced Options
Blog: Quick and Easy SQL Optimization with Toad for Oracle
Video: VIDEO TUTORIAL: Toad Advanced Explain Plan Tips and Techniques
Video: Video: 'Dan's Dozen' Quick Toad Tips – Tip #5 – Advanced Explain Plans
Blog and Video Demo: Why an Oracle Ace thinks Toad® has the best SQL editor in the business
Oracle Documentation: Explain Plan Content
Oracle Documentation: Explaining a SQL statement: Basic Steps
Webcast: Five Cool DBA Features in Toad for Oracle Base Edition
Webcast: Top Five Use Cases for Developers for Toad for Oracle Base Edition
Chapter 10 “Toad as a SQL Tuning Tool” in Toad for Oracle Unleashed.
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!
Start the discussion at forums.toadworld.com