.\" Automatically generated by Pod::Man 2.16 (Pod::Simple 3.05)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sh \" Subsection heading
.br
.if t .Sp
.ne 5
.PP
\fB\\$1\fR
.PP
..
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.ie \nF \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    nr % 0
.    rr F
.\}
.el \{\
.    de IX
..
.\}
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "ORA_EXPLAIN 1"
.TH ORA_EXPLAIN 1 "2008-11-27" "perl v5.10.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
explain, ora_explain \- Visualise Oracle query plans
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\& $ explain [ [ user/password@database ] sql script ]
\& $ ora_explain [ [ user/password@database ] sql script ]
.Ve
.PP
\&\fBNote:\fR When bundled with DBD::Oracle, the script is called ora_explain
.SH "DESCRIPTION"
.IX Header "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 \s-1SQL\s0
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.
.PP
Explain allows a user to interactively edit a \s-1SQL\s0 statemant and view the
resulting query plan with the click of a single button.  The effects of
modifying the \s-1SQL\s0 or of adding hints can be rapidly established.
.PP
Explain allows the user to capture all the \s-1SQL\s0 currently cached by Oracle.  The
\&\s-1SQL\s0 capture can be filtered and sorted by different criterea, e.g. all \s-1SQL\s0
matching a pattern, order by number of executions etc.
.PP
Explain is written using Perl, DBI/DBD::Oracle and Tk.
.SH "PREREQUISITES"
.IX Header "PREREQUISITES"
.IP "1." 2
Oracle 7 or Oracle 8, with SQL*Net if appropriate
.IP "2." 2
Perl 5.004_04 or later
.IP "3." 2
\&\s-1DBI\s0 version 1.02 or later
.IP "4." 2
DBD::Oracle 0.54 or later
.IP "5." 2
Tk 800.011 or later
.IP "6." 2
Tk::Pod 3.15 or later
.PP
Items 2 through 6 can be obtained from any \s-1CPAN\s0 mirror.
.SH "INSTALLATION"
.IX Header "INSTALLATION"
.IP "1." 2
Check you have all the prequisites installed and working.
.IP "2." 2
Run 'perl Makefile.PL; make instal1'
.IP "3." 2
Make sure you have run the script \f(CW$ORACLE_HOME\fR/rdbms/admin/utlxplan.sql
from a SQL*Plus session.  This script creates the \s-1PLAN_TABLE\s0 that is used
by Oracle when explaining query plans.
.SH "HOW TO USE"
.IX Header "HOW TO USE"
Type \*(L"explain\*(R" or \*(L"ora_explain\*(R" at the shell prompt.  A window will appear with
a menu bar and three frames, labelled \*(L"Query Plan\*(R", \*(L"Query Step Details\*(R" and
\&\*(L"\s-1SQL\s0 Editor\*(R".  At the bottom of the window are three buttons labelled
\&\*(L"Explain\*(R", \*(L"Clear\*(R" and \*(L"\s-1SQL\s0 Cache\*(R".  A login dialog will also appear, into
which you should enter the database username, password and database instance
name (\s-1SID\s0).  The parameters you enter are passed to the \s-1DBI\-\s0>\fIconnect()\fR method,
so if you have any problems refer to the \s-1DBI\s0 and DBD::Oracle documentation.
.PP
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 \s-1SQL\s0
editor.  If two arguments are supplied, the second one will be assumed to be a
filename.
.PP
Examples:
.PP
.Vb 3
\&   explain scott/tiger@DEMO query.sql
\&   explain / query.sql
\&   explain query.sql
.Ve
.Sh "Explain functionality"
.IX Subsection "Explain functionality"
The menu bar has two pulldown menus, \*(L"File\*(R" and \*(L"Help\*(R".  \*(L"File\*(R" allows you to
login to Oracle, Change the current schema, Capture the contents of the Oracle
\&\s-1SQL\s0 cache, Load \s-1SQL\s0 from files, Save \s-1SQL\s0 to files and to Exit the program.
\&\*(L"Help\*(R" allows you to view release information and read this documentation.
.PP
The \*(L"\s-1SQL\s0 Editor\*(R" frame allows the editing of a \s-1SQL\s0 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 \*(L"File\*(R" pulldown menu.
.PP
Once you have entered a \s-1SQL\s0 statement, the \*(L"Explain\*(R" 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 \*(L"Query Plan\*(R" frame.  Individual
\&\*(L"legs\*(R" of the plan may be expanded and collapsed by clicking on the \*(L"+' and \*(R"\-\*(L"
boxes on the plan tree.  The tree is drawn so that the \*(R"innermost\*(L" or \*(R"first\*(L"
query steps are indented most deeply.  The connecting lines show the
\&\*(R"parent-child\*(L" relationships between the query steps.  For a comprehensive
explanation of the meaning of query plans you should refer to the relevant
Oracle documentation.  The \*(R"Clear" button will empty the editor & query plan
tree panes.
.PP
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.
.PP
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.
.PP
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.
.PP
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.
.Sh "Capture \s-1SQL\s0 Cache functionality"
.IX Subsection "Capture SQL Cache functionality"
The explain window has an option on the \*(L"File\*(R" menu labelled \*(L"\s-1SQL\s0 Cache ...\*(R",
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 \*(L"\s-1SQL\s0 Cache\*(R",
\&\*(L"\s-1SQL\s0 Statement Statistics\*(R" and \*(L"\s-1SQL\s0 Selection Criterea\*(R".  At the bottom of the
window are three buttons labelled \*(L"Capture \s-1SQL\s0\*(R", \*(L"Explain\*(R" and \*(L"Close\*(R".
.PP
The menu bar has two pulldown menus \*(L"File\*(R" and \*(L"Help\*(R".  \*(L"File\*(R" allows you to
Save the contents of the \s-1SQL\s0 Cache pane to a file, copy the selected \s-1SQL\s0
statement to the Explain window and Close the Grab window.
.PP
The \*(L"\s-1SQL\s0 Cache\*(R" frame shows the statements currently in the Oracle \s-1SQL\s0 cache.
As you move the cursor over this window, each \s-1SQL\s0 statement will be highlighted
with an outline box.  Single-clicking on a statement in the \s-1SQL\s0 Cache pane will
highlight the stamement in green and display more detailed information on that
statement in the \s-1SQL\s0 Statement Statistics frame.
.PP
If you want to save the entire contents of the \s-1SQL\s0 Cache pane, you can do this
from the \*(L"File\*(R" menu.
.PP
The \*(L"\s-1SQL\s0 Selection Criterea\*(R" frame allows you to specify which \s-1SQL\s0 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 \*(L"Capture \s-1SQL\s0\*(R" button will read all the matching
statements from the \s-1SQL\s0 cache and display them in the top frame.
.PP
Double-clicking on a statement in the \*(L"\s-1SQL\s0 Cache\*(R" pane, selecting \*(L"Explain\*(R"
from the \*(L"File\*(R" menu or clicking the \*(L"Explain\*(R" button will copy the currently
highlighted statement in the \*(L"\s-1SQL\s0 Cache\*(R" pane to the \s-1SQL\s0 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.
.SH "SEE ALSO"
.IX Header "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
\&\*(L"Concepts\*(R" and \*(L"Oracle Tuning\*(R" manuals \- look for \*(L"Query plan\*(R" and \*(L"Explain
plan\*(R".  Two other useful sources of information are:
.PP
.Vb 4
\&   Oracle Performance Tuning, 2nd ed.
\&      Mark Gurry and Peter Corrigan
\&      O\*(AqReilly & 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\-6
.Ve
.SH "SUPPORT"
.IX Header "SUPPORT"
Support questions and suggestions can be directed to Alan.Burlison@uk.sun.com
.SH "COPYRIGHT AND DISCLAIMER"
.IX Header "COPYRIGHT AND DISCLAIMER"
Copyright (c) 1999 Alan Burlison
.PP
You may distribute under the terms of either the \s-1GNU\s0 General Public License
or the Artistic License, as specified in the Perl \s-1README\s0 file, with the
exception that it cannot be placed on a CD-ROM or similar media for commercial
distribution without the prior approval of the author.
.PP
This code is provided with no warranty of any kind, and is used entirely at
your own risk.
.PP
This code was written by the author as a private individual, and is in no way
endorsed or warrantied by Sun Microsystems.