Toad World Blog

SQL performance tuning using Toad® for Oracle Base Explain Plan

Mar 9, 2021 8:50:40 AM by Dan Hotka

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

Buy Toad for Oracle Xpert Plus Subscription Edition for same price as Oracle Pro DB Admin Subscription Edition, save $400 USDBuy Toad for Oracle Xpert Plus Subscription Edition for same price as Oracle Pro DB Admin Subscription Edition, save $400 USD


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

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

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

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.

toad4

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

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

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 plan if 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

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

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 ...

Try Toad for Oracle 

 

Watch an 8-minute video tutorial on SQL performance tuning using Toad for Oracle Base Explain Plan

 

Dan_Hotka_SQL_Tuning_ToadBase

 

 

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!

 

Tags: Toad for Oracle Toad for Oracle Videos sql performance tuning explain plan

Dan Hotka

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