And if you don't want to use MS Graph, you can use
Perl's built-in graphing facility (see Programming
Perl) or gnuplot.
I had developed something similar at a previous gig
but I used gnuplot.
mkb
- "Gesler, Rich" <RGesler_at_lexington.com> wrote:
> 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...";
>
=== message truncated ===
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
INET: mkb125_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Sep 19 2002 - 12:20:42 CDT
