prof-cid.pl

Oracle optimizing performance things

#!/usr/bin/perl

# $Header: /home/cvs/cvm-book1/sqltrace/prof-cid.pl,v 1.5 2003/09/12 07:40:38 cvm Exp $
# Cary Millsap ([e-mail])
# Copyright (c) 1999-2003 by Hotsos Enterprises, Ltd. All rights reserved.

# Create a resource profile for a single database call.
# Usage: $0 file.trc

# Requires input of Oracle extended SQL trace data (level 8 or level 12)
# that has been pre-filtered to contain only a single database call (that
# is, a single PARSE, EXEC, FETCH, UNMAP, or SORT UNMAP with no recursive
# children) and the WAIT lines associated with that db call. Example input
# file content:
#
#   WAIT #2: nam='db file sequential read' ela= 0 p1=2 p2=3240 p3=1
#   WAIT #2: nam='db file sequential read' ela= 0 p1=2 p2=3239 p3=1
#   FETCH #2:c=213,e=998,p=2039,cr=100550,cu=5,mis=0,r=0,dep=0,og=4,tim=85264276

use strict;
use warnings;
my $cid;            # cursor id
my %ela;            # $ela{event} contains sum of ela statistics for event
my $sum_ela = 0;    # sum of all ela times across events
my $r = 0;          # response time for database call
my $action = "(?:PARSE|EXEC|FETCH|UNMAP|SORT UNMAP)";
while (<>) {
    if (/^WAIT #(\d+): nam='([^']*)' ela=\s*(\d+)/i) {
        $ela{$2} += $3;
        $sum_ela += $3;
    }
    elsif (/^$action #(\d+):c=(\d+),e=(\d+)/i) {
        $ela{"total CPU"} += $2;
        $r = $3;
    }
    if (!defined $cid) {
        $cid = $1;
    } else {
        die "can't mix data across cursor ids $cid and $1" if $1 != $cid;
    }
}
$ela{"unaccounted-for"} = $r - ($ela{"total CPU"} + $sum_ela);
printf "%9s  %6s  %-40s\n", "Duration", "Pct", "Oracle kernel event";
printf "%8s-  %5s-  %-40s\n", "-"x8, "-"x5, "-"x40;
printf "%8.2fs  %5.1f%%  %-40s\n", $ela{$_}/100, $ela{$_}/$r*100, $_ for sort { $ela{$b} <=> $ela{$a} } keys %ela;
printf "%8s-  %5s-  %-40s\n", "-"x8, "-"x5, "-"x40;
printf "%8.2fs  %5.1f%%  %-40s\n", $r/100, 100, "Total response time";