DBD-Oracle
view release on metacpan or search on metacpan
examples/ora_explain.pl view on Meta::CPAN
{
# Do the header label
$index_fr->Label(-text => "Index\norder", -relief => "ridge",
-borderwidth => 1)
->grid(-column => 0, -row => 0, -sticky => "we", -ipadx => 3,
-ipady => 2, -columnspan => scalar(@indexes), -rowspan => 2);
# This will retrieve (table column id, index position) for an index
$qry = $Db->prepare(qq(
$SqlMarker select atc.column_id, aic.column_position
from all_tab_columns atc, all_ind_columns aic
where aic.index_owner = :1 and aic.index_name = :2
and atc.owner = aic.table_owner and atc.table_name = aic.table_name
and atc.column_name = aic.column_name
order by aic.index_name, atc.column_id
));
# For each index, add a label describing the index
my $cb = sub { disp_index($_[1], $_[2]); };
my $grid_col = 0;
foreach my $index (@indexes)
{
($ind_owner, $ind_name) = @{$index}{qw(owner name)};
$qry->execute($ind_owner, $ind_name)
|| die("Index columns:\n$DBI::errstr\n");
my $index_txt;
my $col = 1;
while (my ($col_id, $col_pos) = $qry->fetchrow_array())
{
$index_txt .= "\n" x ($col_id - $col) . "$col_pos\n";
$col = $col_id + 1;
}
$index_txt .= "\n" x ($num_cols - ($col - 1));
chop($index_txt);
my $label = $index_fr->Label(-text => $index_txt, -relief => "ridge",
-borderwidth => 1, -justify => "left")
->grid(-column => $grid_col, -row => 2, -sticky => "w",
-ipadx => 3);
$label->bind("<1>", [ $cb, $ind_owner, $ind_name ]);
$Balloon->attach($label, -msg => "$ind_owner.$ind_name",
-balloonposition => "mouse");
$grid_col++;
}
}
}
if ($index_bn->cget(-text) eq "Indexes")
{
$index_bn->configure(-text => "Hide Indexes");
$index_fr->pack(-side => "right", -expand => 1);
}
else
{
$index_bn->configure(-text => "Indexes");
$index_fr->packForget();
}
busy(0);
return(1);
}
################################################################################
# Display a popup dialog showing the structure of a table
sub disp_table($$)
{
my ($owner, $table) = @_;
# Create the dialog for displaying the object details
busy(1);
my $dialog = $PlanMain->Toplevel(-title => "Table");
$dialog->withdraw();
$dialog->resizable(0, 0);
# Create the table definition frame
my $box1 = $dialog->Frame(-borderwidth => 3, -relief => "raised");
my $box2 = $box1->Frame(-borderwidth => 0);
my $table_fr = $box2->Frame(-borderwidth => 1, -relief => "flat");
$table_fr->Label(-text => "$owner.$table",
-relief => "ridge", -borderwidth => 1)
->grid(-column => 0, -row => 0, -columnspan => 2, -sticky => "we");
$table_fr->Label(-text => "Name", -relief => "ridge", -borderwidth => 1)
->grid(-column => 0, -row => 1, -sticky => "we", -ipadx => 3);
$table_fr->Label(-text => "Type", -relief => "ridge", -borderwidth => 1)
->grid(-column => 1, -row => 1, -sticky => "we", -ipadx => 3);
# This will get the table description
my $qry = $Db->prepare(qq(
$SqlMarker select column_name, data_type, data_length,
data_precision, data_scale
from all_tab_columns
where owner = :1 and table_name = :2
order by column_id
));
$qry->execute($owner, $table)
|| die("Table columns:\n$DBI::errstr\n");
my ($num_cols, $name_txt, $type_txt);
while ((my ($name, $type, $length, $precision, $scale)
= $qry->fetchrow_array()))
{
if ($precision)
{
$type .= "($precision";
$type .= ",$scale" if ($scale);
$type .= ")";
}
elsif ($type =~ /CHAR/)
{
$type .= "($length)";
}
$name_txt .= "$name\n";
$type_txt .= "$type\n";
$num_cols++;
}
$qry->finish();
chop($name_txt, $type_txt);
$table_fr->Label(-text => $name_txt, -relief => "ridge", -borderwidth => 1,
-justify => "left")
->grid(-column => 0, -row => 2, -sticky => "we", -ipadx => 3);
$table_fr->Label(-text => $type_txt, -relief => "ridge", -borderwidth => 1,
-justify => "left")
->grid(-column => 1, -row => 2, -sticky => "we", -ipadx => 3);
examples/ora_explain.pl view on Meta::CPAN
$box1 = $dialog->Frame(-borderwidth => 0);
$box1->Button(-text => "Close", -command => sub { $dialog->destroy(); })
->pack(-padx => 6, -side => "left", -expand => 1);
my $index_bn;
$index_bn = $box1->Button(-text => "Indexes")
->pack(-padx => 6, -side => "left", -expand => 1);
$index_bn->configure(-command => sub { disp_table_cb($owner, $table, $num_cols,
$index_fr, $index_bn); });
$box1->pack(-side => "bottom", -pady => 6);
$dialog->Popup();
busy(0);
return(1);
}
################################################################################
# Display the query plan tree
sub disp_plan_tree()
{
$PlanTitle->configure(-text => $Plan->{title});
$PlanTree->delete("all");
my $steps = 0;
foreach my $step (@{$Plan->{id}})
{
my $item = $PlanTree->add($step->{key}, -text => $step->{desc});
$steps++;
}
$PlanTree->autosetmode();
if ($steps)
{
$PlanTree->selectionSet("1");
disp_plan_step("1");
}
}
################################################################################
# Display the statistics for a given plan step
sub disp_plan_step($)
{
my ($key) = @_;
my $row = $Plan->{key}{$key};
$PlanStep->delete("1.0", "end");
my $info = "";
$info .= "Cost:\t\t$row->{COST}\t(Estimate of the cost of this step)\n"
. "Cardinality:\t$row->{CARDINALITY}\t"
. "(Estimated number of rows fetched by this step)\n"
. "Bytes:\t\t$row->{BYTES}\t"
. "(Estimated number of bytes fetched by this step)\n"
if ($row->{COST});
$info .= "\nPartition\nStart:\t$row->{PARTITION_START}\tStop:\t\t"
. "$row->{PARTITION_STOP}\tId:\t\t$row->{PARTITION_ID}\n"
if ($row->{PARTITION_START});
$info .= "\nSQL used by Parallel Query Slave:\n$row->{OTHER}"
if ($row->{OTHER});
$PlanStep->insert("1.0", $info);
}
################################################################################
# Display a popup dialog showing the structure of the table or index used in
# the passed plan step
sub disp_plan_step_obj($)
{
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();
busy(0);
if ($object_type eq "TABLE")
{
disp_table($row->{OBJECT_OWNER}, $row->{OBJECT_NAME});
}
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();
examples/ora_explain.pl view on Meta::CPAN
$Balloon = $PlanMain->Balloon();
### Splash screen
my $splash;
if (@ARGV == 0 || $ARGV[0] ne '-q')
{
about($PlanMain, \$splash);
$splash->after(10000,
sub { if ($splash) { $splash->destroy(); undef($splash); } });
$PlanMain->update();
}
else
{ shift(@ARGV); }
### Menubar
my $menubar = $PlanMain->Frame(-relief => "raised", -borderwidth => 3);
# Create a bold font $ figure out charcter spacing
my $t = $PlanMain->Text();
my $f = $t->cget(-font);
$t->fontCreate("bold", $PlanMain->fontActual($f), -weight => "bold");
$CharWidth = $PlanMain->fontMeasure($f, "X");
undef($f);
$t->destroy();
undef($t);
my $menubar_file = $menubar->Menubutton(-text => "File", -underline => 0);
$menubar_file->command(-label => "Login ...", -underline => 0,
-command => sub { login_dialog($PlanMain); });
$menubar_file->command(-label => "Schema ...", -underline => 2,
-command => sub { schema_dialog($PlanMain); });
$menubar_file->command(-label => "Explain", -underline => 0,
-command => \&explain);
$menubar_file->command(-label => "SQL Cache ...", -underline => 4,
-command => \&grab_main);
$menubar_file->separator();
$menubar_file->command(-label => "Open File ...", -underline => 0,
-command => sub { open_dialog($PlanMain); });
$menubar_file->command(-label => "Save File ...", -underline => 0,
-command => sub { save_dialog($PlanMain, $PlanSql); });
$menubar_file->separator();
$menubar_file->command(-label => "Exit", -underline => 1,
-command => sub { $Db->disconnect() if ($Db); exit(0); });
$menubar_file->pack(-side => "left");
my $menubar_help = $menubar->Menubutton(-text => "Help", -underline => 0);
$menubar_help->command(-label => "About ...", -underline => 0,
-command => sub { about($PlanMain); });
$menubar_help->command(-label => "Usage ...", -underline => 0,
-command => sub { help($PlanMain); });
$menubar_help->pack(-side => "right");
$menubar->pack(-fill => "x");
### Query plan tree
my $frame;
$frame = $PlanMain->Frame(-borderwidth => 3, -relief => "raised");
$PlanTitle = $frame->Label(-text => "Query Plan")->pack(-anchor => "nw");
my $b1_cb = sub
{ error($PlanMain, $@) if (! eval { disp_plan_step_obj($_[0])}); };
my $b3_cb = sub
{ error($PlanMain, $@) if (! eval { disp_index_popup($_[0])}); };
$PlanTree = $frame->Scrolled("B3Tree", -height => 15, -width => 80,
-borderwidth => 0, -highlightthickness => 1,
-scrollbars => "osoe",
-browsecmd => \&disp_plan_step,
-command => $b1_cb, -b3command => $b3_cb)
->pack(-expand => 1, -fill => "both");
$frame->pack(-expand => 1, -fill => "both");
### Query plan statement details
$frame = $PlanMain->Frame(-borderwidth => 3, -relief => "raised");
$frame->Label(-text => "Query Step Details")->pack(-anchor => "nw");
$PlanStep = $frame->Scrolled("ROText", -height => 8, -width => 80,
-borderwidth => 0, -wrap => "none",
-setgrid => "true", -scrollbars => "osoe")
->pack(-fill => "x");
$frame->pack(-fill => "x");
### SQL text editor
$frame = $PlanMain->Frame(-borderwidth => 3, -relief => "raised");
$frame->Label(-text => "SQL Editor")->pack(-anchor => "nw");
$PlanSql = $frame->Scrolled("Text", -setgrid => "true", -scrollbars => "oe",
-borderwidth => 0, -height => 15, -width => 80,
-wrap => "word")
->pack(-expand => 1, -fill => "both");
$frame->pack(-expand => 1, -fill => "both");
### Buttons
$frame = $PlanMain->Frame(-borderwidth => 3, -relief => "raised");
$frame->Button(-text => "Explain", -command => \&explain)
->pack(-side => "left", -expand => 1, -pady => 6);
$frame->Button(-text => "Clear", -command => \&clear_editor)
->pack(-side => "left", -expand => 1, -pady => 6);
$frame->Button(-text => "SQL Cache", -command => \&grab_main)
->pack(-side => "left", -expand => 1, -pady => 6);
$frame->pack(-fill => "x");
### user/pass@db command-line argument processing
$PlanMain->update();
$PlanMain->deiconify();
$splash->raise() if (defined($splash));
if (@ARGV >= 1 && $ARGV[0] =~ /\w*\/\w*(@\w+)?/)
{
my ($username, $password, $database) = split(/[\/@]/, shift(@ARGV));
if (! $username) { $username = "/"; $password = ""; }
if (! $database) { $database = $ENV{TWO_TASK} || $ENV{ORACLE_SID}; }
error($PlanMain, $@) if (! eval { login($database, $username, $password); });
update_title();
}
else
{
login_dialog($PlanMain);
}
### SQL filename argument processing
if (@ARGV >= 1 && -r $ARGV[0])
{
my $file = shift(@ARGV);
if (open_file($file))
{
$FileDir = dirname($file);
examples/ora_explain.pl view on Meta::CPAN
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.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
( run in 2.091 seconds using v1.01-cache-2.11-cpan-364913b4093 )