{"id":68,"date":"2009-05-25T18:18:50","date_gmt":"2009-05-25T16:18:50","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=68"},"modified":"2020-08-18T17:00:19","modified_gmt":"2020-08-18T15:00:19","slug":"oracle-capacity-planning-with-rrdtool","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/oracle-capacity-planning-with-rrdtool\/","title":{"rendered":"Oracle capacity planning with RRDTOOL"},"content":{"rendered":"

RRDize everything, chapter 2<\/strong><\/p>\n

Oracle Database Server has the most powerful system catalog that allows to query almost any aspect inside an oracle instance.
\nYou can query many v$<\/em> fixed views at regular intervals and populate many RRD files through rrdtool<\/a>: space usage, wait events. system statistics and so on…<\/p>\n

Since release 10.1 Oracle has introduced Automatic Workload Repository<\/a>, a finer version of old good Statspack.
\nNo matter if you are using AWR or statspack, you can rely on their views to collect data for your RRDs.<\/p>\n

If you are administering a new instance and you haven’t collected its statistics so far, you can query (as example) the DBA_HIST_BG_EVENT_SUMMARY view to gather all AWR data about wait events. Historical views could be useful also to collect historical data once a week rather than query the fixed views every few minutes doing the hard work twice (you and AWR).<\/p>\n

The whole process of gathering performance data and update rrd files can be resumed into the following steps:<\/p>\n

– connect to the database
\n– query the AWR’s views
\n– build and execute an rrdtool update command
\n– check if rrd file exists or create it
\n– update the rrd file<\/p>\n

The less rrdtool update commands you will execute, the better the whole process will perform.
\nDo it in a language you are comfortable with and that supports easily connection descriptors.<\/p>\n

Since I’m very comfortable with php, I did it this way.<\/p>\n

This is a very basilar script that works greatly for me with good performances:<\/p>\n

#!\/usr\/bin\/php -f\r\n< ?php                                         \r\n\r\ndefine('WD','\/opt\/oracle\/awr');\r\n$cs         = $_SERVER['argv'][1];\r\n$user       = 'mymonitoruser';\r\n$pass       = 'mystrongpassword'; \r\n\r\n\/* open a new connection *\/\r\n$ds = oci_connect($user, $pass, $cs)\r\n        or die (\"Cannot connect to Oracle Database \".$cs.\"\\n\");\r\n\r\n\/* setting client nls environment *\/\r\n$sql = \"alter session set nls_timestamp_format='MM\/DD\/YY HH24:MI'\";\r\n$stmt = oci_parse($ds, $sql);\r\noci_execute($stmt);\r\noci_free_statement($stmt);                                         \r\n\r\n\/* create directory that will contain rrds (if not exists) *\/\r\nif(!file_exists(WD.'\/'.$cs))\r\n                mkdir(WD.'\/'.$cs);\r\nif(!file_exists(WD.'\/'.$cs.'\/wait'))\r\n                mkdir(WD.'\/'.$cs.'\/wait');                   \r\n\r\n\/* function to create new RRDs *\/\r\nfunction createRRD($name, $interval, $cs) {\r\n        $hb = $interval*5; \/\/heartbeat\r\n        $cmd=\"rrdtool create \".WD.\"\/\".$cs.\"\/wait\/${name}.rrd -s \".$interval.\" \\\r\n                -b \\\"now -3month\\\" DS:waits:DERIVE:$hb:0:U \\\r\n                DS:mswaited:DERIVE:$hb:0:U \\\r\n                RRA:AVERAGE:0.5:1:1440 RRA:AVERAGE:0.5:30:336 \\\r\n                RRA:AVERAGE:0.5:120:372 RRA:AVERAGE:0.5:720:730 \\\r\n                RRA:MIN:0.5:1:1440 RRA:MIN:0.5:30:336 \\\r\n                RRA:MIN:0.5:120:372 RRA:MIN:0.5:720:730 \\\r\n                RRA:MAX:0.5:1:1440 RRA:MAX:0.5:30:336 \\\r\n                RRA:MAX:0.5:120:372 RRA:MAX:0.5:720:730 \\\r\n                RRA:LAST:0.5:1:1440\";\r\n        \/\/print $cmd.\"\\n\";\r\n        return passthru($cmd);\r\n}                                                                              \r\n\r\n\/* take the snapshot frequency from dba_hist_wr_control\r\n to create the RDD with correct heartbeat value *\/\r\n$sql = 'select extract(hour from snap_interval)*3600 +\r\nextract(minute from snap_interval)*60 as SEED from DBA_HIST_WR_CONTROL';\r\n$stmt = oci_parse($ds, $sql);\r\noci_execute($stmt);\r\n$row = oci_fetch_assoc($stmt);\r\n$interval = $row['SEED'];\r\nunset($row);\r\noci_free_statement($stmt);                                              \r\n\r\n\/* statement definition that will collect\r\n all snapshots for a certain wait event with more than\r\n a certain amonut of time waited.\r\n Gathering ALL EVENTS could be time consuming and useless.\r\n I fetch rows ordered by event_name rather\r\n then by date because I can update many values\r\n into the same rrd with very few rrdupdate commands\r\n*\/\r\n$sql = 'select s.END_INTERVAL_TIME END_INTERVAL_TIME,\r\n    g.EVENT_NAME, g.WAIT_CLASS, g.TOTAL_WAITS,\r\n    round(g.TIME_WAITED_MICRO\/1000) MS\r\n  from DBA_HIST_SNAPSHOT s,\r\n   dba_hist_bg_event_summary g,\r\n   v$instance i\r\n where s.SNAP_ID=g.SNAP_ID and g.wait_class!=\\'Idle\\'\r\n  and g.TIME_WAITED_MICRO>100000\r\n  and s.instance_number=i.instance_number\r\n  and s.instance_number=g.instance_number\r\n order by 2,1';                                      \r\n\r\n\/* default prefetch size (148) matches default snapshot retention (24hx7dd) *\/\r\n$stmt = oci_parse($ds, $sql);\r\noci_set_prefetch($stmt, 148);\r\noci_execute($stmt);\r\n\r\n$i=0;\r\n$oldevent=\"\";\r\nwhile ($row = oci_fetch_assoc($stmt)) {\r\n        if ($oldevent != $row['EVENT_NAME']) {\r\n                \/\/NEW EVENT DETECTED: WILL START A NEW UPDATE CMD\r\n                if ($i != 0 && !empty($cmd)) {\r\n                        \/* not the first occurrence,\r\n                         I bet there's something in my buffer *\/\r\n                        passthru($cmd);\r\n                }\r\n                $cleanName = preg_replace (\"([^[:alnum:]_-])\",\"_\",$row['EVENT_NAME']);\r\n                \/\/ if there is no rrd for this event, I create a new one\r\n                if (!file_exists(WD.\"\/\".$cs.\"\/wait\/${cleanName}.rrd\")) {\r\n                        createRRD($cleanName, $interval, $cs);\r\n                }\r\n                \/*\r\n                * I initialize a new update command. This string act as a buffer: I append many\r\n                * values to be updated so I'll update many values in a single command line:\r\n                * less forks of rrdtool and less file opens: the whole update process has an\r\n                * enormous improvement.\r\n                *\/\r\n                $precmd=\"rrdtool update \".WD.\"\/\".$cs.\"\/wait\/${cleanName}.rrd \";\r\n                $lastcmd=\"rrdtool info \".WD.\"\/\".$cs.\"\/wait\/${cleanName}.rrd\".\r\n                        \"| grep last_update | awk '{print \\$NF}'\";\r\n                $last=trim(`$lastcmd`);\r\n                printf (\"%s - %s - last: %d\\n\", $row['EVENT_NAME'], $cleanName, $last);\r\n                $i=0;\r\n                $cmd=$precmd;\r\n                $oldevent=$row['EVENT_NAME'];\r\n        }\r\n        $time=strtotime($row['END_INTERVAL_TIME']);\r\n        \/\/print \"time: \".$time.\"  last: \".$last.\"\\n\";\r\n        if ( $time > $last ) {\r\n                $cmd.=\" \".$time.\":\".$row['TOTAL_WAITS'].\":\".$row['MS'];\r\n                $i++;\r\n        }\r\n        if ($i >= 40) {\r\n                \/\/ when I reach 40 values per commandline I force\r\n                \/\/ the update: next loop will reinitialize a new commandline.\r\n                passthru($cmd);\r\n                $cmd=$precmd;\r\n                $i=0;\r\n        }\r\n        unset($row);\r\n\r\n}\r\nif ($i != 0) {\r\n        \/* one more update pending in my buffer *\/\r\n        passthru($cmd);\r\n}\r\noci_free_statement($stmt);\r\noci_close($ds);\r\n?><\/pre>\n
Depending on how many different wait events you have, you’ll have a certain number of rrd files:<\/p>\n
# ls -l\r\ntotal 3864\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 Streams_AQ__enqueue_blocked_on_low_memory.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 20 08:18 buffer_busy_waits.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 control_file_parallel_write.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 control_file_sequential_read.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 Apr 30 10:12 cursor__pin_S_wait_on_X.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 db_file_scattered_read.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 db_file_sequential_read.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 events_in_waitclass_Other.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 latch__cache_buffers_chains.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 latch__library_cache.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 11 13:22 latch__library_cache_lock.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 20 08:18 latch__redo_writing.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 latch__row_cache_objects.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 latch__shared_pool.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 library_cache_load_lock.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 Apr 15 13:17 library_cache_lock.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 log_buffer_space.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 log_file_parallel_write.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 log_file_sequential_read.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 log_file_single_write.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 log_file_switch_completion.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 11 13:22 log_file_sync.rrd\r\n-rw-r--r-- 1 ludovico ludovico 165304 May 25 15:00 os_thread_startup.rrd<\/pre>\n
As you can see, they are not so big…<\/p>\n
Once you have your data in rrd files, it’s quite simple to script even complex plots with several datasources. Everything depends on the results you want.
\nThis script stack all my wait events for a certain instance: it takes the directory containing all the rrds as first argument and the number of hours we want to be plotted as second argument:<\/p>\n
cs=$1\r\nhours=${2:-148}\r\n\r\neventlist=`ls $cs\/wait\/*rrd`\r\n\r\ncolors[1]=\"#000000\"\r\ncolors[2]=\"#000055\"\r\ncolors[3]=\"#0000aa\"\r\ncolors[4]=\"#0000ff\"\r\ncolors[5]=\"#550055\"\r\ncolors[6]=\"#aa00aa\"\r\ncolors[7]=\"#ff00ff\"\r\ncolors[8]=\"#550000\"\r\ncolors[9]=\"#aa0000\"\r\ncolors[10]=\"#ff0000\"\r\ncolors[11]=\"#555500\"\r\ncolors[12]=\"#aaaa00\"\r\ncolors[13]=\"#ffff00\"\r\ncolors[14]=\"#005500\"\r\ncolors[15]=\"#00aa00\"\r\ncolors[16]=\"#00ff00\"\r\ncolors[17]=\"#005555\"\r\ncolors[18]=\"#00aaaa\"\r\ncolors[19]=\"#00ffff\"\r\ncolors[20]=\"#555555\"\r\ncolors[21]=\"#aaaaaa\"\r\n\r\ni=0\r\n\r\nfor event in $eventlist ; do\r\n        if [ $i -eq 0 ] ; then\r\n                end=`rrdtool info $event | grep last_update | awk '{print $NF}'`\r\n                end=`rrdtool info $cs\/wait\/control_file_parallel_write.rrd | grep last_update | awk '{print $NF}'`\r\n                cmd=\"rrdtool graph - -s end-${hours}hours -e $end  -v \\\"milliseconds waited\\\" -l 0 -w 640 -h 240 -t \\\"$cs WAIT PROFILE\\\"\"\r\n                i=$(($i+1))\r\n        fi\r\n        color=${colors[$i]}\r\n        echo $color\r\n        evname=`basename $event | sed -e s\/\\.rrd\\$\/\/`\r\n        cmd=\"$cmd  DEF:$evname=$event:mswaited:AVERAGE\"\r\n        cmd=\"$cmd  AREA:${evname}${color}:\"$evname\":STACK\"\r\n        i=$(($i+1))\r\n        if [ $i -eq 20 ] ; then\r\n                i=1\r\n        fi\r\ndone\r\n        cmd=\"$cmd  |display \/dev\/input\"\r\n        echo $cmd\r\n        eval $cmd\r\nexit<\/pre>\n
The resulting command is very long:<\/p>\n
rrdtool graph - -s end-148hours -e 1243252800 \\\r\n -v \"milliseconds waited\" -l 0 -w 640 -h 240 -t \"mydb WAIT PROFILE\"\\\r\n DEF:Streams_AQ__enqueue_blocked_on_low_memory=mydb\/wait\/Streams_AQ__enqueue_blocked_on_low_memory.rrd:mswaited:AVERAGE \\\r\n AREA:Streams_AQ__enqueue_blocked_on_low_memory#000000:Streams_AQ__enqueue_blocked_on_low_memory:STACK\\\r\n DEF:buffer_busy_waits=mydb\/wait\/buffer_busy_waits.rrd:mswaited:AVERAGE \\\r\n AREA:buffer_busy_waits#000055:buffer_busy_waits:STACK\\\r\n DEF:control_file_parallel_write=mydb\/wait\/control_file_parallel_write.rrd:mswaited:AVERAGE \\\r\n AREA:control_file_parallel_write#0000aa:control_file_parallel_write:STACK\\\r\n DEF:control_file_sequential_read=mydb\/wait\/control_file_sequential_read.rrd:mswaited:AVERAGE \\\r\n AREA:control_file_sequential_read#0000ff:control_file_sequential_read:STACK\\\r\n DEF:cursor__pin_S_wait_on_X=mydb\/wait\/cursor__pin_S_wait_on_X.rrd:mswaited:AVERAGE \\\r\n AREA:cursor__pin_S_wait_on_X#550055:cursor__pin_S_wait_on_X:STACK\\\r\n DEF:db_file_scattered_read=mydb\/wait\/db_file_scattered_read.rrd:mswaited:AVERAGE \\\r\n AREA:db_file_scattered_read#aa00aa:db_file_scattered_read:STACK\\\r\n DEF:db_file_sequential_read=mydb\/wait\/db_file_sequential_read.rrd:mswaited:AVERAGE \\\r\n AREA:db_file_sequential_read#ff00ff:db_file_sequential_read:STACK\\\r\n DEF:events_in_waitclass_Other=mydb\/wait\/events_in_waitclass_Other.rrd:mswaited:AVERAGE \\\r\n AREA:events_in_waitclass_Other#550000:events_in_waitclass_Other:STACK\\\r\n DEF:latch__cache_buffers_chains=mydb\/wait\/latch__cache_buffers_chains.rrd:mswaited:AVERAGE \\\r\n AREA:latch__cache_buffers_chains#aa0000:latch__cache_buffers_chains:STACK\\\r\n DEF:latch__library_cache=mydb\/wait\/latch__library_cache.rrd:mswaited:AVERAGE \\\r\n AREA:latch__library_cache#ff0000:latch__library_cache:STACK\\\r\n DEF:latch__library_cache_lock=mydb\/wait\/latch__library_cache_lock.rrd:mswaited:AVERAGE \\\r\n AREA:latch__library_cache_lock#555500:latch__library_cache_lock:STACK\\\r\n DEF:latch__redo_writing=mydb\/wait\/latch__redo_writing.rrd:mswaited:AVERAGE \\\r\n AREA:latch__redo_writing#aaaa00:latch__redo_writing:STACK\\\r\n DEF:latch__row_cache_objects=mydb\/wait\/latch__row_cache_objects.rrd:mswaited:AVERAGE \\\r\n AREA:latch__row_cache_objects#ffff00:latch__row_cache_objects:STACK\\\r\n DEF:latch__shared_pool=mydb\/wait\/latch__shared_pool.rrd:mswaited:AVERAGE \\\r\n AREA:latch__shared_pool#005500:latch__shared_pool:STACK\\\r\n DEF:library_cache_load_lock=mydb\/wait\/library_cache_load_lock.rrd:mswaited:AVERAGE \\\r\n AREA:library_cache_load_lock#00aa00:library_cache_load_lock:STACK\\\r\n DEF:library_cache_lock=mydb\/wait\/library_cache_lock.rrd:mswaited:AVERAGE \\\r\n AREA:library_cache_lock#00ff00:library_cache_lock:STACK\\\r\n DEF:log_buffer_space=mydb\/wait\/log_buffer_space.rrd:mswaited:AVERAGE \\\r\n AREA:log_buffer_space#005555:log_buffer_space:STACK\\\r\n DEF:log_file_parallel_write=mydb\/wait\/log_file_parallel_write.rrd:mswaited:AVERAGE \\\r\n AREA:log_file_parallel_write#00aaaa:log_file_parallel_write:STACK\\\r\n DEF:log_file_sequential_read=mydb\/wait\/log_file_sequential_read.rrd:mswaited:AVERAGE \\\r\n AREA:log_file_sequential_read#00ffff:log_file_sequential_read:STACK\\\r\n DEF:log_file_single_write=mydb\/wait\/log_file_single_write.rrd:mswaited:AVERAGE \\\r\n AREA:log_file_single_write#000000:log_file_single_write:STACK\\\r\n DEF:log_file_switch_completion=mydb\/wait\/log_file_switch_completion.rrd:mswaited:AVERAGE \\\r\n AREA:log_file_switch_completion#000055:log_file_switch_completion:STACK\\\r\n DEF:log_file_sync=mydb\/wait\/log_file_sync.rrd:mswaited:AVERAGE \\\r\n AREA:log_file_sync#0000aa:log_file_sync:STACK\\\r\n DEF:os_thread_startup=mydb\/wait\/os_thread_startup.rrd:mswaited:AVERAGE \\\r\n AREA:os_thread_startup#0000ff:os_thread_startup:STACK |display \/dev\/input<\/pre>\n
This is the resulting graph:
\n\"Graph<\/a><\/p>\n
OHHHHHHHHHHHH COOOOL!!!<\/strong>
\n\ud83d\ude09<\/p>\n
Any comment is appreciated! thanks<\/p>\n","protected":false},"excerpt":{"rendered":"
RRDize everything, chapter 2 Oracle Database Server has the most powerful system catalog that allows to query almost any aspect inside an oracle instance. You can query many v$ fixed views at regular intervals and populate many RRD files through … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,6],"tags":[12,30,9,22,286,35,13],"class_list":["post-68","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-perf","tag-awr","tag-capacity-planning","tag-oracle","tag-oracle-database","tag-perf","tag-rrdtool","tag-workload"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":8,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"predecessor-version":[{"id":1991,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/68\/revisions\/1991"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}