10046.pl

#!/usr/bin/perl
#===============================================================================
#
#         FILE:  10046.pl
#
#        USAGE:  10046.pl -h | [ -e -s sort_key_list -S ] { -t trace_file.trc } 
#
#  DESCRIPTION: In basic mode it provides statistical report of 10046 wait
#               events from raw trace file.
#
#               For pre Oracle 10.2 trace files the breakdown and histogram
#               sections of the report are based on unique P1 and event
#               combinations. For tracefiles produced from 10.2 onwards
#               these sections are reported for each unique combination
#               of obj# and event.
#
#               Flags -e and -p may be used to get cursor level details:
#
#               o Cursor operation statistics
#               o Row source plan details
#               o Cursor level event histograms
#
#               Use 10046.pl -h for full details of command modifiers.
#
#      OPTIONS:  -h -t trace_file.trc
# REQUIREMENTS:  ---
#         BUGS:  ---
#        NOTES:  ---
#       AUTHOR:  Clive Bostock (Oracle ACS)
#      COMPANY: 
my        $ver = '1.6';
#      CREATED:  17/04/2011 11:14:22
#     REVISION:  ---
#                19.05.2011 - Implemented fix by Kyle Hailey for
#                             SQL*Net type events.                         
#                28.05.2011 - Fixed divide by zero bug for calculating pct ela time
#                28.05.2011 - Included cursor level details.
#                26.06.2011 - Extended Kyle Hailey's work on extended histogram
#                             ranges. Implemented with -w (window view) flag. 
#                             Use -h for help on this.                        
#===============================================================================
use Data::Dumper;
use strict;
use warnings;
use File::Basename;
use Getopt::Std;
use Text::Wrap qw(wrap $columns $huge);

$columns = 80;        # Wrap at 80 characters
$huge = 'wrap';


#my $OUTPUT = open('>');
#open OUTPUT, '>', \$variable or die "Can't open STDOUT: $!";
#open OUTPUT, '>', STDOUT or die "Can't open STDOUT: $!";
#my $OUTPUT = STDOUT;
my $aggr_rec;
my $base_version;
my $bucket;
my $count = -1;
my $conn_str = '';
my $curs;
                                       #*************************************/
                                       # Adjust $disp_buckets to alter the  */
                                       # number of display buckets          */
                                       #*************************************/
my $disp_buckets  = 13;
my $ela_ms;
my $elapsed = 0.00;
my $er;
my $event;
my $event_line;
my $event_rec;
my $field_length;
my $fmt_mask;
my $full_version;
my $hi_bucket    = 0;
my $instance_name  = '';
my $inst_name      = '';
my $max_bucket_label = '';
my $min_bucket_label = '';
my $pred = '(';
my $hist_labels  = '';
my $hist_uscore  = '';
my $obj_header   = 'Object Id';
my $object_name;
my $objid;
my $objid_event;
my $output;
my $pre_10_2     = 0;
my $record;
my $ref_buckets;
my $skip_sys = 'N';
my $sort_key;
my $tot_ela      = 0;
                                       #*************************************/
                                       # Add to $total_buckets for each     */
                                       # extra bucket added below.          */
                                       #*************************************/
my $total_buckets = 16;
my $total_elapse = 0;
my $trace_file;
my $window_adj   = 2;
my %curs_handles;
my %cursor;
my %event_aggr;
my %event_records;
my %event_stat;
my %tot_objn_sort;
my %obj_headers    = ();
my %object_ids     = ();
my @objects;
my %objects;
my %tot_sort;
my @cursors;
my @ev_buckets;
my @events;
my @trace_file;
our $gather_cursor_data = 0;
our $opt_c;
our $opt_e;
our $opt_h;
our $opt_o;
our $opt_p;
our $opt_s;
our $opt_S;
our $opt_t;
our $opt_w;
our $prog      = basename($0);
our $sort_list = '';
our @sort_list = ();
our $SD="\/";
our $SP=":";
our $os = $ENV{'OS'};

my @dataset = ();
my $obj_sql =
'select o.object_id
      , o.object_name
      , o.object_type
from  dba_objects o
where o.object_id in ';

my $inst_sql = 'select instance_name from v$instance';
my $tab_sql = '
column partitioned format a11
select o.object_id
     , t.table_name
     , t.tablespace_name
     , t.owner
       , t.last_analyzed
       , t.num_rows
       , t.blocks
       , t.empty_blocks
       , t.avg_row_len
       , t.chain_cnt
       , t.degree
       , t.instances
       , t.partitioned
from dba_objects o,
     dba_tables  t
where o.owner = t.owner
  and o.object_name = t.table_name
  and o.object_type = \'TABLE\'
  and o.object_id in ';

my $idx_sql =
'column partitioned heading "PARTND" format a6
column avg_leaf_blocks_per_key heading "LF_BLKS_KEY"
column avg_data_blocks_per_key heading "DATA_BLKS_KEY"
column clustering_factor heading "CLSTR_FACT"
select o.object_id
     , i.index_name
--   , i.owner
       , i.last_analyzed
     , i.table_name
     , i.table_type
     , i.index_type
     , decode(i.uniqueness,\'UNIQUE\',\'Yes   \',\'No    \') "UNIQUE"
       , i.degree
       , i.blevel
       , i.leaf_blocks
       , i.avg_leaf_blocks_per_key
       , i.avg_data_blocks_per_key
     , i.clustering_factor
       , i.num_rows
       , i.instances as inst
       , i.partitioned
from dba_objects o,
     dba_indexes  i
where o.owner = i.owner
  and o.object_name = i.index_name
  and o.object_type = \'INDEX\'
  and o.object_id in ';
#*****************************************************************************/
# Routine, test_dbconn, to test that a database connection via sqlplus is    */
# possible. Accepts a connect string as an argument.                         */
#*****************************************************************************/
sub test_dbconn
{
  my ($connect) = (@_);
  my $SD="\/";
  my $SP=":";
  my $os = $ENV{'OS'};
  my @sqlplus_output;
  my $sqlplus_output;
  my @ora_errors;
  open (SQL,">","10046_tmp.sql") or die "$prog: [test_dbconn] cannot open for output 10046_tmp.sql file\n";
  print SQL "select * from dual;\n";
  print SQL "\n";
  print SQL "\n";
  print SQL "\n";
  print SQL "\n";
  print SQL "\n";
  print SQL "\n";
  print SQL "exit;";
  close (SQL);
  if  ($os  !~ /Win/i)
  {
    @sqlplus_output = qx(sqlplus -s $connect \@10046_tmp.sql);
  }
  else
  {
    @sqlplus_output = qx(sqlplus -s $connect <10046_tmp.sql);
  }

  chomp @sqlplus_output;
  @ora_errors = grep(/^ORA-/, @sqlplus_output);
  my @usage_error = ();
  my $uerror_count=@usage_error = grep(/Usage/, @sqlplus_output);
  my %seen = ();
  my $error_count = 0;
  for my $error (@ora_errors)
  {
     unless($seen{$error})
     {
       $seen{$error} = 1;
       $error_count++;
       printf "$error\n";
     } 
  }
  if ( $error_count)
  {
     exit 1;
  }
  if ( $uerror_count )
  {
     print STDERR "Probable connect string error. E.g. '/ AS SYSBA'\n";
     exit 2;
  }
  close(SQL);
}
#******************************************************************************/
#* Function which returns a set of histogram buckets. We set up fourteen      */
#* bucket slots but we can only display 11. Use -b option to decide which     */
#* way to slide the bucket views. The function returns a new, unsullied       */
#* structure.                                                                 */
#******************************************************************************/
sub build_hist_buckets
{
  my @buckets;
  my $hist_rec;
  my ($window_adj)  = @_;
  my ($shift_buckets, $pop_buckets, $pop_count, $shift_count);

  # $disp_buckets
  # $total_buckets
  # $shift_buckets = 2 + $window_adj;
  # $pop_buckets   = 1 - $window_adj;
  $shift_buckets = $window_adj;
  $pop_buckets   = $total_buckets - $disp_buckets - $window_adj;

  $hist_rec = {
                  LOW_VAL => 0.0
                , HI_VAL  => 0.05
                , LABEL   => '50us'
                , COUNT   => 0
              };
  $buckets[0] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 0.05
                , HI_VAL  => 0.1
                , LABEL   => '100us'
                , COUNT   => 0
              };
  $buckets[1] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 0.1
                , HI_VAL  => 0.2
                , LABEL   => '200us'
                , COUNT   => 0
              };
  $buckets[2] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 0.2
                , HI_VAL  => 0.5
                , LABEL   => '500us'
                , COUNT   => 0
              };
  $buckets[3] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 0.5
                , HI_VAL  => 1
                , LABEL   => '1ms'
                , COUNT   => 0
              };
  $buckets[4] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 1
                , HI_VAL  => 2
                , LABEL   => '2ms'
                , COUNT   => 0
              };
  $buckets[5] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 2
                , HI_VAL  => 4
                , LABEL   => '4ms'
                , COUNT   => 0
              };
  $buckets[6] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 4
                , HI_VAL  => 8
                , LABEL   => '8ms'
                , COUNT   => 0
              };
  $buckets[7] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 8
                , HI_VAL  => 16
                , LABEL   => '16ms'
                , COUNT   => 0
              };
  $buckets[8] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 16
                , HI_VAL  => 32
                , LABEL   => '32ms'
                , COUNT   => 0
              };
  $buckets[9] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 32
                , HI_VAL  => 64
                , LABEL   => '64ms'
                , COUNT   => 0
              };
  $buckets[10] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 64
                , HI_VAL  => 128
                , LABEL   => '128ms'
                , COUNT   => 0
              };
  $buckets[11] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 128
                , HI_VAL  => 256
                , LABEL   => '256ms'
                , COUNT   => 0
              };
  $buckets[12] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 256
                , HI_VAL  => 512
                , LABEL   => '512ms'
                , COUNT   => 0
              };
  $buckets[13] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 512
                , HI_VAL  => 1024
                , LABEL   => '1024ms'
                , COUNT   => 0
              };
  $buckets[14] = $hist_rec;
  $hist_rec = {
                  LOW_VAL => 1024
                , HI_VAL  => 2048
                , LABEL   => '2048ms'
                , COUNT   => 0
              };
  $buckets[15] = $hist_rec;

  $max_bucket_label = $buckets[$#buckets]->{LABEL};
  $min_bucket_label = $buckets[0]->{LABEL};

  for ($shift_count = 0; $shift_count < $shift_buckets; $shift_count++)
      { shift @buckets }
  for ($pop_count = 0; $pop_count < $pop_buckets; $pop_count++)
        { pop @buckets }
  $buckets[0]->{LOW_VAL} = 0;
  $hi_bucket = $#buckets;
  return @buckets;
}
                                       #*************************************/
                                       # We make a call here for            */
                                       # referencesm made when processing   */
                                       # $opt_h.                            */
                                       #*************************************/
