DRINK COFFEE AND PLOT AWAY YOUR DATABASE METRICS

How do you do it?

Step1:
Download gnuplot from http://www.gnuplot.info/ to generate your postscriptfile and imageprick to convert your postscript file to gif http://www.imagemagick.org/script/install-source.php
Gnuplot:
Download gnuplot-4.0.0.tar.gz
Move the binaries to your database server
Untar the file ./configure will configure gnuplot for you on your server.
Imagemagick:
gunzip -c ImageMagick.tar.gz tar xvf –
cd ImageMagick-6.?.?
./configure
make
make install

Step2:
Below is an example of a metrics table that records start and end time of a specific load run that is run every evening

LOAD_NAMESTART_TIMEEND_TIMELOAD_DATE
LOAD_NAMESTART_TIMEEND_TIMELOAD_DATE
test4/15/2006 2:20:00 AM4/15/2006 3:00:00 AM4/15/2006
test4/16/2006 2:00:00 AM4/16/2006 3:45:00 AM4/16/2006
test4/14/2006 2:00:00 AM4/14/2006 3:00:00 AM4/14/2006

Step3a:
A small Java stored procedure scheduled as a DBMS job that will read from the table weekly , invokes the unix program gnuplot and generates your plot as an image and emails the plot to the DBA.

Java Stored Procedure called UnixCmd – that will let you execute any host command from the database

1)
--Grant Privileges as SYS to your LOAD_USR
EXEC Dbms_Java.Grant_Permission('LOAD_USR', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
EXEC Dbms_Java.Grant_Permission('LOAD_USR', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('LOAD_USR', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC dbms_java.grant_permission( 'LOAD_USR','SYS:java.io.FilePermission', '/bin/sh', 'execute' )
grant execute on p_UnixCmd to LOAD_USR;

2)
--connect as LOAD_USR
--Create Java Class Named UnixCmd
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED UnixCmd AS
import java.io.*;
public class UnixCmd {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

final Process pr = Runtime.getRuntime().exec(finalCommand);
new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
}
}).start();

new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}

};
/

--Create a stored procedure that calls your Java Class
CREATE OR REPLACE PROCEDURE p_UnixCmd (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'UnixCmd.executeCommand (java.lang.String)';
/


--Test a Unix Cmd call from your stored procedure
SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
p_UnixCmd (p_command => 'touch /tmp/test1.txt');
END;
/

Step3b:-- Now your ready to create the procedure that plots your metrics and send you the metrics via email

CREATE DIRECTORY WRITEDIR
AS '/tmp';

GRANT READ ON DIRECTORY WRITEDIR TO PUBLIC;

GRANT WRITE ON DIRECTORY WRITEDIR TO LOAD_USR;

CREATE OR REPLACE procedure dump_data
is
cursor c1 is
select ( to_char(load_date,'YYYY/MM/DD') ' '(to_date(to_char(end_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')-
to_date(to_char(start_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'))*24*60*60 ) as PLOT from load_metrics1 order by load_date asc;

file_handle UTL_FILE.FILE_TYPE;
file_handle1 UTL_FILE.FILE_TYPE;
i number:=1;
sample_start varchar2(11);
sample_end varchar2(11);
metricsdate date:=sysdate;
BEGIN

file_handle := UTL_FILE.FOPEN('WRITEDIR','LOAD_METRICS.dat','W');

for rec in c1 loop
if (i = 1) then
sample_start:=substr(to_char(rec.PLOT),0,11);
end if;
UTL_FILE.PUT_LINE(file_handle,rec.PLOT);
if (i = c1%rowcount) then
sample_end:=substr(to_char(rec.PLOT),0,11);
end if;
i:=i+1;
end loop;

UTL_FILE.FCLOSE(file_handle);
p_UnixCmd (p_command => '/var/home/oracle/gnuplot.sh');
END dump_data;
/

create or replace procedure dump_data2 as
BEGIN
p_UnixCmd (p_command => '/usr/local/bin/convert /tmp/LOAD_METRICS.ps /tmp/LOAD_METRICS.gif');
p_UnixCmd (p_command => 'mailx -s "Weekly Load Run Report" "xxxxxxxxxxxxxxxxx" < /tmp/LOAD_METRICS.gif'); END; / contents of gnuplot.sh is as follows: gnuplot << !EOF set terminal postscript set output "/tmp/LOAD_METRICS.ps" set title "Load Run Statistics" set xlabel "Date" set ylabel "Duration \n(in s)" set xdata time set timefmt "%Y/%m/%d" plot "/tmp/LOAD_METRICS.dat" using 1:2 with boxes exit !EOF Step4:
As load_usr schedule a dbms_job to exec dump_data and dump_data2 and your resulting gif file will be emailed to you.
above is a sample gif output file obtained as an email attachment

……..now just forward the attachment to those who would like to see your load run statistics.