Generating graphs - basics

Recently, we had a requirement to monitor our database performance on a regular basis. We ran some periodical SQLs daily and sent the spool output to our mailing client ( Microsoft Outlook ). This worked great and it did help in monitoring our DBs, we wanted to improve further. We took the output to Excel and generated cool graphs - for single instance as well as RAC. But generating graphs using Excel involves good deal of clicks/copy-paste etc...Won't it be cool, if we can get performance graphs to out outlook mail? Post few hours of R&D, we got it working. Here is the know-how.

Step 1 : Download ploticus from
http://ploticus.sourceforge.net

Since I had 64 bit linux on play with, downloaded pl241linuxi386.tar into my directory /home/oracle/dba/setia and then untar it. It created pl241linuxi386 sub-directory and placed all its scripts/file inside the direcoty.

Step 2 : Setup the env
export INSTALL_DIR="/home/oracle/dba/setia"
export PLOTICUS_PREFABS="$INSTALL_DIR/pl241linuxi386/prefabs"
PATH=$PATH:$INSTALL_DIR/pl241linuxi386/bin
export PATH

Step 3 : Create a sample data file, say db_time_daily.txt

$ cat db_time_daily.txt

11/07/26 100

11/07/27 150

11/07/28 60

11/07/29 180

11/07/30 250

11/07/31 100

11/08/01 80

11/08/02 300

11/08/03 60

11/08/04 80

11/08/05 90

11/08/06 100

11/08/07 60

11/08/08 101

Step 4 : Generate graph file. The file would have .png extension.
pl -prefab chron data=db_time_daily.txt -png -o mygraph.png x=1 y=2 datefmt=yy/mm/dd xinc="1 day" \
mode=line linedet="color=green" title="Avg DB Time per day"

The above command would output mygraph.png file.
As you can see, we ran pl command, passing tons of parameters which tells what type of graph we want, from where to pull up X-axis data / Y-axis data, format of the date in the data and what type of graph we want and title of the graph. pl is an executable and hence you cannot see how it works, but can enjoy the wonderful graphs it can offer you.

Step 5 : Mail the graph to yourself ( or to any no. of mail IDs )
uuencode mygraph.png mygraph.png mailx -s "Avg. DB Time" <your-mail-id>


U should be seeing mygraph.png file in your outlook. If you double-click, you should be seeing the graph