Oracle Statistics Chart Viewer

11072010

July 11, 2010

Last week I provided a blog article that created a horizonal orientation auto-scaling HTML-based bar chart using nothing more than a SQL statement. At first glace the final SQL statement would likely appear to be very complicated, however if you follow the blog article from the start, the solution really is quite straightforward. Today’s blog article will build on the final result of last week’s blog article, however the path to the final solution will be very different, and multiple vertical orientation bar charts will appear on a single web page.

Since this project is a bit more advanced than last week’s project, we will use a programming language to perform the calculations and write out the HTML directly to a web browser. We will use VBScript, which is available on most computers running Windows, so that will make it easy for you to modify the script for your needs:

Changing the statistics to be displayed

Changing the sampling frequency

Changing the number of charts displayed per row and the number of rows

Changing the spacing between the charts

Changing the bar colors

Changing the background color

Adding grid lines

The actual script may be a little difficult to understand. Basically, the script queries several views, displays the historical delta values of the specified statistics in chart form, and then waits for the sampling frequency counter to tick down before collecting the next set of statistics. The statistics collection process ends when the user clicks the Close button. The following views are queried every time the statistics are gathered:

V$OSSTAT

V$SYS_TIME_MODEL

V$WAITSTAT

V$SYSSTAT

V$SYSTEM_EVENT

The statistic names from some of the views are prefixed to reduce the chances of name collisions between statistics from the different views. For example, the statistics from V$SYS_TIME_MODEL are prefixed with “SYS TIME MODEL ” and the statistics from V$WAITSTAT are prefixed with “WAITSTAT TIME “. The current value is displayed in the left-most bar, with up to the previous 29 values displayed in the remaining bars.

Sample Charts:

Flat appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

–

Flat appearance with six horizontal and four vertical bar charts with the refresh interval set at 10 seconds:

–

3D appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

—–

So, what changes do you need to make to use the script? First, if you are running a release of Oracle Database prior to 10.1 you will need to remove the queries of V$SYS_TIME_MODEL and V$OSSTAT. Second, you need to have an Oracle user other than a SYSDBA user (SYS) that is able to query the various performance views. Third, you need to specify the username and password of that user in the script:

Next, decide which statistics you would like to display in the charts and in what order (sequenced across the page and then down the page). The statistic names are all recorded in uppercase, with the following as defaults in the script:

Please post any improvements that you make to the script. It might be interesting to note that this code can be incorporated into the Database Inspector Gadget that I created a while ago if the computer is running Windows Vista or Windows 7.

Partially related side note: There are still a couple of seats remaining for the Michigan OakTable Symposium, which will certainly have the most entertaining and in-depth training sessions for DBAs and developers in 2010. There are 20+ OakTable Network members from around the world converging on Ann Arbor, MI. in mid-September 2010. I will be in attendance, but not presenting (Randolf Geist, who co-wrote two chapters in the Expert Oracle Practices book with me is flying in from Germany to present two sessions). More information may be found here: Michigan OakTable Symposium.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: