We can do the whole thing ( data selection / .png file graph generation / sending the .png file via mail ) in a single script. I prefer to divide the work in individual logical scripts for couple of reasons :
1. You can build 1 thing at a time. Make sure 1 logical piece is working before moving on to next
2. Easy to fix problems/bugs.
3. After each piece is working fine, put all of them together and enjoy the daily dose of your system performance stats.
For the objective at hand, let's create 3 main scripts...
1. graph_day.ksh
2. gen_graph_day.ksh
3. send_mail
1. graph_day.ksh would create "db_time_day.lst". The lst would show db time recorded at every snapshot for all the 3 instances
2. gen_graph_day.ksh would run the ploticus sole "pl" utility to create a .png file, say db_time_day.png
3. send_mail would send db_time_day.png file to you ( or to one or many ) via unix "mailx" utility.
graph_day.ksh
Internally calls
o set_oracle_env
o db_time_day.sql
db_time_day.sql outputs "db_time_day.lst". Here are the source code of all.
$ cat set_oracle_env
ORACLE_SID=db1; export ORACLE_SID
ORACLE_BASE=/oracle; export ORACLE_BASE
ORACLE_HOME=/oracle/product/10.2.0/db1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/openv/netbackup/bin
export LD_LIBRARY_PATH
$ cat db_time_day.sql
set feedback off heading off
set pagesize 2000
set linesize 200
set verify off echo off trimspool on
col snap_id format A8
col begin_interval_time_N format A30 trunc
col end_interval_time_N format A30 trunc
col HOST_NAME format A15 trunc
col INSTANCE_NUMBER format 9 heading "I"
col begin_interval_time_N noprint
col end_interval_time_N noprint
spool db_time_day.lst
select to_char(AA.snap_id) snap_id,
to_char(AA.begin_interval_time_N,'hh24:mi') begin_time,
AA.begin_interval_time_N,
AA.end_interval_time_N,
to_char(AA.end_interval_time_N,'hh24:mi') end_time,
round(AA.DB_TIME) DB_TIME_1, round(BB.DB_TIME) DB_TIME_2, round(CC.DB_TIME) DB_TIME_3 from
(
select
A.SNAP_ID
, A.INSTANCE_NUMBER
, C.HOST_NAME
, MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N
, MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N
, (B. VALUE - LAG(B.VALUE,1,0) OVER (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME
from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C
where A.INSTANCE_NUMBER = 1
and A.SNAP_ID=B.SNAP_ID
and A.DBID=B.DBID
And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER
and B.STAT_NAME Like 'DB time'
and A.DBID=C.DBID
and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER
and A.startup_time = C.startup_time
and A.begin_interval_time >= trunc(sysdate) - 1 and A.begin_interval_time < trunc(sysdate)
) AA,
(
select
A.SNAP_ID
, A.INSTANCE_NUMBER
, C.HOST_NAME
, MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N
, MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N
, (B. VALUE - LAG(B.VALUE,1,0) OVER (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME
from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C
where A.INSTANCE_NUMBER = 2
and A.SNAP_ID=B.SNAP_ID
and A.DBID=B.DBID
And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER
and B.STAT_NAME Like 'DB time'
and A.DBID=C.DBID
and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER
and A.startup_time = C.startup_time
and A.begin_interval_time >= trunc(sysdate) - 1 and A.begin_interval_time < trunc(sysdate)
) BB,
(
select
A.SNAP_ID
, A.INSTANCE_NUMBER
, C.HOST_NAME
, MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N
, MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N
, (B. VALUE - LAG(B.VALUE,1,0) OVER (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME
from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C
where A.INSTANCE_NUMBER = 3
and A.SNAP_ID=B.SNAP_ID
and A.DBID=B.DBID
And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER
and B.STAT_NAME Like 'DB time'
and A.DBID=C.DBID
and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER
and A.startup_time = C.startup_time
and A.begin_interval_time >= trunc(sysdate) - 1 and A.begin_interval_time < trunc(sysdate)
) CC
where AA.SNAP_ID = BB.SNAP_ID and
BB.SNAP_ID = CC.SNAP_ID and
AA.begin_interval_time_N >= trunc(sysdate) - 1 + 20/(24*60) order by 3 ;
spool off
$ cat graph_day.ksh
#! /bin/ksh
. $HOME/set_oracle_env
GRAPH_NAME="db_time"
sqlplus dbauser/password << EOH
@${GRAPH_NAME}_day.sql
EOH
exit 0
Now, make sure you get the desired output before proceeding further.
The output of graph_day.ksh ( db_time_day.lst ) should be db time data for all the snapshots taken yesterday. So, if the snapshot interval is 1 hour, you should be seeing 24 rows ; if it is 30 minutes, you should be seeing 48 rows in db_time_day.lst
Note that we are outputting 6 columns in the output - snap_id, begin_time, end_time, db_time_for_inst_1, db_time_for_inst_2, db_time_for_inst_3
Here is sample of output in db_time_day.lst...
31725 08:00 08:30 40 10 6
31726 08:30 09:00 44 10 7
31727 09:00 09:30 50 16 11
31728 09:30 10:00 55 13 11
31729 10:00 10:30 63 12 11
31730 10:30 11:00 64 13 12
31731 11:00 11:30 68 14 12
31732 11:30 12:00 71 16 12
31733 12:00 12:30 78 16 16
31734 12:30 13:00 77 18 11
31735 13:00 13:30 76 18 17
31736 13:30 14:00 71 25 11
31737 14:00 14:30 64 13 10
31738 14:30 15:00 65 15 10
31739 15:00 15:30 78 19 11
31740 15:30 16:00 78 21 16
31741 16:00 16:30 84 33 29
If you noticed, the sql inside db_time_day.sql has more columns in select, but I have disabled the output to keep things neat and tidy. You can enable them to know what the script would be doing. After you are comfortable with the output, put them off again, so that we work with these 6 column data only.
Out of 6 columns in the output, we plan to use 4 columns for the graph.
Column 2 : X-Axis ( Begin time of snap )
Colum 4 : Y-Axis ( DB Time of instance # 1 )
Colum 5 : Y-Axis ( DB Time of instance # 2 )
Colum 6 : Y-Axis ( DB Time of instance # 3 )
So, 1 graph would show DB time of all 3 instances. Sounds interesting! Let's proceed with other parts...
gen_graph_day.ksh
Internally calls
o set_ploticus_env
$ cat set_ploticus_env
export INSTALL_DIR="/home/oracle/dba/setia"
export PLOTICUS_PREFABS="$INSTALL_DIR/pl241linuxi386/prefabs"
PATH=$PATH:$INSTALL_DIR/pl241linuxi386/bin
export PATH
$ cat gen_graph_day.ksh
#! /bin/ksh
. ./set_ploticus_env
pl -prefab chron data=db_time_day.lst -png -o db_time_day.png x=2 y=4 y2=5 y3=6 unittype=time mode=line xscaletype=hh:mi \
name="db1" name2="db2" name3="db3" xscaletype=hh:mi \
linedet2="color=red width=1.5" \
linedet3="color=blue width=1.5" \
fill=skyblue ylbl="DB Time in minutes" ygrid=yes stubfmt="Www" \
legendfmt=singleline \
title="DB Time ( Snap Interval : 30 minutes ) for `date --date="yesterday" +"%a, %d %b %Y"`"
The script above uses 4 columns to generate graph - a png file named db_time_day.png. Time column as X-axis and 3 columns for Y axis. We can build a filled graph or a line graph. I have choosen a mix of both. DB Time for 1st instance is filled line graph and for other instances is simple line graph.
send_mail
$ cat send_mail
uuencode $1 $1 | mailx -s "DB Time graph for `date --date="yesterday" +"%a, %d %b %Y"`" satwindersetia@gmail.com
( NOTE : Change the mail ID to point to yours )
Now, it is time to send the graph to you...
$./send_mail db_time_day.png
If everything goes, well you should be seeing the DB time graph like this in your mail box.
Time to automate the whole thing...
$ cat graph_db_time.ksh
#! /bin/ksh
#########################################################
#Change the directory name as appropriate for your env
#########################################################
GRAPH1="/home/oracle/setia/perf/db_time"
rm ${GRAPH1}/db_time_day.lst
cd $GRAPH1
# Generate the data file
./graph_day.ksh
# Generate the graph
./gen_graph_day.ksh
# Send the graph via mail
./send_mail db_time_day.png
exit 0
