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
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: