 |
|
EnterpriseDB: Visual Explain
Oracle Tips by
Burleson
|
The
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.
The
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.
In the
example below (Figure 5.50), I am selecting two columns from two
tables and joining them on a declared foreign key relationship:
Figure
5.50: Visual Explain
The
Visual Explain tool is broken into four main areas:
menu/toolbar, explain, statistics and query. The menu/toolbar
area works as you would expect.
You can
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.
You can
stop a running explain by pressing the red X.
The next
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.
The
following two icons and the numeric drop down zoom in and out for you.
The
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.
The
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.
To run
an explain plan on a query, enter your query at the bottom of the
window in the query pane.
SQL
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
execute.
You can
use the Visual Explain tool with EnterpriseDB Advanced Server and with
PostgreSQL. You cannot use it with Oracle.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
|