my @hist_headers = build_hist_buckets($window_adj);
$count = -1;
foreach $bucket (@hist_headers)
{
   $count = $count + 1;
   if ($count < $hi_bucket)
   {
       $hist_labels = $hist_labels . sprintf ("%8s", ' <' . $bucket->{LABEL});
   }
   else
   {
       $hist_labels = $hist_labels . sprintf ("%8s", ' >=' . $bucket->{LABEL});
   }
}

my $orphan_handle = 0;
my %orphan_handles = ();

my $valid_sort_params ='';
my %valid_sort_params =
      (
        UID     => 'Userid of user that parsed the cursor'
      , HV      => 'The Hash Value of the SQL statements'
      , PRSCPU  => 'Cpu time parsing'
      , PRSCNT  => 'Number of times parse was called'
      , PRSDSK  => 'Number of disk IOs during parse'
      , PRSELA  => 'Elapsed time parsing'
      , PRSQRY  => 'Number of buffers for consistent read during parse'
      , PRSCU   => 'Number of buffers for current read during parse'
      , PRSMIS  => 'Number of misses in library cache during parse'
      , EXECNT  => 'Number of execute was called'
      , EXEDSK  => 'Number of disk IOs during execute'
      , EXECPU  => 'CPU time spent executing'
      , EXEELA  => 'Elapsed time executing'
      , EXEQRY  => 'Number of buffers for consistent read during execute'
      , EXECU   => 'Number of buffers for current read during execute'
      , EXEROW  => 'Number of rows processed during execute'
      , EXEMIS  => 'Number of library cache misses during execute'
      , FCHCNT  => 'Number of times fetch was called'
      , FCHDSK  => 'Number of disk IOs during fetch'
      , FCHCPU  => 'CPU time spent fetching'
      , FCHELA  => 'Elapsed time fetching'
      , FCHQRY  => 'Number of buffers for consistent read during fetch'
      , FCHCU   => 'Number of buffers for current read during fetch'
      , FCHROW  => 'Number of rows fetched'
      );
my $io_event;
my @io_events = (
                  'db file scattered read'
                , 'db file sequential read'
                , 'db file single write'    
                , 'direct path read'    
                , 'direct path read temp'    
                , 'direct path write'    
                , 'direct path write temp'    
                );


#******************************************************************************/
#* Perl trim function to remove whitespace from the start and end of the      */
#* string                                                                     */
#******************************************************************************/
sub trim($)
{
    my $string = shift;
    $string =~ s/^\s+//;
    $string =~ s/\s+$//;
    return $string;
}
#******************************************************************************/
#* Sub routine which accepts a SQL statement, a connect sting and a pointer   */
#* to an array. The sub routine executes the SQL and loads the referenced     */
#* array with the result set.                                                 */
#******************************************************************************/
sub exec_sqlplus_sql
{
  my ($sql, $connect, $dataset) = @_;

  my @sqlplus_output;
 
  open (SQL,">","10046_tmp.sql") or die "cannot open for output 10046_tmp.sql file\n";
  print SQL "whenever sqlerror exit sqlcode\n";
  print SQL "set feedback off\n";
  print SQL "set pagesize 50000\n";
  print SQL "set numwidth 16\n";
  print SQL "set linesize 5000\n";
  print SQL "set colsep |\n";
  print SQL "alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI';\n";
  print SQL "$sql\n";
  print SQL "/\n";
  print SQL "exit;\n";
  close (SQL);

  my $sqlplus_output = qx(sqlplus -s $connect \@10046_tmp.sql);

  @sqlplus_output = split(/\n/,$sqlplus_output);
  my @ora_errors = ();
  my $error_count=@ora_errors = grep(/ORA-/, @sqlplus_output);
  if ( $error_count)
    {
     print $sqlplus_output;
     print "Error detected attempting to run sqlplus script (10046_tmp.sql)\n";
     die "$prog: Deploying chute and bailing out!\n\n"; }

  chomp $sqlplus_output;
  @$dataset = @sqlplus_output;
}
#******************************************************************************/
#* The read_dataset function is here for possible future use. It allows the  */
#* spooled results (file specified by 1st parameter) from a sqlpus query to   */
#* be read. The results are passed back to the array referenced by the        */
#* second parameter.                                                          */
#******************************************************************************/
sub read_dataset
{
  my ($dataset_file, $dataset) = (@_);
  my %dataset;
  my $field_pos = 0;
   
  open (DSET, $dataset_file) || die "Failed to open data set file ($dataset_file)\n";
  my @dataset = <DSET>;
  close (DSET);
  @$dataset = @dataset;
}
#******************************************************************************/
#* This function takes a "raw" dataset returned from a sqlplus session. The   */
#* dataset, which must be presented as an array, is assumed to have the       */
#* column headers on the second line. The prep_dataset function prepares    */
#* the dataset by removing blank lines and loads the results into 2 main      */
#* hash structures. The first hash describes the header columns along with    */
#* the header field sizes. The second hash structure consists of a record     */
#* for each data set line from the sqlplus query. These records in turn are   */
#* broken down into a hash for each field (column value returned). A field    */
#* has attributes: FIELD_POS, FIELD_NAME and FIELD_VALUE. Subroutines are     */
#* provided to take the dataset hashes and print out the contents in          */
#* tablular format. A further subroutine is provided to return one of the     */
#* field attributes based on the record id and FIELD_NAME. The record id is   */
#* based on the 1st column of the dataset, which must be a unique identifier  */
#* for each record.                                                           */
#******************************************************************************/
sub prep_dataset
{
  my ($dataset_in, $field_headers, $dataset_out) = (@_);
  my @temp_array  =();
  my @temp_array2 =();
  my $record;
  my $field;
  my %headers;
  my $headers;
  my %dataset;
  my @headers;
  my @record;
  my $field_pos = 0;
  my @dataset  = @$dataset_in;

  chomp @dataset;
  shift @dataset;

  $headers  = $dataset[0];
  shift @dataset;
  @headers = split(/[|]/,$headers);

  $field_pos = 0;
  my $field_len;

  foreach $field (@headers)
  {
      $field = trim($field);
      $field_pos++;
      $field_len = length($field);
      my $field_rec = {
                        FIELD_POS    => $field_pos
                      , FIELD_NAME   => $field
                      , FIELD_LENGTH => $field_len
                      };
      $headers{$field_pos} = $field_rec;
  }
                                       #**************************************/
                                       #* Clean up dataset                   */
                                       #**************************************/
  foreach $record (@dataset)
  {
    if (! length($record) or $record =~ m/^-/)
    {
        next;
    }

    @record = split(/[|]/,$record);
    $field_pos = 0;
    my %rec;

    foreach $field (@record)
    {
        $field_pos++;
        $field = trim($field);
        $field_len = length($field);
                                       #*************************************/
                                       # Ensure we cater for maximum        */
                                       # possible field width. This may be  */
                                       # updated later if field values      */
                                       # exist which are longer than the    */
                                       # field header label.                */
                                       #*************************************/
        if ($field_len > $headers{$field_pos}->{FIELD_LENGTH})
        {
          $headers{$field_pos}->{FIELD_LENGTH} = $field_len;
        }
        my $fld_ptr = {
                        FIELD_POS    => $field_pos
                      , FIELD_LENGTH => 0
                      , FIELD_VALUE  => $field
                      };
        $rec{$headers{$field_pos}->{FIELD_NAME}} = $fld_ptr;
    }
                                       #**************************************/
                                       #*  We assume the first field value   */
                                       #* is the hash key.                   */
                                       #**************************************/

    $dataset{$rec{$headers{1}->{FIELD_NAME}}->{FIELD_VALUE}} = \%rec;
  }
  my $fld_name;
  my $fld_pos;
                                       #**************************************/
                                       #* This next bit re-scans our fields  */
                                       #* and where required updates the     */
                                       #* Field length figure. This is a     */
                                       #* bit of de-normalisation so as to   */
                                       #* save more involved coding when we  */
                                       #* come to print out details. We      */
                                       #* need the FIELD_LENGTH to be set    */
                                       #* to the max required column width   */
                                       #* for the field.                     */
                                       #**************************************/
  my $rec_id;
  foreach $rec_id (keys %dataset)
  {
      my $record = $dataset{$rec_id};

      my %record = %$record;
      foreach $fld_name (keys %record)
      {
          $fld_pos = $record{$fld_name}->{FIELD_POS};
          if ($record{$fld_name}->{FIELD_LENGTH} <  $headers{$fld_pos}->{FIELD_LENGTH})
          {
            $record{$fld_name}->{FIELD_LENGTH} =  $headers{$fld_pos}->{FIELD_LENGTH};
          }
      }
  }
  %$field_headers  = %headers;
  %$dataset_out    = %dataset
}
#******************************************************************************/
#* The print_dataset function takes the hash outputs (headers and dataset)    */
#* as supplied by the prep_dataset function and prints the contents in        */
#* tabular format, complete with column headers.                             */
#******************************************************************************/
sub print_dataset
{
  my ($field_headers, $dataset) = (@_);

  my %headers       = %$field_headers;
  my %dataset       = %$dataset;
  my $field;
  my $field_name;
  my $field_length;
  my $fmt_mask;


  foreach $field (sort {$headers{$a}->{FIELD_POS} <=> $headers{$b}->{FIELD_POS}} keys %headers)
  {
      $field_name   = $headers{$field}->{FIELD_NAME};
      $field_length = $headers{$field}->{FIELD_LENGTH};
      $fmt_mask  = '%-' . $field_length . 's ';
      printf OUTPUT ($fmt_mask, $field_name);
  }


  print OUTPUT "\n";
  foreach $field (sort {$headers{$a}->{FIELD_POS} <=> $headers{$b}->{FIELD_POS}} keys %headers)
  {
      printf OUTPUT ("%s ", '~' x $headers{$field}->{FIELD_LENGTH});
  }
  print OUTPUT "\n";
 
  my $rec_id;
  my $fld_name;
  my $field_value;
  # my $fmt_mask;
  foreach $rec_id (keys %dataset)
    {
        my $record = $dataset{$rec_id};
        my %record = %$record;
        foreach $fld_name (sort { $record{$a}->{FIELD_POS} <=> $record{$b}->{FIELD_POS} } keys %record)
        {
          $field_value  = $record{$fld_name}->{FIELD_VALUE};
          $field_length = $record{$fld_name}->{FIELD_LENGTH};
          $fmt_mask  = '%-' . $field_length . 's ';
          printf OUTPUT ($fmt_mask, $field_value) ;
        }
        print OUTPUT "\n";
    }
}


#my $dataset_file = 'plusres.lst';
my %headers;
my %dataset;

#******************************************************************************/
#* The dataset_attr subroutine acts as a function. Based on the record id     */
#* (1st column value of a dataset) and a field name (as per the column        */
#* headers), it returns a specified attribute,   which may be one of:        */
#* FIELD_LENGTH, FIELD_VALUE or FIELD_POS.                                    */
#******************************************************************************/
sub dataset_attr
{
  my ($rec_id, $fld_name, $attr_name, $dataset) = (@_);

  if ($attr_name ne 'FIELD_LENGTH' and $attr_name ne 'FIELD_VALUE' and $attr_name ne 'FIELD_POS')
  {
      print STDERR "$prog: Invalid argument (\$attr_name: $attr_name) passed to function dataset_attr\n";
      print STDERR "$prog: Valid values: FIELD_LENGTH, FIELD_VALUE or FIELD_POS\n";
      print STDERR "$prog: Deploying chute and bailing out!\n";
      exit (1);
  }
  my %record;
  my $field_name;
  my $field_length;
  my $fmt_mask;
#  print Dumper ($dataset);
  my %dset = %$dataset;
# print Dump ($dataset);
  my $record;
  if ($record = $dset{$rec_id})
  {
    %record = %$record;
    my $attr   = $record{$fld_name}->{$attr_name};
    $field_length = $record{$fld_name}->{FIELD_LENGTH};
    $fmt_mask  = '%-' . $field_length . 's';
    $attr = sprintf ($fmt_mask, $attr) ;
    return $attr;
  }
  else
  {
      # Invalid entry.
      return 0;
  }
}

#******************************************************************************/
#* Function: ltrim to trim white space from left of a string                  */
#******************************************************************************/
sub ltrim($)
{
    my $string = shift;
    $string =~ s/^\s+//;
    return $string;
}

if ( ! $os )
  { $os = $ENV{'OSTYPE'}; }
if ( ! $os )
  {
     $os = `uname`;
     chomp $os; 
  }
if ( ! $os )
  {
     $os = 'Unknown';
  }

$SD="\\" if($os  =~ /Win/i);
$SP=";" if($os   =~ /Win/i);

getopts('c:heo:pt:s:Sw:') or
    die "\nInvalid options specified, use $prog -h.\n$prog: Deploying chute and bailing out!!!\n";

if (defined($opt_h))
{
    print "\nUsage:  $prog -h | [ -e -p -s sort_key_list -S -w count ]\n";
    print "                     { -t trace_file.trc -o output_file}\n\n";
    print " -c conn_str : Connect to database with specified connect string.n";
    print " -e          : Include cursor events and event histograms\n";
    print " -p          : Include row source plans and statistics\n";
    print " -s          : List of sort keys\n";
    print " -S          : Suppress reporting of SYS executed SQL\n";
    print " -w count    : Adjusts the histogram display (see below).\n\n";
    print "By default $prog reports overall wait event statistic.\n\n";
    print "The -c conn_str option requires that you are connecting to the \n";
    print "database from which the trace was generated. It is used to\n";
    print "translate Object Ids into object names. The connect string should be \n";
    print "for a DBA logon. This option is ignored for pre-10.2 trace files.\n";
    print "This option uses sqlplus to make a connection and also requires\n";
    print "that your operating system environment is conditioned for connection\n";
    print "to the Oracle database from which the trace file was generated.\n\n";
    print "The -e and -p flags are used to request extra \n";
    print "details relating to cursor operations.\n\n";
    print "\nValid sort keys are:\n\n";
    foreach $sort_key (sort keys %valid_sort_params)
    {
        printf ("%-6s : %s\n",$sort_key, $valid_sort_params{$sort_key});
    }
    print "\nSort keys, which are case insensitive, can be combined by supplying a list\n";
    print "of one or more keys separated, by comma characters. $prog sorts by the sum\n";
    print "of the specified keys. The sort operation only affects the order of the \n";
    print "Cursor sections of the report. These are reported in descending order of \n";
    print "the sum of the sort keys. The HV and UID sort keys can only be specified\n";
    print "in isolation. The Default sort order is based on PRSELA, EXEELA and FCHELA.\n\n";
    print "\nExample:\n\n";
    print "   $prog -t my_trace_file.trc -o report.txt -p -e -s prscpu,execpu,fchcpu\n\n";
    print "$prog reads my_trace_file.trc and writes the report to report.txt\n";
    print "We are also requesting cursor level event details (-e) to be reported\n";
    print "as well as cursor operation statistics and row source plans (-p) for the\n";
    print "cursors. The cursor details section is sorted by descending sum of:\n\n";
    print "- Parse CPU Time.\n";
    print "- Execution CPU Time.\n";
    print "- Fetch CPU Time.\n\n";
    print "The defaulted report histogram buckets range $hist_headers[0]->{LABEL} to $hist_headers[$hi_bucket]->{LABEL},\n";
    print "but you can use the -w option to slide the window so that the range reported \n";
    print "is shifted by count buckets.";
    print "To start with the lowest bucket ($min_bucket_label) use -w 0.\n";
    print "To slide the window view to the right, increase the value of count.\n";
    print "The default value for count is $window_adj which means that the low end of the \n";
    printf ("reported bucket range is $hist_headers[0]->{LABEL}. The count value can range from 1 to %s.\n\n", $total_buckets - $disp_buckets);
    print "Done.\n";
   
    exit;
}

if (defined($opt_w))
{
    $window_adj = $opt_w;
    if ( $window_adj > ($total_buckets - $disp_buckets))
    {
        printf ("$prog: Valid values to specify with -w are integers from 0 through to %s.\n", $total_buckets - $disp_buckets);
        print "$prog: Deploying chute and bailing out!\n";
        exit (1);
    }
    $hist_labels = '';
    @hist_headers = build_hist_buckets($window_adj);
    $count = -1;
    foreach $bucket (@hist_headers)
    {
       $count = $count + 1;
       if ($count < $hi_bucket)
       {
           $hist_labels = $hist_labels . sprintf ("%8s", ' <' . $bucket->{LABEL});
       }
       else
       {
           $hist_labels = $hist_labels . sprintf ("%8s", ' >=' . $bucket->{LABEL});
       }
    }
}
if (defined($opt_s))
{
    $sort_list = $opt_s;
    $sort_list =~ tr/a-z/A-Z/;
    $sort_list =~ s/ //g;
    @sort_list = split(/,/,$sort_list);

    if (index($sort_list, 'HV') >=0  and scalar @sort_list > 1)
    {
          print STDERR "\nERROR: HV (Hash Value) cannnot be used in conjunction with other keys as a sort option\n";
          exit;
    }
    elsif (index($sort_list, 'UID') >=0  and scalar @sort_list > 1)
    {
          print STDERR "\nERROR: UID (User Id) cannnot be used in conjunction with other keys as a sort option\n";
          exit;
    }

    foreach $sort_key (keys %valid_sort_params)
    {
        $valid_sort_params = $valid_sort_params . ',' . $sort_key;
    }
    foreach $sort_key (@sort_list)
    {
        if (index($valid_sort_params,$sort_key) == -1)
            {
                print STDERR "\nERROR: Invalid sort key specified: $sort_key\n";
                print STDERR "\nUse $prog -h for a valid list\n";
                exit(1);
            }
    }   
}

if (defined($opt_S))
{
    $skip_sys = 'Y';
}
if (! $sort_list)
{
    $sort_list = 'PRSELA:EXEELA:FCHELA';
    @sort_list = split(/:/,$sort_list);
}

