Oracle Statistics Chart Viewer

11 07 2010

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.

The inspiration for today’s blog article comes from the Real Time Monitor in my Toy Project for Performance Tuning:

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:

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

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:

'The statistics to be displayed in the charts
strStatToChart(1) = "BUSY_TIME"
strStatToChart(2) = "CPU USED BY THIS SESSION"
strStatToChart(3) = "USER CALLS"
strStatToChart(4) = "SQL*NET ROUNDTRIPS TO/FROM CLIENT"
strStatToChart(5) = "USER COMMITS"
strStatToChart(6) = "USER ROLLBACKS"
strStatToChart(7) = "TRANSACTION ROLLBACKS"
strStatToChart(8) = "DB FILE SEQUENTIAL READ"
strStatToChart(9) = "DB FILE SCATTERED READ"
strStatToChart(10) = "READ BY OTHER SESSION"
strStatToChart(11) = "SYS TIME MODEL DB TIME"
strStatToChart(12) = "SYS TIME MODEL DB CPU"
strStatToChart(13) = "SYS TIME MODEL BACKGROUND CPU TIME"
strStatToChart(14) = "PARSE TIME CPU"
strStatToChart(15) = "RECURSIVE CPU USAGE"
strStatToChart(16) = "CONSISTENT GETS"
strStatToChart(17) = "DB BLOCK GETS"
strStatToChart(18) = "PHYSICAL READS CACHE"
strStatToChart(19) = "DATA BLOCKS CONSISTENT READS - UNDO RECORDS APPLIED"
strStatToChart(20) = "REDO SIZE"
strStatToChart(21) = "REDO WRITE TIME"
strStatToChart(30) = "WAITSTAT TIME DATA BLOCK"

Next, decide how the charts should appear on the page.  For example, for six charts, you might decide to use the following settings (the defaults)

'Large Charts
intChartsHorizontal = 3
intChartsVertical = 2
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 50
lngChartSpacingY = 50
lngChartWidth = 300
lngChartHeight = 300
strBar3DEffect = "border-style:outset;border-width:thin;"

For 24 charts, you might use the following settings (the defaults):

'Small Charts
intChartsHorizontal = 6
intChartsVertical = 4
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 10
lngChartSpacingY = 20
lngChartWidth = 150
lngChartHeight = 150
strBar3DEffect = "border-style:none;border-width:inherit;"

Finally, decide the color scheme of the charts, the update frequency, and whether delta values or absolute values should be displayed.  The following are the defaults:

strBarColorStandard = "#AAAAAA"
strBarColorCurrent = "#1010FF"
strBarTextColor = "#000000"
strChartBackgroundColor = "#EEEEEE"
strChart3DEffect = "border-style:inset;"
intShowDelta = True
intRefreshSeconds = 60 'Refresh interval of the chart data

I have found the book “CSS: The Definitive Guide” to be very helpful when adjusting the various appearance options.

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.

Script download: OracleStatisticChartViewer.vbs (save as OracleStatisticChartViewer.vbs)

—————————————————————–

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.


Comments : 2 Comments »

Categories : HTML, Performance, VBS