Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter