ThatJeffSmith

Getting Your Execution Plan, The Hard Way

Most Oracle IDEs (integrated development environments) these days make it really easy to get your SQL statement’s Execution Plan. Of course you have your theoretical plan, and then the actual plan that the database engine employs. You look in different places for those plans, but the tool generally makes that process invisible to the user.

Read More About Explain Plans

Where Does Toad Store it Execution Plans?

What if you want to get the plan out in a format different than what the tool is offering?

My favorite IDE gives you like 12 different ways to view the Explain, but one of my favorite users asked for format #13. Instead of forcing the tool to do something it doesn’t want, what if we just went after the raw data and built it the way we wanted?

When you ask for a plan in Toad, it gets stored in a Plan Table. The plan table is defined in the Toad Options. You’ll need to know this table name in a few seconds, so go look that up now.

We are going to employ one of my favorite tricks – invoking the ‘Spool SQL’ feature.

This puts Toad in a ‘verbose’ mode where all the SQL it sends to Oracle is displayed for our viewing pleasure. This will allow me to see what my statementID is.

On the Toad Menu, select ‘Database’, then ‘Spool SQL.’ I prefer the ‘Spool SQL to Screen’ method. This will dump all the application SQL to the Toad ‘Output’ panel. Now when we ask for the execution plan in the Editor, I can see the actual ‘explain plan set’ statement.

Note that in v11 of Toad, you can optionally ask for the cached plan.

Here is an example of what this SQL looks like – when generated from Toad.

----------------------------------
Session: QUEST_OPTI@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.20.130)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))
Timestamp: 14:16:48.803
explain plan set statement_id='JSMITH:080811141648' into TOAD_PLAN_TABLE For select * from dba_tablespaces

Now I can simply query TOAD_PLAN_TABLE WHERE statement_id = ‘JSMITH:080811141648′

The raw plan table data for my query

Now You’ll Want to Employ All of Your Toad Data Grid Tricks

Hide the columns you don’t care for, sort by step, then export to Excel – which is what my friend was asking for originally.

Yes, yes, yes – you can read your plans in Excel. Unfortunately there’s not an ‘Easy’ button to do that auto-magically in Toad. Of course if you are an XML wizard, there is an easy button for that. This actually sounds like a great enhancement request for Toad, and I’ll submit it for v11.Next!