EnterpriseDB: Visual Explain
Oracle Tips by
Visual Explain tool is an awesome addition to your programming
arsenal. One of the most common problems I see in applications
is bad SQL. The Visual Explain tool graphically displays how
your program is accessing your database.
basis for this is a tool built into most databases called "explain
plan". Any relational database worth using will have some kind
of explain tool but most are purely character-based listings.
example below (Figure 5.50), I am selecting two columns from two
tables and joining them on a declared foreign key relationship:
5.50: Visual Explain
Visual Explain tool is broken into four main areas:
menu/toolbar, explain, statistics and query. The menu/toolbar
area works as you would expect.
choose to run an explain by pressing the first icon on the toolbar
(the arrow) or you can select Explain from the Statement menu option.
If you choose Explain Analyze instead of Explain, you will receive
additional information in the statistics pane.
stop a running explain by pressing the red X.
four icons change the way the graphic is displayed. Instead of
the default Top-down display, you can choose bottom-up, left-right or
right-left. This doesn't change the information displayed.
It is solely for your preference in displaying the data.
following two icons and the numeric drop down zoom in and out for you.
explain area is the graphical representation of your query. In
the example above, the query scans on the primary key, sorts the
results of both tables and then merges the result set together.
statistics area provides information about the executing query.
The query above is a Merge-Join query and the two tables are being
merged on the deptno column.
an explain plan on a query, enter your query at the bottom of the
window in the query pane.
query tuning is a huge topic and there are many books available that
concentrate on that topic. Therefore, tuning is beyond the scope
of this book. The Visual Explain plan tool that is provided by
EnterpriseDB will make your tuning tasks easier to learn and easier to
use the Visual Explain tool with EnterpriseDB Advanced Server and with
PostgreSQL. You cannot use it with Oracle.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.