rrdtool php 绘图,使用rrdtool自定义绘图监控Oracle数据库

#!/usr/bin/php -f

define('WD','/u01/app/oracle/awr');

$cs= $_SERVER['argv'][1];

$user='system';

$pass='oracle';

/* open a new connection */

$ds=oci_connect($user, $pass, $cs)

or die ("Cannot connect to Oracle Database ".$cs."\n");

/* setting client nls environment */

$sql="alter session set nls_timestamp_format='MM/DD/YY HH24:MI'";

$stmt=oci_parse($ds, $sql);

oci_execute($stmt);

oci_free_statement($stmt);

/* create directory that will contain rrds (if not exists) */

if(!file_exists(WD.'/'.$cs))

mkdir(WD.'/'.$cs);

if(!file_exists(WD.'/'.$cs.'/wait'))

mkdir(WD.'/'.$cs.'/wait');

/* function to create new RRDs */

function createRRD($name, $interval, $cs) {

$hb= $interval*5; //heartbeat

$cmd="rrdtool create ".WD."/".$cs."/wait/${name}.rrd -s ".$interval." \

-b \"now -3month\" DS:waits:DERIVE:$hb:0:U \

DS:mswaited:DERIVE:$hb:0:U \

RRA:AVERAGE:0.5:1:1440 RRA:AVERAGE:0.5:30:336 \

RRA:AVERAGE:0.5:120:372 RRA:AVERAGE:0.5:720:730 \

RRA:MIN:0.5:1:1440 RRA:MIN:0.5:30:336 \

RRA:MIN:0.5:120:372 RRA:MIN:0.5:720:730 \

RRA:MAX:0.5:1:1440 RRA:MAX:0.5:30:336 \

RRA:MAX:0.5:120:372 RRA:MAX:0.5:720:730 \

RRA:LAST:0.5:1:1440";

//print $cmd."\n";

return passthru($cmd);

}

/* take the snapshot frequency from dba_hist_wr_control

to create the RDD with correct heartbeat value */

$sql= 'select extract(hour from snap_interval)*3600 +

extract(minute from snap_interval)*60 as SEED from DBA_HIST_WR_CONTROL';

$stmt=oci_parse($ds, $sql);

oci_execute($stmt);

$row=oci_fetch_assoc($stmt);

$interval= $row['SEED'];

unset($row);

oci_free_statement($stmt);

/* statement definition that will collect

all snapshots for a certain wait event with more than

a certain amonut of time waited.

Gathering ALL EVENTS could be time consuming and useless.

I fetch rows ordered by event_name rather

then by date because I can update many values

into the same rrd with very few rrdupdate commands

*/

$sql= 'select s.END_INTERVAL_TIME END_INTERVAL_TIME,

g.EVENT_NAME, g.WAIT_CLASS, g.TOTAL_WAITS,

round(g.TIME_WAITED_MICRO/1000) MS

from DBA_HIST_SNAPSHOT s,

dba_hist_bg_event_summary g,

v$instance i

wheres.SNAP_ID=g.SNAP_ID and g.wait_class!=\'Idle\'

and g.TIME_WAITED_MICRO>100000

ands.instance_number=i.instance_number

ands.instance_number=g.instance_number

order by 2,1';

/* default prefetch size (148) matches default snapshot retention (24hx7dd) */

$stmt=oci_parse($ds, $sql);

oci_set_prefetch($stmt, 148);

oci_execute($stmt);

$i=0;

$oldevent="";

while ($row=oci_fetch_assoc($stmt)) {

if ($oldevent != $row['EVENT_NAME']) {

//NEW EVENT DETECTED: WILL START A NEW UPDATE CMD

if ($i != 0 && !empty($cmd)) {

/* not the first occurrence,

I bet there's something in my buffer */

passthru($cmd);

}

$cleanName=preg_replace("([^[:alnum:]_-])","_",$row['EVENT_NAME']);

// if there is no rrd for this event, I create a new one

if (!file_exists(WD."/".$cs."/wait/${cleanName}.rrd")) {

createRRD($cleanName, $interval, $cs);

}

/*

* I initialize a new update command. This string act as a buffer: I append many

* values to be updated so I'll update many values in a single command line:

* less forks of rrdtool and less file opens: the whole update process has an

* enormous improvement.

*/

$precmd="rrdtool update ".WD."/".$cs."/wait/${cleanName}.rrd ";

$lastcmd="rrdtool info ".WD."/".$cs."/wait/${cleanName}.rrd".

"| grep last_update | awk '{print \$NF}'";

$last=trim(`$lastcmd`);

printf ("%s - %s - last: %d\n", $row['EVENT_NAME'], $cleanName, $last);

$i=0;

$cmd=$precmd;

$oldevent=$row['EVENT_NAME'];

}

$time=strtotime($row['END_INTERVAL_TIME']);

//print "time: ".$time." last: ".$last."\n";

if ( $time>$last ) {

$cmd.=" ".$time.":".$row['TOTAL_WAITS'].":".$row['MS'];

$i++;

}

if ($i>= 40) {

// when I reach 40 values per commandline I force

// the update: next loop will reinitialize a new commandline.

passthru($cmd);

$cmd=$precmd;

$i=0;

}

unset($row);

}

if ($i != 0) {

/* one more update pending in my buffer */

passthru($cmd);

}

oci_free_statement($stmt);

oci_close($ds);

?>