DBD-Oracle

 view release on metacpan or  search on metacpan

examples/ora_explain.pl  view on Meta::CPAN

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);

examples/ora_explain.pl  view on Meta::CPAN

   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});

examples/ora_explain.pl  view on Meta::CPAN

   {
   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(

examples/ora_explain.pl  view on Meta::CPAN

$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();

examples/ora_explain.pl  view on Meta::CPAN

$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");

examples/ora_explain.pl  view on Meta::CPAN

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