Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Staspack Grapher/Viewer ?

RE: Staspack Grapher/Viewer ?

From: Gesler, Rich <RGesler_at_lexington.com>
Date: Thu, 19 Sep 2002 07:58:39 -0800
Message-ID: <F001.004D3D57.20020919075839@fatcity.com> 





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: ORA1034_at_sbcglobal.net

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gesler, Rich
  INET: RGesler_at_lexington.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 - 10:58:39 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US