Use rrdtool to customize plotting to monitor Oracle databases

In conjunction with this article on connecting PHP to the Oracle database, this time, combined with the rrdtool drawing tool, to draw some custom monitoring charts, although Oracle's EM has been perfect, however, you may not be able to monitor issues that are user-defined or concerned with the Business Environment Based on your business needs. In this case, you need to do it yourself.

First, use php to write a production script for the rrdtool database file.

1. #! /Usr/bin/php-f
2. <? Php
3. Define ('wd ','/u01/app/oracle/awr ');
4. $ Cs = $ _ SERVER ['argv'] [1];
5. $ User = 'system ';
6. $ Pass = 'oracle ';
7. /* Open a new connection */
8. $ Ds = oci_connect ($ user, $ pass, $ cs)
9. Or die ("Cannot connect to Oracle Database". $ cs. "\ n ");
10. /* Setting client nls environment */
11. $ SQL = "alter session set nls_timestamp_format = 'Mm/DD/YY HH24: Mi '";
12. $ Stmt = oci_parse ($ ds, $ SQL );
13. Oci_execute ($ stmt );
14. Oci_free_statement ($ stmt );
15. /* Create directory that will contain in rrds (if not exists )*/
16. If (! File_exists (WD. '/'. $ cs ))
17. Mkdir (WD. '/'. $ cs );
18. If (! File_exists (WD. '/'. $ cs. '/wait '))
19. Mkdir (WD. '/'. $ cs. '/wait ');
20. /* Function to create new RRDs */
21. Function createRRD ($ name, $ interval, $ cs ){
22. $ Hb = $ interval * 5; // heartbeat
23. $ Cmd = "rrdtool create". WD. "/". $ cs. "/wait/$ {name}. rrd-s". $ interval ."\
24. -B \ "now-3month \" DS: waits: DERIVE: $ hb: 0: U \
25. DS: mswaited: DERIVE: $ hb: 0: U \
26. RRA: AVERAGE: 0.5: 1: 1440 RRA: AVERAGE: 0.5: 30: 336 \
27. RRA: AVERAGE: 0.5: 120: 372 RRA: AVERAGE: 0.5: 720: 730 \
28. RRA: MIN: 0.5: 1: 1440 RRA: MIN: 0.5: 30: 336 \
29. RRA: MIN: 0.5: 120: 372 RRA: MIN: 0.5: 720: 730 \
30. RRA: MAX: 0.5: 1: 1440 RRA: MAX: 0.5: 30: 336 \
31. RRA: MAX: 0.5: 120: 372 RRA: MAX: 0.5: 720: 730 \
32. RRA: LAST: 0.5: 1: 1440 ";
33. // Print $ cmd. "\ n ";
34. Return passthru ($ cmd );
35. }
36. /* Take the snapshot frequency from dba_hist_wr_control
37. To create the RDD with correct heartbeat value */
38. $ SQL = 'select extract (hour from snap_interval) * 3600 +
39. Extract (minute from snap_interval) * 60 as SEED from DBA_HIST_WR_CONTROL ';
40. $ Stmt = oci_parse ($ ds, $ SQL );
41. Oci_execute ($ stmt );
42. $ Row = oci_fetch_assoc ($ stmt );
43. $ Interval = $ row ['seed'];
44. Unset ($ row );
45. Oci_free_statement ($ stmt );
46. /* Statement definition that will collect
47. All snapshots for a certain wait event with more
48. A certain amonut of time waited.
49. Gathering all events cocould be time consuming and useless.
50. I fetch rows ordered by event_name rather
51. Then by date because I can update your values
52. Into the same rrd with very few rrdupdate commands
53. */
54. $ SQL = 'select s. END_INTERVAL_TIME,
55. G. EVENT_NAME, g. WAIT_CLASS, g. TOTAL_WAITS,
56. Round (g. TIME_WAITED_MICRO/1000) MS
57. From DBA_HIST_SNAPSHOT s,
58. Dba_hist_bg_event_summary g,
59. V $ instance I
60. Where s. SNAP_ID = g. SNAP_ID and g. wait_class! = \ 'Idle \'
61. And g. TIME_WAITED_MICRO> 100000
62. And s. instance_number = I. instance_number
63. And s. instance_number = g. instance_number
64. Order by 2, 1 ';
65. /* Default prefetch size (148) matches default snapshot retention (24hx7dd )*/
66. $ Stmt = oci_parse ($ ds, $ SQL );
67. Oci_set_prefetch ($ stmt, 148 );
68. Oci_execute ($ stmt );
69. $ I = 0;
70. $ Oldevent = "";
71. While ($ row = oci_fetch_assoc ($ stmt )){
72. If ($ oldevent! = $ Row ['event _ name']) {
73. // New event detected: WILL START A NEW UPDATE CMD
74. If ($ I! = 0 & amp ;! Empty ($ cmd )){
75. /* Not the first occurrence,
76. I bet there's something in my buffer */
77. Passthru ($ cmd );
78. }
79. $ CleanName = preg_replace ("([^ [: alnum:] _-])", "_", $ row ['event _ name']);
80. // If there is no rrd for this event, I create a new one
81. If (! File_exists (WD. "/". $ cs. "/wait/$ {cleanName}. rrd ")){
82. CreateRRD ($ cleanName, $ interval, $ cs );
83. }
84. /*
85. * I initialize a new update command. This string act as a buffer: I append signature
86. * Values to be updated so I'll update them values in a single command line:
87. * Less forks of rrdtool and less file opens: the whole update process has
88. * Enormous improvement.
89. */
90. $ Precmd = "rrdtool update". WD. "/". $ cs. "/wait/$ {cleanName}. rrd ";
91. $ Lastcmd = "rrdtool info". WD. "/". $ cs. "/wait/$ {cleanName}. rrd ".
92. "| Grep last_update | awk '{print \ $ NF }'";
93. $ Last = trim ('$ lastcmd ');
94. Printf ("% s-last: % d \ n", $ row ['event _ name'], $ cleanName, $ last );
95. $ I = 0;
96. $ Cmd = $ precmd;
97. $ Oldevent = $ row ['event _ name'];
98. }
99. $ Time = strtotime ($ row ['end _ INTERVAL_TIME ']);
100. // Print "time:". $ time. "last:". $ last. "\ n ";
101. If ($ time & gt; $ last ){
102. $ Cmd. = "". $ time. ":". $ row ['total _ WAITS ']. ":". $ row ['Ms'];
103. $ I ++;
104. }
105. If ($ I & gt; = 40 ){
106. // When I reach 40 values per commandline I force
107. // The update: next loop will reinitialize a new commandline.
108. Passthru ($ cmd );
109. $ Cmd = $ precmd;
110. $ I = 0;
111. }
112. Unset ($ row );
113. }
114. If ($ I! = 0 ){
115. /* One more update pending in my buffer */
116. Passthru ($ cmd );
117. }
118. Oci_free_statement ($ stmt );
119. Oci_close ($ ds );
120. ?>

