This project as a prerequisite needs the [Musas](Musas.md) project running - to provide the raw AWR / Statspack reports. As we have those reports it is relatively easy to do some draws.

This project was born from the need to somehow show developers a nice picture that will tell them more than the raw numbers.

Oracle is changing the AWR report format with every major release, that is why there are different projects.

Old fashioned Statspack, have not used since Oracle 9.

# mkdir /var/www/html/oi_defcon_statspack
# chown orainf:orainf /var/www/html/oi_defcon_statspack

$ cd /var/www/html/
$ svn checkout https://github.com/rbogusze/oracleinfrastructure/trunk/oi_defcon_statspack
$ cd oi_defcon_statspack
$ tar xvzf jpgraph-1.20.5.tar.gz

# mkdir /var/www/html/oi_defcon_awr
# chown orainf:orainf /var/www/html/oi_defcon_awr

$ cd /var/www/html/
$ svn checkout https://github.com/rbogusze/oracleinfrastructure/trunk/oi_defcon_awr
$ cd oi_defcon_awr
$ tar xvzf jpgraph-1.20.5.tar.gz

# mkdir /var/www/html/oi_defcon_awr11
# chown orainf:orainf /var/www/html/oi_defcon_awr11

$ cd /var/www/html/
$ svn checkout https://github.com/rbogusze/oracleinfrastructure/trunk/oi_defcon_awr11
$ cd oi_defcon_awr11
$ tar xvzf jpgraph-1.20.5.tar.gz

# mkdir /var/www/html/oi_defcon_awr12
# chown orainf:orainf /var/www/html/oi_defcon_awr12

$ cd /var/www/html/
$ svn checkout https://github.com/rbogusze/oracleinfrastructure/trunk/oi_defcon_awr12
$ cd oi_defcon_awr12
$ tar xvzf jpgraph-1.20.5.tar.gz

Install PHP

# yum install php53 php53-gd
# /etc/init.d/httpd restart

Enable apache user to access the /home/orainf/scripto/php/my_library.php

# chmod g+rx /home/orainf/
# chmod o+rx /home/orainf/

# mkdir -p /usr/X11R6/lib/X11/fonts/truetype
# cp /var/www/html/oi_defcon_aw*/arial.ttf /usr/X11R6/lib/X11/fonts/truetype/

Open the browser and direct it to defcon main directory.

http://logwatch/oi_defcon_awr/

http://logwatch/oi_defcon_statspack/

You will be presented with a page allowing to chose a database. There is no LDAP parameter that controls which DB will be available here :). It is just the directories under var/www/html/awr\_reports that contain the awr/statspack reports.

After selecting the database we are at the main defcon page. Basically some sections from the Statspack report are visualised witch helps to “feel” waht is going on in the database.

The main sections are “CPU Usage”, “Buffer Gets”, “Physical Reads” and “Elapsed Time”. What I find to be the biggest advantage here is that it becomes very easy to see performance problems caused by few very bad performing SQLs that overwhelm the whole database.

If you see something like:

It becomes very obvious the the performance effords should focus on first three SQL, as they are the main contributors.

This approach helped me a lot of times when dealing with developers to convince them to focus their attention on selected SQLs in cases where overall system performance was bad and we had to select some areas that will be redesigned / tuned.

Each section has the corresponding SQL links:

The column “Module-exp” should contain the module name to easily identify from where the SQL is comming from (this has to be configured by the developer). As shown on this image often no such information is presented. Selecting that name redirects us to AWR/Statspack SQL report.

More interesting part is the second column named “FullHistor” which on the main page is an SQL ID but selecting this link directs us to the SQL history report.

Those drawings are made based on SQL reports, which are created like:

On AWR:
> @?/rdbms/admin/awrsqrpt.sql

On Statspack (level 6 or greater)
> @?/rdbms/admin/sprepsql

And I like the a lot. They tell us how selected SQLs are performing during the life of the database. Whether they take more or less time / resources, whether they are executed more or less often, etc. The statistics are divided into sections per instance and per execution. On the bottom of each section we have links that direct us to specific SQL ID report on which the are based.

Here one can easily spot some important trends. The one that is dangerous is where on every next day the SQL is taking more and more time / resources to complete.

Another interesting feature is the ability to show the instance statistics in historical perspective. Often the numbers are just the numbers and until we have some historical data it is hard to tell whether they are high or low :)

Those statistics are very usefull e.g. during some SGA / PGA parameter changes to confirm the expected effect.