if (defined($opt_t))
{
    $trace_file = $opt_t;
}
else
{
    print "\nUsage:  $prog -h | [ -e -p -s sort_key_list -S ] { -t trace_file.trc -o output_file }\n\n";
    print "Use -h for more help\n";
    exit;
}
if (defined($opt_o))
{
    $output = $opt_o;
    open(OUTPUT, ">$output")     || die "Failed to open output file ($output)\n";
}
else
{
    #print OUTPUT "\nUsage:  $prog -h | [ -e -p -s sort_key_list -S ] { -t trace_file.trc -o output_file }\n\n";
    #print OUTPUT "Use -h for more help\n";
    #exit;
        *OUTPUT=*STDOUT ;
}

if (defined($opt_e) or defined($opt_p))
{
    $gather_cursor_data = 1;
}


if (defined($opt_c))
{
  $conn_str = $opt_c;
}

open(TRACEFILE, $trace_file) || die "Failed to open trace file ($trace_file)\n";
   

printf "$prog: 10046 traces events analyser ver $ver\n\n";
printf OUTPUT "$prog: 10046 traces events analyser ver $ver\n\n";
print "Processing tracefile: $trace_file\n";

if ($conn_str)
{
  my @instance       = ();
  my %inst_headers   = ();
  my %instance       = ();

  test_dbconn($conn_str);
  exec_sqlplus_sql ($inst_sql, $conn_str, \@instance);
  $inst_name = $instance[3];
}

@trace_file = <TRACEFILE>;
close(TRACEFILE);

$full_version = $trace_file[1];
$full_version =~ s/^.*([1-9 ]\d.\d\.\d\.\d.\d).*/$1/;
$full_version =~ s/([\S]+).*/$1/;
$base_version = $full_version;
$base_version =~ s/^([\d ]+\.[\d]+)[\.].*/$1/;

if ($base_version < 9.2)
{
    print STDERR "$prog: Unsupported trace version ($base_version)\n";
    print OUTPUT "$prog: Unsupported trace version ($base_version)\n";
    exit(1);
}

print OUTPUT "*** Trace file header details ***\n\n";
foreach $event_line (@trace_file)
{
    chomp $event_line;
    if (length($event_line))
         {
             if (index($event_line,'Instance name:') == 0 )
             {
                 $instance_name = $event_line;
             }
             print OUTPUT "$event_line\n";
         }
    else { last; }
}
$instance_name =~ s/Instance name:[\s]+(.*)/$1/;

if ( $inst_name ne $instance_name and $conn_str )
{
    print STDERR "\nWARNING: The instance name in the tracefile does not match the\n";
    print STDERR "         instance specified for the connect string.\n";
    print STDERR "         Please ensure that this is the database which\n";
    print STDERR "         was used to create the trace.\n\n";
    print OUTPUT "\nWARNING: The instance name in the tracefile does not match the\n";
    print OUTPUT "         instance specified for the connect string.\n";
    print OUTPUT "         Please ensure that this is the database which\n";
    print OUTPUT "         was used to create the trace.\n\n";
}
print OUTPUT "Trace input file : $trace_file\n";

