Oracle Scratchpad

February 1, 2007

Execution Order

Filed under: Execution plans — Jonathan Lewis @ 1:17 pm BST Feb 1,2007

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.

 

 

10 Comments »

  1. It has been there for ages, DBA Studio had it in 8i for the currently running SQL, and Tuning Pack tools could use it for any SQL. It’s been a great learning help for me back then, before I could read plans in the correct “order of execution” off a telnet session :-)

    Comment by Flado — February 1, 2007 @ 1:55 pm BST Feb 1,2007 | Reply

  2. This execution tree reminds me of my old ingres days in early 90s. In Ingres 5/6 query tool (forgot the name) you could do a “set qep on” and it would dump a query execution plan in a similar manner, in text not the fancy gui.

    We have come a long way …

    Comment by Raj — February 1, 2007 @ 2:21 pm BST Feb 1,2007 | Reply

  3. Ha! Funny. Did you by chance get inspired because of the question on Oracle-L, “a explain plan question”? When I read that, I thought about this tool. Then when I looked at the 10g version I noticed that the tree thingy disappeared. Too bad…

    jan

    Comment by jan van mourik — February 1, 2007 @ 3:05 pm BST Feb 1,2007 | Reply

  4. Do you know of something that will create this tree for 10g? I’ve tried to use the 9i OEM but it doesn’t work against the 10g database. Maybe this could be a new niche market for someone.

    Comment by Eric — February 1, 2007 @ 3:52 pm BST Feb 1,2007 | Reply

  5. I don’t think it’s strictly true that the graphical tree isn’t available in 10g OEM – though it may not be in dbconsole, isn;t available for arbitrary sql, and is almost certainly another example of charging for something that was previously free by including it in a pack.

    In my installation of Grid Control 10.2 you can see this via the somewhat convoluted navigation path of selecting the Top Activity page, of picking a troublesome sqlstatement which brings up a page entitled SQL details. Clicking the plan tab then gives a graphical (or tabular) execution plan which includes the order. Simple as pie :(

    Comment by Niall Litchfield — February 1, 2007 @ 4:16 pm BST Feb 1,2007 | Reply

  6. really cute and unbelievable I didn’t notice it so far !

    Comment by Sokrates — February 1, 2007 @ 4:41 pm BST Feb 1,2007 | Reply

  7. Flowcharts are useless for tree rendering purposes for a simple reason that they take more space. For that reason you’ll never see a flowchart style tree in windows file explorer. Also there is no way to combine a tree and a table into a tree table which shows details at each node.

    BTW, check up the autotrace button in SQLDeveloper worksheet. It shows predicates “in place” and even parses them.

    Comment by Mikito Harakiri — February 1, 2007 @ 6:01 pm BST Feb 1,2007 | Reply

  8. 10g version starts and is used the same way as 9.2

    oemapp console

    is an alternative quick way to start the console from windows command prompt (runs first version it finds in your PATH)

    (I think sql scratchpad was added in 9.2, not present in 9.0 which is why many overlooked it.)

    10g Version of the “Java thingy” has same the features, except graphical tree as noted, and can still connect to 9i and 8i DBs. (You really would not need to use the 9i version)

    10g version appears to require that you have select any dictionary system
    privilege.

    9i version will not connect to 10g as someone noted and appears to require
    select_catalog_role role for 9i and probably 8i.

    Explain plan info:
    Beautification of ugly SQL is a great feature. You can cut and
    paste it out of the new explain plan pane back into the input area.

    Separate statements by a space and you can easily have multiple explain plan
    windows open for comparison with/without a hint or simply change hints in
    one statement. The hints are highlighted in red in the explain output if they
    work, if hint syntax is bad, they do not show up.

    Right mouse click on the table, index, etc. in the Explain Plan window and it gives you some optimizer statistics info if available.

    Report icon in explain plan window generates a beautified, nicely documented HTML
    page after clicking “show report.” You can mail or save to document interesting cases.

    DESCRIBE SQL plus command is supported but most are not.

    Result set of running a statement:
    dates formatted with time by default
    easily sorted by clicking on a column, if many rows or columns, java in PC can run a bit doing the sort.
    report feature available with HTML, CSV, TEXT output.

    Not a SQL*Plus or SQL Worksheet replacement, but can assist greatly.

    Comment by Dave Moore — February 1, 2007 @ 10:05 pm BST Feb 1,2007 | Reply

  9. This graphical view I recently saw on 10G grid. I never had seen it earlier on OEM/EM. I did not see it though on regular EM on 10g. But I like the onw from Quest – when I have trouble understanding a comcplex query I go to Quest(Paid).

    Comment by Shivaswamy Raghunath — February 5, 2007 @ 2:33 am BST Feb 5,2007 | Reply

  10. [...] plans — Jonathan Lewis @ 9:04 pm UTC Apr 13,2007 A little while ago, in a blog about the order of operations in an execution plan, the follow-up discussion about graphical execution plans included a comment [...]

    Pingback by Graphic Plans « Oracle Scratchpad — April 13, 2007 @ 9:08 pm BST Apr 13,2007 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,876 other followers