This is a guest post from Dan Hotka, Oracle ACE Director. Learn more at his webinar Explain Plan Tips and Techniques.
Hi!
My name is Dan Hotka. I am an Oracle ACE Director. One of my many charters with this group is to help spread the good word and technical knowledge about the Oracle RDBMS. I am well published with 12 (going on 14 books) and literally hundreds of articles. I regularly blog as I will here. Maybe we have met at a trade show or user group. I regularly speak at these around the world.
I am hoping to share with you both Oracle technical knowledge and how this knowledge is reflected in Embarcadero tools.
I’ve downloaded the big 3: Rapid SQL, DB Artisan, and DB Power Studio from embarcadero.com. This blog will cover my initial thoughts and some explain plan tips utilizing Rapid SQL.
I’ve just installed Rapid SQL version 8.6.1.
I’ll show you a couple of explain plan tips in and around Rapid SQL.
I like the tool. Of course this is the perfect tool if you have various different database vendors because this tool supports like a dozen different databases. One interface to learn for all the databases! The tips I show will be for the Oracle RDBMS. The tips around the Embarcadero tools should work for you no matter which data source you are connected to.
When displaying explain plans, I like to see the explain plan and the SQL at the same time.
This is easily accomplished.
First…load your SQL into the ISQL editor window (using the Open button) then click the Explain Plan button (see red circle), the button will remain active.
Execute the SQL and the Query Plan tab will appear and populate.
Hover the mouse over any of these nodes for additional and useful information about that step of the Explain Plan!
Rapid SQL first shows a graphical explain plan. I’m from the old world of tuning…I like the text version so right click in this explain plan tab and select ‘View as Text’.
I like to see the query and explain plan at the same time:
Hide image
This is easy to do. Notice the ISQL window tabs at the bottom of the tool. First, we have to tell Rapid SQL to create the Query Plan in its own window.
Click the Options button (left red circle) and then select ‘Unattached’ for the Result Window…this will create a 2nd tab along the bottom of Rapid SQL when the SQL is executed.
Simply drag the Query Plan tab up slowly, you will see a box appear where the tab will be relocated…
Watch for my webinars and the replay that Embarcadero will host on August 20. I will cover this technique as well as various aspects of the Oracle Explain plan process.
Dan Hotka
Oracle ACE Director
Instructor/Author/CEO