Run the preceding script to generate the rrd file.

1. # Ll
2. Total 3864
3. -Rw-r -- 1 oracle oinstall 165244 Nov 24 Streams_AQ _ enqueue_blocked_on_low_memory.rrd
4. -Rw-r -- 1 oracle oinstall 165244 Nov 24 buffer_busy_waits.rrd
5. -Rw-r -- 1 oracle oinstall 165244 Nov 24 control_file_parallel_write.rrd
6. -Rw-r -- 1 oracle oinstall 165244 Nov 24 23:28 control_file_sequential_read.rrd
7. -Rw-r -- 1 oracle oinstall 165244 Nov 24 cursor _ pin_S_wait_on_X.rrd
8. -Rw-r -- 1 oracle oinstall 165244 Nov 24 23:28 db_file_scattered_read.rrd
9. -Rw-r -- 1 oracle oinstall 165244 Nov 24 23:28 db_file_sequential_read.r
10. -Rw-r -- 1 oracle oinstall 165244 Nov 24 events_in_waitclass_Other.rrd
11. -Rw-r -- 1 oracle oinstall 165244 Nov 24 latch _ cache_buffers_chains.rrd
12. -Rw-r -- 1 oracle oinstall 165244 Nov 24 latch _ library_cache.rrd
13. -Rw-r -- 1 oracle oinstall 165244 Nov 24 latch _ library_cache_lock.rrd
14. -Rw-r -- 1 oracle oinstall 165244 Nov 24 latch _ redo_writing.rrd
15. -Rw-r -- 1 oracle oinstall 165244 Nov 24 latch _ row_cache_objects.rrd
16. -Rw-r -- 1 oracle oinstall 165244 Nov 24 latch _ shared_pool.rrd
17. -Rw-r -- 1 oracle oinstall 165244 Nov 24 library_cache_load_lock.rrd
18. -Rw-r -- 1 oracle oinstall 165244 Nov 24 library_cache_lock.rrd
19. -Rw-r -- 1 oracle oinstall 165244 Nov 24 log_buffer_space.rrd
20. -Rw-r -- 1 oracle oinstall 165244 Nov 24 log_file_parallel_write.rrd
21. -Rw-r -- 1 oracle oinstall 165244 Nov 24 23:28 log_file_sequential_read.rrd
22. -Rw-r -- 1 oracle oinstall 165244 Nov 24 log_file_single_write.rrd
23. -Rw-r -- 1 oracle oinstall 165244 Nov 24 log_file_switch_completion.rrd
24. -Rw-r -- 1 oracle oinstall 165244 Nov 24 log_file_sync.rrd
25. -Rw-r -- 1 oracle oinstall 165244 Nov 24 OS _thread_startup.rrd

Use the rrdtool command to plot as follows: