A common question about execution plans is the one ‘which line does Oracle operate first’. One way to find out is come on my next “Explain Plan” tutorial day – keep an eye on the website for dates and locations in Europe and the USA. Another is to use a little feature that I learned about from Dave Moore at Cargill last week. (See comment 8 for some extra notes from Dave Moore).
If you run Oracle Enterprise Manager for 9i – the Java thingy – there a little-known feature called the SQL Scratchpad (now there’s a coincidence) that allows you to run, and explain, SQL statements – a bit like a couple of commercial tools, but free(r).
To get this going, start up OEM, and click on the Databases icon to get the drop-down list of known databases. Right-click on one of the available databases and you get a drop-down menu which includes, third from bottom, “SQL Scratchpad”. Click on this, and you get the logon screen for the database.
As you acquire a connection, the SQL Scratchpad window pops up. Type in a bit of SQL, make sure the cursor is somewhere in the SQL, and hit the “lightning” icon to the left of the window to make it run. Hit the icon below that to get the Explain Plan window. The first picture below is the result of explaining “select * from dba_objects”. I’ve left a little bit of the SQL Scratchpad window in view so that you can see the available icons.
As you can see from the picture, the right hand column of text shows you the order of execution of the steps. Click on any step, and an explanation of the step appears at the bottom of the window. For a cost-based query, the extra columns about cost, rows, and bytes would also be filled.
It’s not really a good example of using explain plan, as it doesn’t tell you about the predicates (oh no! not that again) – but at least it gives you structure and the internal order of execution.
But the best is yet to come. See the button that looks like some sort of flow chart. Click on that, and you get the graphic image of the execution plan. (The other buttons let you walk through the plan in order, forwards or backwards, view the statistics of each object in turn, or generate a complete of the query and plan).
The buttons still allow you to walk the plan in order. The way it is drawn is as I described in my note on “Left-Deep Trees” – You walk anticlockwsie around the tree, ignoring nodes on the way down, operating them as you go up, passing them to their right.
Isn’t it just too cute – and it isn’t available in the 10g Web version of OEM.