sub process_events
{
  my ($events) = (@_);
  my @ev       = @$events;
  $tot_ela = 0;
  foreach $event_line (@ev)
  {
 
    $event = $elapsed = $objid = $event_line;
  # $event =~ s/^.*nam='([\\:\w\s\d-]+)'.*/$1/;
  # Kyle Hailey mod to fix SQL*Net events
    $event =~ s/^.*nam='//;
    $event =~ s/' ela=.*//;

    $elapsed  =~ s/^.*ela=[\s]{0,1}[\s]*([\d]+) .*/$1/;
    if ( $pre_10_2 == 0 )
    {
      if ( $event_line !~ m/obj#/ )
      {
        print OUTPUT "\n\n***********************************************\n";
        print OUTPUT "*** Trace file format looks pre Oracle 10.2 ***\n";
        print OUTPUT "***********************************************\n\n";
        $pre_10_2 = 1;
      }
    }
 
    if ($pre_10_2 )
    {
      $objid  =~ s/^.*p1=[-\s]*([\d]+) .*/$1/;
      $obj_header = 'P1';
    }
    else
    {
      $objid  =~ s/^.*obj#=[-\s]*([\d]+) .*/$1/;
      $object_ids{$objid} = 1;
    }
 
    $objid_event = sprintf("%-19s: %-45s", $objid, $event);
  # printf OUTPUT  ("%s %s %s\n", $event, $elapsed, $objid);
 
                                         #*************************************/
                                         #  We maintain this (simple) hash    */
                                         # as a method of sorting by total    */
                                         # elapsed time per event             */
                                         #*************************************/
    if (exists $tot_sort{$event})
    { $tot_sort{$event} = $tot_sort{$event} + $elapsed; }
    else { $tot_sort{$event} = $elapsed; }
 
                                         #*************************************/
                                         #  Get histogram counts              */
                                         #*************************************/
    $ela_ms = $elapsed / 1000;
    #$ela_ms = $elapsed;
    $tot_ela = $tot_ela + $elapsed;
                                         #*************************************/
                                         #  Build an anonymous record         */
                                         # structure containing our event     */
                                         # data.                              */
                                         #*************************************/
    my @ev_buckets = build_hist_buckets($window_adj);
    $count = -1;
    foreach $bucket (@ev_buckets)
    {
      $count = $count + 1;
      if ($bucket->{LOW_VAL} <= $ela_ms and $ela_ms < $bucket->{HI_VAL} and $count < $hi_bucket)
      {
        $ev_buckets[$count]->{COUNT} = 1;
        last;
      }
      elsif ($bucket->{LOW_VAL} <= $ela_ms and $ela_ms < $bucket->{HI_VAL} and $count == $hi_bucket)
      {
        $ev_buckets[$count]->{COUNT} = 1;
      }
     }
   $event_rec = {OBJ_EVENT  => $objid_event
               , OBJID      => $objid
               , EVENT      => $event
               , TOT_ELA    => $elapsed
               , COUNT      => 1
               , EV_BUCKETS => \@ev_buckets
                };
                                         #*************************************/
                                         #  Pull the record into $er (if it   */
                                         # exists) and update the histogram.  */
                                         #*************************************/
   if ($er = $event_stat{$objid_event})
   {
     $ref_buckets   = $er->{EV_BUCKETS};
     my @ev_buckets = @$ref_buckets;
     $er->{COUNT}   = $er->{COUNT} + 1;
     $er->{TOT_ELA} = $er->{TOT_ELA} + $event_rec->{TOT_ELA};

     $count = -1;
     foreach $bucket (@ev_buckets)
     {
       $count = $count + 1;
       if ($bucket->{LOW_VAL} <= $ela_ms and $ela_ms < $bucket->{HI_VAL} and $count < $hi_bucket)
       {
         $ev_buckets[$count]->{COUNT} = $ev_buckets[$count]->{COUNT} + 1;
         last;
       }
       elsif ($count == $hi_bucket)
       {
         $ev_buckets[$count]->{COUNT} = $ev_buckets[$count]->{COUNT} + 1;
       }
     }
     $er->{EV_BUCKETS}=\@ev_buckets;
     $event_stat {$er -> {OBJ_EVENT}} = $er;
     $tot_objn_sort{$objid_event} = $tot_objn_sort{$objid_event} + $elapsed;
   }
   else
                                       #*************************************/
                                       # Record doesn't exists so create a  */
                                       # new object/event record.           */
                                       #*************************************/
   {
     $event_stat {$event_rec->{OBJ_EVENT}} = $event_rec;
     $tot_objn_sort{$objid_event} = $elapsed;
   }
 
   $aggr_rec = {EVENT => $event, TOT_ELA => $elapsed,  COUNT => 1};
   if ($er = $event_aggr{$event})
   {
     $er->{COUNT}   = $er->{COUNT} + 1;
     $er->{TOT_ELA} = $er->{TOT_ELA} + $aggr_rec->{TOT_ELA};
     $er->{TOT_ELA} = $er->{TOT_ELA} + 1;
     $event_aggr {$er -> {EVENT}} = $er;
   }
   else
   {
     $event_aggr {$aggr_rec -> {EVENT}} = $aggr_rec;
   }
  }
}

sub report_events
{
  my $no_keys = keys %event_aggr;
  if ( $no_keys == 0)
  {
      print OUTPUT  "*** No event data exists for this section ***\n";
      return;
  }

  print OUTPUT  "\nEVENT AGGREGATES\n";
  print OUTPUT  "================\n\n";
  printf OUTPUT ("Wait Event %s Times Waited Elapsed (ms)     %%Total  Avg Ela (ms)   \n",' ' x 34);
  printf OUTPUT ("~~~~~~~~~~~%s ~~~~~~~~~~~~ ~~~~~~~~~~~~     ~~~~~~  ~~~~~~~~~~~~  \n",'~' x 34);
  foreach $objid_event (sort {$tot_sort{$b} <=>  $tot_sort{$a} } keys %event_aggr)
  {
      my $ev = $event_aggr{$objid_event};
      my $pct_total_ela;
      if ($total_elapse)
      {
          $pct_total_ela = ($ev->{TOT_ELA}*100)/$total_elapse;
      }
      elsif ($tot_ela == 0)
      {
          $pct_total_ela = 0;
      }
      else
      {
          $pct_total_ela = $ev->{TOT_ELA}*100/$tot_ela;
      }
      my $tot_elapsed = 0.00000 + $ev->{TOT_ELA };
      printf OUTPUT ("%45s %12.0f %12.0f     %6.2f  %12.0f\n",$ev->{EVENT}, $ev->{COUNT}, $ev->{TOT_ELA}/1000, $pct_total_ela, ($ev->{TOT_ELA}/$ev->{COUNT})/1000 );
  }
  printf OUTPUT ("           %s              ~~~~~~~~~~~~                         \n",' ' x 34);
  printf OUTPUT ("         %s Total Elapsed: %12.0f                                   \n",' ' x 34,$tot_ela / 1000);
  print  OUTPUT  "\nEVENT AGGREGATE BREAKDOWN\n";
  print  OUTPUT  "=========================\n\n";
                                       #*************************************/
                                       #  Just use the 1st record entry     */
                                       # here to get the FIELD_LENGTH       */
                                       #*************************************/
  foreach $objid_event (keys %event_stat)
  {
    if ( $conn_str )
    {
      my $ev = $event_stat{$objid_event};
      $field_length = 30;
      $fmt_mask  = '%-' . $field_length . 's ';
      printf OUTPUT ($fmt_mask, 'Object Name');
    }
    last;
  }
  printf OUTPUT ("%-9s         : Wait Event %s Times Waited Tot Ela (ms) %%Total Avg Ela (ms)\n",$obj_header, ' ' x 34);
  if ( $conn_str )
  {  
      printf OUTPUT ("%s ", '~' x $field_length);
  }
  printf OUTPUT ("~~~~~~~~~~~~~~~~~ : ~~~~~~~~~~~%s ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~ ~~~~~~~~~~~~\n",'~' x 34);
  foreach $objid_event (sort {$tot_objn_sort{$b} <=>  $tot_objn_sort{$a} } keys %event_stat)
  {
      my $ev = $event_stat{$objid_event};
      my $pct_total_ela;
      if ($total_elapse)
      {
          $pct_total_ela = ($ev->{TOT_ELA}*100)/$total_elapse;
      }
      elsif ($tot_ela == 0)
      {
          $pct_total_ela = 0;
      }
      else
      {
          $pct_total_ela = $ev->{TOT_ELA}*100/$tot_ela;
      }
      if ($conn_str)
      {
        $object_name = dataset_attr($ev->{OBJID}, 'OBJECT_NAME','FIELD_VALUE', \%objects);
        if ($object_name eq '0')
        {
            $object_name = 'UNKNOWN';
        }
        $fmt_mask  = '%-' . $field_length . 's ';
        printf OUTPUT ($fmt_mask, $object_name);
      }
      $objid_event = sprintf("%-18s: %-45s", $ev->{OBJID}, $ev->{EVENT});
      printf OUTPUT ("%58s %12.0f %12.0f  %5.2f %12.0f\n",$objid_event, $ev->{COUNT}, $ev->{TOT_ELA}/1000
                                         , $pct_total_ela, ($ev->{TOT_ELA}/$ev->{COUNT})/1000);
  }
  print OUTPUT "\nEVENT HISTOGRAM BREAKDOWN\n";
  print OUTPUT "===========================\n\n";
  print OUTPUT "This section splits the event counts into elapsed time\n";
  print OUTPUT "buckets so that we can see if there are any poor\n";
  print OUTPUT "or anomalous response time / frequency patterns.\n\n";
  printf OUTPUT ("%-9s          : Wait Event %s%s\n",$obj_header, ' ' x 34, $hist_labels);
  printf OUTPUT ("~~~~~~~~~~~~~~~~~~ : ~~~~~~~~~~~%s %s~~~~~~~~\n",'~' x 34, '~~~~~~~ ' x ($disp_buckets - 1));
  foreach $objid_event (sort {$tot_objn_sort{$b} <=>  $tot_objn_sort{$a} } keys %event_stat)
  {
      my $ev = $event_stat{$objid_event};
      $objid_event = sprintf("%-19s: %-45s", $ev->{OBJID}, $ev->{EVENT});
      printf OUTPUT ("%60s ", $objid_event);
      $ref_buckets = $ev->{EV_BUCKETS};
      @ev_buckets = @$ref_buckets;
      $count = -1;
      foreach $bucket (@ev_buckets)
      {
         $count = $count + 1;
         if ( $count < $hi_bucket)
         {
           printf OUTPUT ("%7s ", $bucket->{COUNT} ? $bucket->{COUNT} : "" );
         }
         else
         {
           printf OUTPUT ("%8s ", $bucket->{COUNT} ? $bucket->{COUNT} : "" );
         }
      }
      printf OUTPUT ("\n");
  }
}

#******************************************************************************/
#* Accumulate cursor related data.                                            */
#******************************************************************************/
my $curs_handle;
my $in_stmnt = 0;
my $sql_id;
my $sql_idx;    # sql_id concatenated with plan hash value


if ($gather_cursor_data)
{
  foreach $record (@trace_file)
  {
    my $ad;
    my $cpu;
    my $cr;
    my $cu;
    my $dsk;
    my $ela;
    my $hv;
    my $mis;
    my $pcurs;
    my $plh;
    my $rows;
    my $uid;
    my $dep;
    my $exec_count  = 0;
 
    chomp $record;
 
    my @statement      = ();
    my @waits          = ();
    my %stat           = ();
    my %plan_hash_vals = ();
 
    my $handle_rec;
    my $cursor_rec;

    my $wait = {
                   @waits
                 };
 
    my $stat = {
                   %stat
                 };
 
    if ( $record =~ m/^PARSING IN CURSOR/  )
    {
          $curs_handle = $hv = $ad = $sql_id = $uid = $dep = $record;
          $curs_handle =~ s/^PARSING IN CURSOR #([\d]+) .*/$1/;
          $uid    =~ s/^.* uid=([\d]+) .*$/$1/;
        if ($skip_sys eq 'Y' and $uid == 0)
        {
            next;
        }
          $hv     =~ s/^.* hv=([\d]+) .*$/$1/;
          $ad     =~ s/^.* ad='([\d\w]+)'/$1/;
          $dep    =~ s/^.* dep=([\d]+) .*$/$1/;
          if ($base_version > 11.0)
          {
            $sql_id =~ s/^.*sqlid='([\d\w]+)'.*$/$1/;
          }
          else
          {
                                         #*************************************/
                                         #  PARSING IN lines only have SQL    */
                                         # Id in 11g so we need to use        */
                                         # cursor address instead.            */
                                         #*************************************/
            $sql_id = $ad;
          }
                                         #*************************************/
                                         # Register our new handle and        */
                                         # SQL_Id. We need this structure to  */
                                         # store our cursor details until we  */
                                         # have a Plan Hash Value (PLH). We   */
                                         # can only rely on the PLH at EXEC   */
                                         # time due to bind peeking and       */
                                         # Adaptive Cursor Sharing (ACS). We  */
                                         # need to accumulate stats for SQL   */
                                         # Id + PLH so that the report will   */
                                         # reflect the performance by child   */
                                         # cursor.                            */
                                         #*************************************/
        $handle_rec = { SQL_ID            => $sql_id
                      , HV              => $hv   
                      , ADDRESS         => $ad
                      , UID             => $uid
                      , DEP             => $dep
                      , EXEPLH          => 0
                      , PRSPLH          => 0
                      , CURSOR_KEY      => 0
                      , STMNT_COMPLETE  => 0
                      , STATEMENT       => \@statement
                      , WAITS           => \@waits
                      , PARSE_COMPLETE  => 0
                      , PRSCNT          => 0
                      , PRSDSK          => 0
                      , PRSCPU          => 0
                      , PRSELA          => 0
                      , PRSQRY          => 0
                      , PRSCU           => 0
                      , PRSMIS          => 0
                      };
          $curs_handles{$curs_handle} = $handle_rec;
          $in_stmnt = 1;
          next;
      }
 
      if ( $record eq 'END OF STMT' and exists ($curs_handles{$curs_handle}))
      {
          $in_stmnt = 0;
          $curs_handles{$curs_handle}->{STMNT_COMPLETE} = 1;
          next;
      }
 
      if ($in_stmnt and exists ($curs_handles{$curs_handle}))
      {
                                         #*************************************/
                                         #  Add the line to the statement     */
                                         # lines.                             */
                                         #*************************************/
        push @{$curs_handles{$curs_handle}->{STATEMENT}}, ($record);
        my $rec;
 
        next;
      }
      if ( $record =~ m/^PARSE #/  )
      {   
          $curs_handle = $ela = $cpu =  $mis = $rows = $cr = $cu = $plh = $dsk = $record;
          $curs_handle =~ s/^PARSE #([\d]+):.*/$1/;
          if (exists ($curs_handles{$curs_handle}))
          {
            $cpu   =~ s/^.*:c=([\d]+),.*$/$1/;
            $ela   =~ s/^.*,e=[\s]{0,1}([\d]+),.*$/$1/;
            $cr    =~ s/^.*,cr=([\d]+),.*$/$1/;
            $cu    =~ s/^.*,cu=([\d]+),.*$/$1/;
            $dsk    =~ s/^.*,p=([\d]+),.*$/$1/;
            $mis   =~ s/^.*,mis=([\d]+),.*$/$1/;
            $plh   =~ s/^.*,plh=([\d]+),.*$/$1/;
        
                                       #**************************************/
                                       #* Note: We cannot rely on the parse  */
                                       #* plan hash value. The EXEC gives    */
                                       #* us the reliable reading. That      */
                                       #* being the case we temporarily      */
                                       #* collect stats etc against the      */
                                       #* cursor handle until we can         */
                                       #* establish the tue PLH.             */
                                       #**************************************/
          $curs_handles{$curs_handle}->{PRSPLH} = $plh;
          $curs_handles{$curs_handle}->{PRSCNT} = $curs_handles{$curs_handle}->{PRSCNT} + 1;
          $curs_handles{$curs_handle}->{PRSDSK} = $curs_handles{$curs_handle}->{PRSDSK} + $dsk;
          $curs_handles{$curs_handle}->{PRSCPU} = $curs_handles{$curs_handle}->{PRSCPU} + $cpu;
          $curs_handles{$curs_handle}->{PRSELA} = $curs_handles{$curs_handle}->{PRSELA} + $ela;
          $curs_handles{$curs_handle}->{PRSQRY} = $curs_handles{$curs_handle}->{PRSQRY} + $cr;
          $curs_handles{$curs_handle}->{PRSCU}  = $curs_handles{$curs_handle}->{PRSCU}  + $cu;
          $curs_handles{$curs_handle}->{PRSMIS} = $curs_handles{$curs_handle}->{PRSMIS} + $mis;
          }
          next;
      }
 
      if ( $record =~ m/^EXEC #/  )
      {   
          $curs_handle = $ela = $cpu =  $mis = $rows = $cr = $cu = $plh = $dsk = $record;
          $curs_handle =~ s/^EXEC #([\d]+):.*/$1/;
          if (exists $curs_handles{$curs_handle})
          {
            $cpu   =~ s/^.*:c=([\d]+),.*$/$1/;
            $ela   =~ s/^.*,e=[\s]{0,1}([\d]+),.*$/$1/;
            $cr    =~ s/^.*,cr=([\d]+),.*$/$1/;
            $cu    =~ s/^.*,cu=([\d]+),.*$/$1/;
            $dsk   =~ s/^.*,p=([\d]+),.*$/$1/;
            $mis   =~ s/^.*,mis=([\d]+),.*$/$1/;
            $rows  =~ s/^.*,r=([\d]+),.*$/$1/;
            $plh   =~ s/^.*,plh=([\d]+),.*$/$1/;
            $sql_id = $curs_handles{$curs_handle}->{SQL_ID};

          my $sql_idx = $sql_id . ':' . $plh;
          $curs_handles{$curs_handle}->{CURSOR_KEY}       = $sql_idx;
          $curs_handles{$curs_handle}->{PARSE_COMPLETE}   = 1;

            if (! exists $cursor{$sql_idx})
          {
 
                                         #**************************************/
                                         #*  Build an anonymous record         */
                                         #* structure containing our parent    */
                                         #* cursor details and store details.  */
                                         #**************************************/
              $cursor_rec = {CURSOR_HANDLE  => $curs_handle
                           , SQL_ID         => $curs_handles{$curs_handle}->{SQL_ID}
                           , HV             => $curs_handles{$curs_handle}->{HV}
                           , ADDRESS        => $curs_handles{$curs_handle}->{ADDRESS}
                           , UID            => $curs_handles{$curs_handle}->{UID}
                           , DEP            => $curs_handles{$curs_handle}->{DEP}
                           , PRSPLH         => $curs_handles{$curs_handle}->{PRSPLH}
                           , EXEPLH         => $plh
                           , STATEMENT      => $curs_handles{$curs_handle}->{STATEMENT}
                           , WAITS          => $curs_handles{$curs_handle}->{WAITS}
                           , STAT           => \%stat
                           , SORT_VAL       => 0
                           , PRSCNT         => $curs_handles{$curs_handle}->{PRSCNT}
                           , PRSDSK         => $curs_handles{$curs_handle}->{PRSDSK}
                           , PRSCPU         => $curs_handles{$curs_handle}->{PRSCPU}
                           , PRSELA         => $curs_handles{$curs_handle}->{PRSELA}
                           , PRSQRY         => $curs_handles{$curs_handle}->{PRSQRY}
                           , PRSCU          => $curs_handles{$curs_handle}->{PRSCU}
                           , PRSMIS         => $curs_handles{$curs_handle}->{PRSMIS}
                           , EXECNT         => 1
                           , EXEDSK         => $dsk
                           , EXECPU         => $cpu
                           , EXEELA         => $ela
                           , EXEQRY         => $cr
                           , EXECU          => $cu
                           , EXEROW         => $rows
                           , EXEMIS         => $mis
                           , FCHCNT         => 0
                           , FCHDSK         => 0
                           , FCHCPU         => 0
                           , FCHELA         => 0
                           , FCHQRY         => 0
                           , FCHCU          => 0
                           , FCHROW         => 0
                             };
            $cursor{$sql_idx} = $cursor_rec;
            $curs_handles{$curs_handle}->{PRSCNT} = 0;
            $curs_handles{$curs_handle}->{PRSDSK} = 0;
            $curs_handles{$curs_handle}->{PRSCPU} = 0;
            $curs_handles{$curs_handle}->{PRSELA} = 0;
            $curs_handles{$curs_handle}->{PRSQRY} = 0;
            $curs_handles{$curs_handle}->{PRSCU}  = 0;
            $curs_handles{$curs_handle}->{PRSMIS} = 0;
          }
          else
          {
                                       #**************************************/
                                       #*  need to update any further        */
                                       #* accumulations from further PARSE   */
                                       #* declarations found.`               */
                                       #**************************************/
            $cursor{$sql_idx}->{PRSCNT} = $cursor{$sql_idx}->{PRSCNT} +
                                            $curs_handles{$curs_handle}->{PRSCNT};
            $cursor{$sql_idx}->{PRSDSK} = $cursor{$sql_idx}->{PRSDSK} +
                                            $curs_handles{$curs_handle}->{PRSDSK};
            $cursor{$sql_idx}->{PRSCPU} = $cursor{$sql_idx}->{PRSCPU} +
                                            $curs_handles{$curs_handle}->{PRSCPU};
            $cursor{$sql_idx}->{PRSELA} = $cursor{$sql_idx}->{PRSELA} +
                                            $curs_handles{$curs_handle}->{PRSELA};
            $cursor{$sql_idx}->{PRSQRY} = $cursor{$sql_idx}->{PRSQRY} +
                                            $curs_handles{$curs_handle}->{PRSQRY};
            $cursor{$sql_idx}->{PRSCU}  = $cursor{$sql_idx}->{PRSCU}  +
                                            $curs_handles{$curs_handle}->{PRSCU};
            $cursor{$sql_idx}->{PRSMIS} = $cursor{$sql_idx}->{PRSMIS} +
                                            $curs_handles{$curs_handle}->{PRSMIS};

            $curs_handles{$curs_handle}->{PRSCNT} = 0;
            $curs_handles{$curs_handle}->{PRSDSK} = 0;
            $curs_handles{$curs_handle}->{PRSCPU} = 0;
            $curs_handles{$curs_handle}->{PRSELA} = 0;
            $curs_handles{$curs_handle}->{PRSQRY} = 0;
            $curs_handles{$curs_handle}->{PRSCU}  = 0;
            $curs_handles{$curs_handle}->{PRSMIS} = 0;

            $cursor{$sql_idx}->{EXECNT} = $cursor{$sql_idx}->{EXECNT} + 1;
            $cursor{$sql_idx}->{EXECPU} = $cursor{$sql_idx}->{EXECPU} + $cpu;
            $cursor{$sql_idx}->{EXEELA} = $cursor{$sql_idx}->{EXEELA} + $ela;
            $cursor{$sql_idx}->{EXEQRY} = $cursor{$sql_idx}->{EXEQRY} + $cr;
            $cursor{$sql_idx}->{EXECU}  = $cursor{$sql_idx}->{EXECU}  + $cu;
            $cursor{$sql_idx}->{EXEROW} = $cursor{$sql_idx}->{EXEROW} + $rows;
            $cursor{$sql_idx}->{EXEMIS} = $cursor{$sql_idx}->{EXEMIS} + $mis;
            $cursor{$sql_idx}->{EXEDSK} = $cursor{$sql_idx}->{EXEDSK} + $dsk;
          }

          }
          next;
      } 
 
      if ( $record =~ m/^STAT #/  )
      {   
          my ($pid, $obj);
          $curs_handle = $record;
          $curs_handle =~ s/^STAT #([\d]+)[:\s].*/$1/;
          if (exists $curs_handles{$curs_handle})
          {
          my $sql_idx  = $curs_handles{$curs_handle}->{CURSOR_KEY};
            $exec_count = $cursor{$sql_idx}->{EXECNT};
            $stat = $cursor{$sql_idx}->{STAT};
            my $stat_array;
            my @stat = ();
            if ( exists ($stat->{$exec_count}))
            {
               $stat_array = $stat->{$exec_count};
               @stat       = @$stat_array;
               push @stat, ($record);
            }
            else
            {
               push @stat, ($record);
            }
            $stat->{$exec_count} = \@stat;
            $cursor{$sql_idx}->{STAT} = $stat;
          }
          next;
      }

      if ( $record =~ m/^FETCH #/  )
      {   
          $curs_handle = $ela = $cpu =  $mis = $rows = $cr = $cu = $plh = $dsk = $record;
          $curs_handle =~ s/^FETCH #([\d]+):.*/$1/;
          if (exists $curs_handles{$curs_handle})
          {
          my $sql_idx  = $curs_handles{$curs_handle}->{CURSOR_KEY};
            $cpu   =~ s/^.*:c=([\d]+),.*$/$1/;
            $ela   =~ s/^.*,e=[\s]{0,1}([\d]+),.*$/$1/;
            $cr    =~ s/^.*,cr=([\d]+),.*$/$1/;
            $cu    =~ s/^.*,cu=([\d]+),.*$/$1/;
            $dsk   =~ s/^.*,p=([\d]+),.*$/$1/;
            $mis   =~ s/^.*,mis=([\d]+),.*$/$1/;
            $rows  =~ s/^.*,r=([\d]+),.*$/$1/;
            $plh   =~ s/^.*,plh=([\d]+),.*$/$1/;

          $cursor{$sql_idx}->{FCHCNT} = $cursor{$sql_idx}->{FCHCNT} + 1;
          $cursor{$sql_idx}->{FCHDSK} = $cursor{$sql_idx}->{FCHDSK} + $dsk;
          $cursor{$sql_idx}->{FCHCPU} = $cursor{$sql_idx}->{FCHCPU} + $cpu;
          $cursor{$sql_idx}->{FCHELA} = $cursor{$sql_idx}->{FCHELA} + $ela;
          $cursor{$sql_idx}->{FCHQRY} = $cursor{$sql_idx}->{FCHQRY} + $cr;
          $cursor{$sql_idx}->{FCHCU}  = $cursor{$sql_idx}->{FCHCU}  + $cu;
          $cursor{$sql_idx}->{FCHROW} = $cursor{$sql_idx}->{FCHROW} + $rows;

          }
          next;
      } 
 
      if ( $record =~ m/^WAIT #/  )
      {   
          $curs_handle = $record;
          $curs_handle =~ s/^WAIT #([\d]+):.*/$1/;
          if (exists ($curs_handles{$curs_handle}))
        {
          my $sql_idx  = $curs_handles{$curs_handle}->{CURSOR_KEY};
                                       #*************************************/
                                       # Check to see if optimization is    */
                                       # complete (i.e. we have hit the     */
                                       # EXEC for this statement. If not    */
                                       # we need to store wait events in    */
                                       # the cursor stub entry in $cursor,  */
                                       # since we don't know as yet, the    */
                                       # exec PLH.                          */
                                       #*************************************/
          if ($curs_handles{$curs_handle}->{PARSE_COMPLETE})
          {
              if ($curs_handle != 0 and exists ($cursor{$sql_idx}))
              {
                push @{$cursor{$sql_idx}->{WAITS}}, ($record);
              }
              elsif ($curs_handle != 0 and $curs_handles{$curs_handle}->{PARSE_COMPLETE} == 0)
              {
                push @{$curs_handles{$curs_handle}->{WAITS}}, ($record);
            }
              else
              {
                                        #**************************************/
                                        #* Trace file looks incomplete as we  */
                                        #* have a missing SQL Id. Trace may   */
                                        #* have started after the session     */
                                        #* had already parsed one or more     */
                                        #* statements.                        */
                                        #**************************************/
                  # print OUTPUT  "DBG: Missing SQL id: $sql_id\n";
                  $orphan_handle = 1;
                $orphan_handles{$curs_handle} = 1;
              }
          }
          else
          {
            push @{$curs_handles{$curs_handle}->{WAITS}}, ($record);
          }
        }
          else
        {
          if ($curs_handle != 0)
          {
 
                                        #*************************************/
                                        # Trace file looks incomplete as we  */
                                        # have a missing handle. Trace may   */
                                        # have started after the session     */
                                        # had already parsed one or more     */
                                        # statements.                        */
                                        #*************************************/
            $orphan_handle = 1;
            $orphan_handles{$curs_handle} = 1;
          }
        }
          next;
      }
 
  }
                                       #**************************************/
                                       #* Now set the sort value field       */
                                       #* based on the sort criteria.        */
                                       #**************************************/
  for $sql_idx (keys %cursor)
  {
      foreach $sort_key (@sort_list)
      {
          if ($sort_key eq 'SQL_ID')
          {
            $cursor{$sql_idx}->{SORT_VAL} = $cursor{$sql_idx}->{SQL_ID};
          }
          else
          {
            $cursor{$sql_idx}->{SORT_VAL} = $cursor{$sql_idx}->{SORT_VAL} +
                                            $cursor{$sql_idx}->{$sort_key};
          }
      }
  }
}
#*****************************************************************************/
# Report of overall wait events section.                                     */
#*****************************************************************************/
                                       #**************************************/
                                       #* Load only the WAIT event lines     */
                                       #* into @events                       */
                                       #**************************************/
  @events = grep(/^WAIT/, @trace_file);
  chomp @events;
  process_events(\@events);
  $total_elapse = $tot_ela;
  print OUTPUT  "\nREPORT OPTIONS\n";
  print OUTPUT  "==============\n";
  if ( defined ($opt_e) )
  {
      print OUTPUT  "Cursor level event statistics and histograms requested\n"
  }
  if ( defined ($opt_p) )
  {
      print OUTPUT  "Cursor level row source plans and statistics requested\n"
  }
  if ( defined ($opt_s) )
  {
      print OUTPUT  "Sort keys $sort_list\n"
  }
#*****************************************************************************/
# If -c option specified AND trace file is 10.2 or later, build a list of    */
# Object Ids to include in SQL to obtain object details.                     */
#*****************************************************************************/

  if ( ! $pre_10_2  and $conn_str)
  {

    foreach $objid (keys %object_ids)
    {
      if ($pred eq '(')
      {
        $pred = $pred . $objid;
      }
      else
      {
        $pred = $pred . ', ' . $objid;
      }
    }
    $pred = $pred . ')';
    $obj_sql = $obj_sql . ' ' . $pred;

    exec_sqlplus_sql ($obj_sql, $conn_str, \@objects);
    prep_dataset (\@objects, \%obj_headers, \%objects);
  }
  elsif ( $conn_str )
  {
    print "INFO: Pre 10.2 trace file; ignoring -c option.\n";
    $conn_str = '';
  }
  print OUTPUT  "\n\nOVERALL WAIT EVENT FIGURES\n";
  print OUTPUT  "==========================\n";
  report_events;

#******************************************************************************/
#*  Reporting of cursor related data starts here.                             */
#******************************************************************************/
my $ad;
my $dsk;
my $cpu;
my $cr;
my $cu;
my $cursor_rec;
my %cursor_rec;
my $ela;
my $hv;
my $mis;
my $pcurs;
my $plh;
my $rows;
my @statement;
my $uid;
my $rec;
my $parse;
my $execs;
my $fetch;


print OUTPUT "\nSQL / CURSOR DETAILS\n";
print OUTPUT "====================\n";

print OUTPUT "SQL / Cursor details sorted by:\n\n";
foreach $sort_key (@sort_list)
{
    print OUTPUT "$sort_key : $valid_sort_params{$sort_key}\n";
}
if (! defined($opt_s))
{
    print OUTPUT "\nThese are the defaulted sort keys.\n";
}
print OUTPUT "\n";
if ($orphan_handle)
{
    print OUTPUT  "WARNING: Some cursor handles have no associated ";
    print OUTPUT  "\"PARSING IN CURSOR\" declaration. One\n";
    print OUTPUT  "         reason (but not necessarily) for this could";
    print OUTPUT  " be that  the trace was started\n";
    print OUTPUT  "         after the traced session work began.\n\n";
    my $handle;
    print OUTPUT "Orphaned Handles\n";
    print OUTPUT "================\n";
    foreach $handle (keys %orphan_handles)
    {
        print OUTPUT "#$handle\n";
    }
}

printf OUTPUT ("\n%s\n", '*' x 155);
print OUTPUT "Count    = Number of times OCI procedure was executed\n";
print OUTPUT "CPU      = CPU time in seconds executing \n";
print OUTPUT "Elapsed  = Elapsed time in seconds executing\n";
print OUTPUT "Disk     = Number of physical read of buffers from disk\n";
print OUTPUT "Query    = Number of buffers gotten for consistent read\n";
print OUTPUT "Current  = Number of buffers gotten in current mode (usually for update)\n";
print OUTPUT "Rows     = Number of rows processed by the fetch or execute call\n";

for $sql_idx (sort{ $cursor{$b}->{SORT_VAL} <=> $cursor{$a}->{SORT_VAL} } keys %cursor)
{

  %tot_sort      = ();
  %tot_objn_sort = ();
  %event_stat    = ();
  %event_aggr    = ();
  $cursor_rec    = $cursor{$sql_idx};
  $sql_id        = $cursor_rec->{SQL_ID};
  $plh           = $cursor_rec->{EXEPLH};
  $hv            = $cursor_rec->{HV};
 
  my $count_tot = $cursor{$sql_idx}->{PRSCNT} + $cursor{$sql_idx}->{EXECNT}
                                              + $cursor{$sql_idx}->{FCHCNT};
  my $cpu_tot   = sprintf("%14.2f", $cursor{$sql_idx}->{PRSCPU}/1000000)
                + sprintf("%14.2f", $cursor{$sql_idx}->{EXECPU}/1000000)
                + sprintf("%14.2f", $cursor{$sql_idx}->{FCHCPU}/1000000);

  my $ela_tot   = sprintf("%14.2f", $cursor{$sql_idx}->{PRSELA}/1000000)
                + sprintf("%14.2f", $cursor{$sql_idx}->{EXEELA}/1000000)
                + sprintf("%14.2f", $cursor{$sql_idx}->{FCHELA}/1000000);
# $cpu_tot      = $cpu_tot / 1000000;
# $ela_tot      = $ela_tot / 1000000;

  my $cr_tot    = $cursor{$sql_idx}->{PRSQRY} + $cursor{$sql_idx}->{EXEQRY}
                                              + $cursor{$sql_idx}->{FCHQRY};

  my $cu_tot    = $cursor{$sql_idx}->{PRSCU}  + $cursor{$sql_idx}->{EXECU}
                                              + $cursor{$sql_idx}->{FCHCU};

  my $rows_tot  = $cursor{$sql_idx}->{EXEROW} + $cursor{$sql_idx}->{FCHROW};

  my $dsk_tot = $cursor{$sql_idx}->{PRSDSK}   + $cursor{$sql_idx}->{EXEDSK}
                                              + $cursor{$sql_idx}->{FCHDSK};
 
  $plh       = $cursor{$sql_idx}->{EXEPLH};
  $cpu       = $cursor{$sql_idx}->{PRSCPU} / 1000000;
  $ela       = $cursor{$sql_idx}->{PRSELA} / 1000000;
  $cr        = $cursor{$sql_idx}->{PRSQRY};
  $cu        = $cursor{$sql_idx}->{PRSCU};
  $count     = $cursor{$sql_idx}->{PRSCNT};
  $dsk       = $cursor{$sql_idx}->{PRSDSK};
  $uid       = $cursor{$sql_idx}->{UID};
 
  printf OUTPUT ("%s\n\n", '*' x 155);
 
  if ($base_version > 11.0)
  {
    printf OUTPUT ("SQL Id: %-15s    Plan Hash: %15s \n\n", $sql_id, $plh);
  }
  else
  {
    printf OUTPUT ("Addr: %-15s\n\n", $sql_id);
  }
 
  my $long_stmnt = 0;
  foreach $rec (@{$cursor{$sql_idx}->{STATEMENT}})
  {
    if (length($rec) > $columns)
    {
      $long_stmnt = 1;
    }
  }
  if ($long_stmnt)
  {
     $long_stmnt = '';
     foreach $rec (@{$cursor{$sql_idx}->{STATEMENT}})
     {
       $long_stmnt = $long_stmnt . $rec;
     }
     $Text::Wrap::unexpand = 0;
     print OUTPUT  wrap('','', $long_stmnt);   
  }
  else
  {
     foreach $rec (@{$cursor{$sql_idx}->{STATEMENT}})
     {
       print OUTPUT  "$rec\n";
     }
  }

  if ($long_stmnt)
  {
      print OUTPUT  "\n";
  }
  if (defined ($opt_p))
  {
    print OUTPUT "\nTimes shown here are in seconds.\n";
    print OUTPUT  "\nCall     Count         CPU      Elapsed       Disk     Query    Current        Rows\n";
    print OUTPUT  "------- ------  ---------- ------------ ---------- ---------- ----------  ----------\n";
    printf OUTPUT ("Parse   %6d %11.2f %12.2f %10d %10d %10d %11d\n" , $count , $cpu , $ela , $dsk, $cr
                                                       , $cu, 0);
    $cpu       = $cursor{$sql_idx}->{EXECPU} / 1000000;
    $ela       = $cursor{$sql_idx}->{EXEELA} / 1000000;
    $cr        = $cursor{$sql_idx}->{EXEQRY};
    $cu        = $cursor{$sql_idx}->{EXECU};
    $count     = $cursor{$sql_idx}->{EXECNT};
    $dsk       = $cursor{$sql_idx}->{EXEDSK};
    $rows      = $cursor{$sql_idx}->{EXEROW};

    printf OUTPUT ("Execute %6d %11.2f %12.2f %10d %10d %10d %11d\n"
                      , $count
                      , $cpu
                      , $ela
                      , $dsk
                      , $cr
                      , $cu
                      , $rows);
   
    $cpu       = $cursor{$sql_idx}->{FCHCPU} / 1000000;
    $ela       = $cursor{$sql_idx}->{FCHELA} / 1000000;
    $cr        = $cursor{$sql_idx}->{FCHQRY};
    $cu        = $cursor{$sql_idx}->{FCHCU};
    $count     = $cursor{$sql_idx}->{FCHCNT};
    $dsk       = $cursor{$sql_idx}->{FCHDSK};
    $rows      = $cursor{$sql_idx}->{FCHROW};

    printf OUTPUT ("Fetch   %6d %11.2f %12.2f %10d %10d %10d %11d\n" , $count , $cpu , $ela , $dsk, $cr
                                                   , $cu , $rows);
    print OUTPUT  "------- ------  ---------- ------------ ---------- ---------- ----------  ----------\n";
    printf OUTPUT ("Totals  %6d %11.2f %12.2f %10d %10d %10d %11d\n\n" , $count_tot , $cpu_tot
                                                   , $ela_tot , $dsk_tot, $cr_tot
                                                   , $cu_tot , $rows_tot);

    my %stat;
    my $exec_count;
    my $stat = $cursor{$sql_idx}->{STAT};
    %stat = %$stat;
    printf OUTPUT "Library cache misses:";
    printf OUTPUT ("    During parse: %s",$cursor{$sql_idx}->{PRSMIS});
    printf OUTPUT ("  During execute: %s\n\n",$cursor{$sql_idx}->{EXEMIS});
    printf OUTPUT ("Parsing User Id: %s\n",$cursor{$sql_idx}->{UID});
    printf OUTPUT ("Recursive depth: %s\n\n",$cursor{$sql_idx}->{DEP});
    my %row_stat = ();
    print OUTPUT  "\nRow Source Plan ($sql_id):\n";
    my $no_execs = keys %stat;
    my %stat_rows;
    my $count_stat = keys %stat;
   
    foreach $exec_count (sort keys %stat)
    {
      my $stat_array = $stat->{$exec_count};
      my @stat = @$stat_array;
   
      foreach $record (@stat)
      {
        my $row_stat_rec = {
                          PAD_LEN      => 0
                        , FIRST        => 0
                        , TOT          => 0
                        , MIN          => 0
                        , MAX          => 0
                        , OP           => 0
                     };   
        my $id;
        my $pid;
        my $op;
        my $cnt;
        $pid = $id = $op = $cnt = $record;
        $id  =~ s/^.* id=([\d]+) .*/$1/;
        $pid =~ s/^.* pid=([\d]+) .*/$1/;
        $cnt =~ s/^.* cnt=([\d]+) .*/$1/;
        $op  =~ s/^.* op='(.*)'/$1/;
        if (exists ($stat_rows{$id}))
        {
         if ($pid == 0)
         {
           $stat_rows{$id}->{PAD_LEN} = 0;
         }
         else
         {
           $stat_rows{$id}->{PAD_LEN} = $stat_rows{$pid}->{PAD_LEN} + 1;
         }
     
         if ($stat_rows{$id}->{MIN} > $cnt)
         {
           $stat_rows{$id}->{MIN} = $cnt;
         }
     
         if ($stat_rows{$id}->{MAX} < $cnt)
         {
           $stat_rows{$id}->{MAX} = $cnt;
         }
         $stat_rows{$id}->{TOT}  = $stat_rows{$id}->{TOT} + $cnt;
         $stat_rows{$id}->{OP} = $op;
        }
        else
        {
         $stat_rows{$id} = $row_stat_rec;
     
         if ($pid == 0)
         {
           $stat_rows{$id}->{PAD_LEN} = 0;
         }
         else
         {
           $stat_rows{$id}->{PAD_LEN} = $stat_rows{$pid}->{PAD_LEN} + 1;
         }
     
          $stat_rows{$id}->{FIRST} = $cnt;
          $stat_rows{$id}->{MIN}   = $cnt;
          $stat_rows{$id}->{MAX}   = $cnt;
          $stat_rows{$id}->{TOT}   = $cnt / $exec_count;
          $stat_rows{$id}->{OP}    = $op;
        }
    }
   
   
    my $id;
    my $op;
    my $op_str;
    if ($exec_count == $no_execs)
    {
      printf OUTPUT ("\n%-10s %-10s %-10s %-10s %-80s\n",
            'Rows (1st)','Rows (Avg)','Rows (Min)','Rows (Max)','Row Source Operation');      
      printf OUTPUT ("%-10s %-10s %-10s %-10s %-80s\n",
            '~~~~~~~~~~','~~~~~~~~~~','~~~~~~~~~~','~~~~~~~~~~','~' x 111);      
      for $id (sort keys %stat_rows)
      {
          $op     = $stat_rows{$id}->{OP};
   
               printf OUTPUT ("%-10d %-10d %-10d %-10d %s%-80s\n"
              ,$stat_rows{$id}->{FIRST}
              ,$stat_rows{$id}->{TOT} / $count_stat
              ,$stat_rows{$id}->{MIN}
              ,$stat_rows{$id}->{MAX}
              ,' ' x $stat_rows{$id}->{PAD_LEN}
              , $op
              );
      }
    }
   }
  }
  if (defined ($opt_e))
  {
    if ($long_stmnt)
    {
        print OUTPUT "\n";
    }
    my $pad_len = length($plh);
    print OUTPUT  "\nCURSOR $sql_id ($plh) EVENT FIGURES\n";
    print OUTPUT  "=====================================";
    printf OUTPUT ("%s\n",'=' x $pad_len);
    process_events($cursor_rec->{WAITS});
    report_events;
  }
}
printf OUTPUT ("\n%s\n", '*' x 155);
if ( ! $pre_10_2  and $conn_str)
{
  my @tables;
  my %tab_headers    = ();
  my %tables         = ();

  print OUTPUT "\nTABLE DETAILS\n";
  print OUTPUT "=============\n";
  $tab_sql = "$tab_sql $pred";

  exec_sqlplus_sql ($tab_sql, $conn_str, \@tables);
  prep_dataset (\@tables, \%tab_headers, \%tables);
  print_dataset (\%tab_headers, \%tables);

  my @indexes;
  my %idx_headers    = ();
  my %indexes        = ();
  print OUTPUT "\nINDEX DETAILS\n";
  print OUTPUT "=============\n";
  print OUTPUT "\nLF_BLKS_KEY    => AVG_LEAF_BLOCKS_PER_KEY\n";
  print OUTPUT "DATA_BLKS_KEY  => AVG_DATA_BLOCKS_PER_KEY\n";
  print OUTPUT "INST           => INSTANCES\n";
  print OUTPUT "PARTND         => PARTITIONED\n\n";
  $idx_sql = "$idx_sql $pred";

  exec_sqlplus_sql ($idx_sql, $conn_str, \@indexes);
  prep_dataset (\@indexes, \%idx_headers, \%indexes);
  print_dataset (\%idx_headers, \%indexes);
}
printf OUTPUT ("\n*** End of report ***\n");
printf ("\n$prog: Done\n");

Comments