Here is something I am playing around with. The idea came from Burleson's Statspack
book. It requires Active Perl with DBD/DBI installed. Also MS Graph is used. This
is still a work in progress.
#!C:\Perl\bin\perl.exe -w
#
# rpt_avg_bbw_dy.pl
# Report Average Buffer Busy Wait by Day
# This perl script will produce a graphical
# "Signature" of information.
# This information is obtained from statspack tables
# and takes advantage of perl's Win32 OLE interface
# to Microsoft Graph.
#
use DBI;
use Win32::OLE qw( with in );
use Win32::OLE::Const "Microsoft Graph";
#
# Set Oracle User and Password Information
#
$name = "perfstat";
$passwd = "xxxxx";
$ora_sid = "PROD";
# 1 makes creation process visible. 0 is faster.
my $VISIBLE = 1;
my $iIndex = 0;
#
# Make connection to Database
#
$dbh = DBI->connect("dbi:Oracle:$ora_sid", $name, $passwd)
or die "Cannot connect : $DBI::errstr";
#
# Prepare Statement to query database
#
$sth = $dbh->prepare("select to_char(snap_time,'day') day,
avg(new.buffer_busy_wait-old.buffer_busy_wait) bbw from
perfstat.stats\$buffer_pool_statistics old,
perfstat.stats\$buffer_pool_statistics new,
perfstat.stats\$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = sn.snap_id-1
group by
to_char(snap_time,'day') ")
||die "Can't prepare statement: $DBI::errstr";
$sth->execute ()
||die "Can't execute statement: $DBI::errstr";
while (($day, $bbw) = $sth->fetchrow_array)
{ # loop thru, retrieving data
$Data[$iIndex] = [$day, $bbw];
$iIndex = $iIndex + 1;
}
my %ChartOptions = (
width => 640,
height => 400,
haslegend => 0,
type => xl3DLine,
perspective => 30,
rotation => 20,
autoscaling => 1,
rightangleaxes => 1,
title => "Buffer Busy Wait Signature by Day",
);
my( @CELLS ) = ( 'a'..'zz' );
my $File = "C:\\temp\\bbw_day.gif";
# BEGIN CALLOUT A
# new() method creates an instance of MS Graph's Application object.
# To have a remote machine create the chart (DCOM) then change "MSGraph.Application"
# parameter to an anonymous array ["appserver.mydomain.com","MSGraph.Application"]
my $ChartApp = new Win32::OLE( "MSGraph.Application", "Quit" ) ||
die "Cannot create object\n";
# END CALLOUT A
$ChartApp->{Visible} = $VISIBLE;
# BEGIN CALLOUT B
my $DataSheet = $ChartApp->DataSheet();
my $Chart = $ChartApp->Chart();
# END CALLOUT B
foreach my $Option ( keys( %ChartOptions ) )
{
$Chart->{$Option} = $ChartOptions{$Option};
}
# BEGIN CALLOUT C
my $iTotal = $#Data;
foreach my $iIndex ( 0 .. $iTotal)
{
my $iday = $Data[$iIndex][0];
my $ibbw = $Data[$iIndex][1];
$DataSheet->Range( "$CELLS[$iIndex]0" )->{Value} = $iday;
$DataSheet->Range( "$CELLS[$iIndex]1" )->{Value} = $ibbw;
}
# END CALLOUT C
print "\n";
# Configure the X axis.
if( my $Axis = $Chart->Axes( xlCategory ) )
{
$Axis->{HasMajorGridlines} = 0;
$Axis->{TickLabels}->{orientation} = xlUpward;
with( $Axis->{TickLabels}->{Font},
Name => "Tahoma",
Bold => 0,
Italic => 0
);
}
# Configure the Y axis.
if( my $Axis = $Chart->Axes( xlValue ) )
{
$Axis->{HasMajorGridlines} = 1;
$Axis->{MajorGridlines}->{Border}->{Weight} = 1;
$Axis->{MajorGridlines}->{Border}->{ColorIndex} = 48;
$Axis->{MajorGridlines}->{Border}->{LineStyle} = xlContinuous;
with( $Chart->Axes( xlValue )->{TickLabels}->{Font},
Name => "Tahoma",
Bold => 0,
Italic => 0
);
}
# BEGIN CALLOUT D
# Configure data-point labels.
$Chart->SeriesCollection( 1 )->{HasDataLabels} = 1;
if( my $Labels = $Chart->SeriesCollection(1)->DataLabels() )
{
with( $Labels,
NumberFormat => "#.0",
Type => xlDataLabelsShowValue
);
with( $Labels->{Font},
Name => "Tahoma",
Bold => 0,
Italic => 0,
);
}
if( defined $ChartOptions{title} )
{
$Chart->{HasTitle} = 1;
$Chart->{ChartTitle}->{Text} = $ChartOptions{title};
$Chart->{ChartTitle}->{Font}->{Name} = "Tahoma";
$Chart->{ChartTitle}->{Font}->{Size} = 18;
}
# Remove consecutive redundant data-point labels.
$iTotal = $Chart->SeriesCollection( 1 )->Points()->{Count};
$iIndex = 0;
my $PrevText = "";
foreach my $Point (in( $Chart->SeriesCollection( 1 )->Points()))
{
my $Percent = int( ++$iIndex * 100 / $iTotal );
my $Text = $Point->{DataLabel}->{Text};
$Point->{MarkerStyle} = xlMarkerStyleDot;
$Point->{DataLabel}->{Font}->{Background} = xlBackgroundOpaque;
$Point->{DataLabel}->{Top} -= 12;
$Point->{HasDataLabel} = 0 if( $Text eq $PrevText );
$PrevText = $Text;
print "\rFormatting: $Percent%";
}
# END CALLOUT D
print "\n";
print "Exporting to GIF file: $File\n";
# BEGIN CALLOUT E
$Chart->Export( $File, "GIF", 0 );
# END CALLOUT E
`start "" "$File"`;
# print "Press <Enter> to continue...";
# <STDIN>;
-----Original Message-----
Sent: Thursday, September 19, 2002 10:58 AM
To: Multiple recipients of list ORACLE-L
We use MRTG to produce graphs for data from different sources .... network
stats, hardware stats, database stats.
Maybe you could use it for statspack.
-----Original Message-----
Sent: Thursday, September 19, 2002 8:39 AM
To: Multiple recipients of list ORACLE-L
Does anyone have or know of any utilities, preferably
freeeware or very cheap, that can produce graphs of
the data collected by statspack?
Thanks VERY much in advance.
-walt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gesler, Rich
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
