Explain Plan enhancements in SQL Developer 1.5

April 16, 2008


Prior to version 1.5 Explain Plan and Autotrace followed different code paths. This manifested itself in many ways. The most obvious distinction was that the tree nodes were sugarcoated with different set of icons. Then, the predicates were shown differently.

Explain plan implementation change in the Early Access release 3 (EA3) went not completely unnoticed. Many users complained that the explain plan functionality regressed. Most of the problems were fixed for production, although couple of final amendments may still be delayed to the patch release. This article describes the new explain plan.

There are many columns in the PLAN_TABLE competing for the window real estate. Which columns are displayed is controlled from the preferences page:

The check boxes in the autotrace and explain plan columns are self describing. You select one and the corresponding table column in the SQL worksheet autotrace or explain plan tab would appear. There is minimal post processing (if any at all). Few exceptions are

  • The OPERATION column, which is shown unconditionally. It is amended with a tooltip that identifies the query block.
  • FILTER_PREDICATES and ACCESS_PREDICATES
  • PROJECTION
  • OTHER_XML column
  • Partition columns are displayed if they are not empty.
  • Here is explain plan for a pretty complex view ALL_OBJECTS:

    You may wonder why parse the predicates and to show them as nodes in the parse tree, especially that they take quite a lot of space for complex queries. Well, the reason is the same why the views are expanded into the defining queries, so that view nodes become branches in the explain plan tree. From theoretical perspective, a view is a relational algebra expression over base relations, while complex predicate is a boolean algebra expression over primitive predicates. Please refer to that paper for more detailed exposition.

    Although predicates are important, the complaint about them consuming a lot of space is valid as well. Tree structure, no matter how deep it is, can be trimmed to a manageable size. In our case all the predicate branches can be collapsed en masse with little context menu assistance:

    For collapsed predicate nodes a tooltip displays the predicate information like this

    For complex predicate a user can expand the tree node

    Those users who are still unimpressed by the feature, can simply disable the predicate option as shown at the very first preferences screenshot for autotrace. The display looks pretty much like the legacy explain plan:

    Advertisements

    3 Responses to “Explain Plan enhancements in SQL Developer 1.5”

    1. Umesh Agarwal Says:

      Hi,

      Can we store this explain plan with the same indentation?
      If yes, what is the procedure for it?

      Thanks,
      Umesh


    2. Nope. I think export in html format makes sense. I suggest filing feature request:
      http://apex.oracle.com/pls/otn/f?p=42626:37

    3. Alok Says:

      In 1.5.1 I am not able to save the explain plan in any format at all. The only context menu items are expand and collapse predicates.


    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

    %d bloggers like this: