NAME
explain, ora_explain - Visualise Oracle query plans
SYNOPSIS
$ explain [ [ user/password@database ] sql script ]
$ ora_explain [ [ user/password@database ] sql script ]Note: When bundled with DBD::Oracle, the script is called ora_explain
DESCRIPTION
Explain is a GUI-based tool that enables easier visualisation of Oracle Query plans. A query plan is the access path that Oracle will use to satisfy a SQL query. The Oracle query optimiser is responsible for deciding on the optimal path to use. Needless to say, understanding such plans requires a fairly sophisticated knowledge of Oracle architecture and internals.
Explain allows a user to interactively edit a SQL statemant and view the resulting query plan with the click of a single button. The effects of modifying the SQL or of adding hints can be rapidly established.
Explain allows the user to capture all the SQL currently cached by Oracle. The SQL capture can be filtered and sorted by different criterea, e.g. all SQL matching a pattern, order by number of executions etc.
Explain is written using Perl, DBI/DBD::Oracle and Tk.
PREREQUISITES
Oracle 7 or Oracle 8, with SQL*Net if appropriate
Perl 5.004_04 or later
DBI version 1.02 or later
DBD::Oracle 0.54 or later
Tk 800.011 or later
Tk::Pod 3.15 or later
Items 2 through 6 can be obtained from any CPAN mirror.
INSTALLATION
Check you have all the prequisites installed and working.
Run 'perl Makefile.PL; make instal1'
Make sure you have run the script $ORACLE_HOME/rdbms/admin/utlxplan.sql from a SQL*Plus session. This script creates the PLAN_TABLE that is used by Oracle when explaining query plans.
HOW TO USE
Type "explain" or "ora_explain" at the shell prompt. A window will appear with a menu bar and three frames, labelled "Query Plan", "Query Step Details" and "SQL Editor". At the bottom of the window are three buttons labelled "Explain", "Clear" and "SQL Cache". A login dialog will also appear, into which you should enter the database username, password and database instance name (SID). The parameters you enter are passed to the DBI->connect() method, so if you have any problems refer to the DBI and DBD::Oracle documentation.
Optionally you may supply up to two command-line arguments. If the first argument is of the form username/password@database, explain will use this to log in to Oracle, otherwise if it is a filename it will be loaded into the SQL editor. If two arguments are supplied, the second one will be assumed to be a filename.
Examples:
explain scott/tiger@DEMO query.sql
explain / query.sql
explain query.sqlExplain functionality
The menu bar has two pulldown menus, "File" and "Help". "File" allows you to login to Oracle, Change the current schema, Capture the contents of the Oracle SQL cache, Load SQL from files, Save SQL to files and to Exit the program. "Help" allows you to view release information and read this documentation.
The "SQL Editor" frame allows the editing of a SQL statement. This should be just a single statement - multiple statements are not allowed. Refer to the documentation for the Tk text widget for a description of the editing keys available. Text may be loaded and saved by using the "File" pulldown menu.
Once you have entered a SQL statement, the "Explain" button at the bottom of the window will generate the query plan for the statement. A tree representation of the plan will appear in the "Query Plan" frame. Individual "legs" of the plan may be expanded and collapsed by clicking on the "+' and "-" boxes on the plan tree. The tree is drawn so that the "innermost" or "first" query steps are indented most deeply. The connecting lines show the "parent-child" relationships between the query steps. For a comprehensive explanation of the meaning of query plans you should refer to the relevant Oracle documentation. The "Clear" button will empty the editor & query plan tree panes.
Single-clicking on a plan step in the Query Plan pane will display more detailed information on that query step in the Query Step Details frame. This information includes Oracle's estimates of cost, cardinality and bytes returned. The exact information displayed depends on the Oracle version. Again, for detailed information on the meaning of these fields, refer to the Oracle documentation.
Double-clicking on a plan step that refers to either a table or an index will pop up a dialog box showing the definition of the table or index in a format similar to that of the SQL*Plus 'desc' command.
The dialog that appears has a button labelled 'Index'. Clicking on this will expand the table dialog to show all the indexes defined on the table. Each column represents an index, and the figures define the order that the table columns appears in the index. To find out the name of an index, position the mouse over the index column. A single click will display the definition of the index in a seperate dialog.
Right-clicking on a plan step that refers to a table will pop up a menu showing a list of the indexes available for the table. Selecting an index will display its definition in a dialog box.
Capture SQL Cache functionality
The explain window has an option on the "File" menu labelled "SQL Cache ...", as well as a button with the same function. Selecting this will popup a new top-level window containing a menu bar and three frames, labelled "SQL Cache", "SQL Statement Statistics" and "SQL Selection Criterea". At the bottom of the window are three buttons labelled "Capture SQL", "Explain" and "Close".
The menu bar has two pulldown menus "File" and "Help". "File" allows you to Save the contents of the SQL Cache pane to a file, copy the selected SQL statement to the Explain window and Close the Grab window.
The "SQL Cache" frame shows the statements currently in the Oracle SQL cache. As you move the cursor over this window, each SQL statement will be highlighted with an outline box. Single-clicking on a statement in the SQL Cache pane will highlight the stamement in green and display more detailed information on that statement in the SQL Statement Statistics frame.
If you want to save the entire contents of the SQL Cache pane, you can do this from the "File" menu.
The "SQL Selection Criterea" frame allows you to specify which SQL statements you are interested in, and how you want them sorted. The pattern used to select statements is a normal perl regexp. Once you have defined the selection criterea, clicking the "Capture SQL" button will read all the matching statements from the SQL cache and display them in the top frame.
Double-clicking on a statement in the "SQL Cache" pane, selecting "Explain" from the "File" menu or clicking the "Explain" button will copy the currently highlighted statement in the "SQL Cache" pane to the SQL editor in the Explain window, so that the query plan for the statement can be examined. Note also that the current schema will be changed to that of the user who first executed the captured statement.
SEE ALSO
This tool assumes that you already know how to interpret Oracle query plans. If need an explanation of the information displayed by this tool, you should refer to the appropriate Oracle documentation. Information can be found in the "Concepts" and "Oracle Tuning" manuals - look for "Query plan" and "Explain plan". Two other useful sources of information are:
Oracle Performance Tuning, 2nd ed.
Mark Gurry and Peter Corrigan
O'Reilly & Associates, Inc.
ISBN 1-56592-237-9
Advanced Oracle Tuning and Administration
Eyal Aronoff, Kevin Loney and Noorali Sonawalla
Oracle Press (Osborne)
ISBN 0-07-882241-6SUPPORT
Support questions and suggestions can be directed to Alan.Burlison@uk.sun.com
COPYRIGHT AND DISCLAIMER
Copyright (c) 1999 Alan Burlison
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
This code is provided with no warranty of any kind, and is used entirely at your own risk.
This code was written by the author as a private individual, and is in no way endorsed or warrantied by Sun Microsystems.