view release on metacpan or  search on metacpan

examples/ora_explain.pl  view on Meta::CPAN

   }
elsif ($object_type eq "INDEX")
   {
   disp_index($row->{OBJECT_OWNER}, $row->{OBJECT_NAME});
   }
else
   {
   die("Unknown object type $object_type",
       "for $row->{OBJECT_OWNER}.$row->{OBJECT_NAME}\n");
   }
}

################################################################################
# Display a list of available indexes on a table, and display the selected
# table definition

sub disp_index_popup($)
{
my ($key) = @_;

# Get the plan step & return if it doesn't refer to an object
my $row = $Plan->{key}{$key};
return(1) if (! $row->{OBJECT_NAME});

# Work out the type of the object - table or index
busy(1);
my $qry = $Db->prepare(qq(
   $SqlMarker select object_type from all_objects
   where object_name = :1 and owner = :2
));
$qry->execute($row->{OBJECT_NAME}, $row->{OBJECT_OWNER})
   || die("Object type:\n$DBI::errstr\n");
my ($object_type) = $qry->fetchrow_array();
$qry->finish();
if ($object_type ne "TABLE")
   {
   busy(0);
   return(1);
   }

# Build the popup menu
$qry = $Db->prepare(qq(
   $SqlMarker select owner, index_name from all_indexes
   where table_name = :1 and table_owner = :2
));
$qry->execute($row->{OBJECT_NAME}, $row->{OBJECT_OWNER})
   || die("Table indexes:\n$DBI::errstr\n");
my $menu = $PlanMain->Menu(-tearoff => 0, -disabledforeground => "#000000");
$menu->command(-label => "Indexes", -state => "disabled");
               
$menu->separator();
my $count = 0;
while ((my ($index_owner, $index_name) = $qry->fetchrow_array()))
   {
   $menu->command(-label => "$index_owner.$index_name",
                  -command => [ \&disp_index, $index_owner, $index_name ]);
   $count++;
   }
$qry->finish();
busy(0);
$menu->Popup(-popover => "cursor", -popanchor => "nw") if ($count);
return(1);
}

################################################################################
# Produce the query plan for the SQL in $PlanSql and store it in $Plan

sub _explain()
{
# Check there is some SQL
my $stmt = $PlanSql->get("1.0", "end");
$stmt =~ s/;//g;
die("You have not supplied any SQL\n") if ($stmt =~ /^\s*$/);

# Check we are logged on
die("You are not logged on to Oracle\n") if (! $Db);

# Set up the various query strings
# Note that for some reason you can't use bind variables in 'explain plan'
my $prefix = "explain plan set statement_id = '$$' for\n";
my $plan_sql = qq(
   $SqlMarker select level, operation, options, object_node, object_owner,
      object_name, object_instance, object_type, id, parent_id, position,
      other);
if ($OracleVersion ge "7.3")
   { $plan_sql .= qq(, cost, cardinality, bytes, other_tag) };
if ($OracleVersion ge "8")
   { $plan_sql .= qq(, partition_start, partition_stop, partition_id) };
$plan_sql .= qq(
  from plan_table
  where statement_id = :1
  connect by prior id = parent_id and statement_id = :1
  start with id = 0 and statement_id = :1
);

# Clean any old stuff from the plan_table
busy(1);
$Db->do(qq($SqlMarker delete from plan_table where statement_id = :1),
        undef, $$)
   || die("Delete from plan_table:\n$DBI::errstr\n");
$Db->commit();

# Switch schema if required
if ($Schema ne $User)
   {
   $Db->do(qq($SqlMarker alter session set current_schema = $Schema))
      || die("Cannot change schema to $Schema:\n$DBI::errstr\n");
   }

# Explain the plan - need to save message if failed!
$Plan = { schema => $Schema, sql => $stmt };
my $fail;
$fail = $DBI::errstr if (!$Db->do($prefix . $stmt));

# Switch back schema if required
if ($Schema ne $User)
   {
   $Db->do(qq($SqlMarker alter session set current_schema = $User))
      || die("Set current schema to $User:\n$DBI::errstr\n");
   }
# Now we can safely die if the exmplai  plan failed

examples/ora_explain.pl  view on Meta::CPAN

   explain query.sql

=head2 Explain 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 separate 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.

=head2 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 Criteria".  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 statement 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 Criteria" 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
criteria, 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.

=head1 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-6

=head1 SUPPORT

Support questions and suggestions can be directed to Alan.Burlison@uk.sun.com

=head1 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.

=cut