注册
登录
| 关键词： oinstall 165244 oracle rrd Nov 28 rw 24 23 file |
首先用php写一个rrdtool数据库文件的生产 脚本 #!/usr/bin/php -f <?php 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 where s.SNAP_ID=g.SNAP_ID and g.wait_class!=\'Idle\' and g.TIME_WAITED_MICRO>100000 and s.instance_number=i.instance_number and s.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); ?> 通过运行以上脚本可以生成相关rrd文件 # ll total 3864 -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 Streams_AQ__enqueue_blocked_on_low_memory.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 buffer_busy_waits.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 control_file_parallel_write.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 control_file_sequential_read.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 cursor__pin_S_wait_on_X.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 db_file_scattered_read.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 db_file_sequential_read.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 events_in_waitclass_Other.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__cache_buffers_chains.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__library_cache.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__library_cache_lock.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__redo_writing.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__row_cache_objects.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 latch__shared_pool.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 library_cache_load_lock.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 library_cache_lock.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_buffer_space.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_parallel_write.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sequential_read.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_single_write.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_switch_completion.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sync.rrd -rw-r--r-- 1 oracle oinstall 165244 Nov 24 23:28 os_thread_startup.rrd |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
