RE: WAITS

From: Orr, Steve <sorr_at_rightnow.com>
Date: Mon, 13 May 2002 08:53:25 -0800
Message-ID: <F001.0045F6E6.20020513085325@fatcity.com>

> I'm able to display phys read/write IO directly from... statspack

I started with data from StatsPack but instead went directly to the V$ tables to get it "real time," or at least every minute. I get physical writes, physical reads, db block and consistent gets, queries executed with soft and hard parses, and SQL*Net bytes sent and received as part of my top level graphs to maintain a pulse on activity. Then I drill down into the wait interface for the details.

> Would love to be able to pool resources here and bounce off some ideas.

Currently my code is a hodgepodge of Perl, Python, RRDTool, PHP, and is piggybacking on "ORCA" stuff too. I've thought about opening it up to some kind of SourceForge collaborative development but that means I'd have to turn the hodgepodge code into a unified set (probably Python) and commit to supporting it. Don't know if I'm ready for that. I've also thought about presenting it but rather experience the glory of an over-achiever I'm complacently willing to wallow in the anonymity and obscurity of an under-achiever. ;-)

Steve Orr
Bozeman, Montana

-----Original Message-----
Sent: Monday, May 13, 2002 8:03 AM
To: Multiple recipients of list ORACLE-L

That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well.

Would love to be able to pool resources here and bounce off some ideas.

mkb

• "Orr, Steve" <sorr_at_rightnow.com> wrote:
  > > Each morning I produce graphs...
  > I think this is key. Having historical data
  > graphically presented helps to
  > establish the norm and when there may be performance
  > issues to investigate.
  > This follows step 2 of Gaja's "Oracle Performance
  > Tuning 101 Methodology"
  > which says, "Measure and document current
  > performance."
  >
  > To do this I created a DBA monitoring HTML display
  > tool which gets data from
  > V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores
  > it in a round robin
  > database and displays it with RRDTool. I've
  > accumulated 2 months of this
  > data and it's amazing how lightweight it is. With
  > graphs it's easy to see
  > when something's amiss. We capture expensive SQL via
  > StatsPack every 15
  > minutes and I have correlated a spike on a graph to
  > specific SQL executed 2
  > hours earlier.
  >
  > Now I'm trying to decide on my next enhancement: 1)
  > HTML/GUI interface to
  > StatsPack data or; 2) Drill down to V$SESSION_WAIT
  > ???
  >
  >
  > Steve Orr
  > Bozeman, Montana
  >
  >
  >
  > -----Original Message-----
  > Sent: Thursday, May 09, 2002 5:23 PM
  > To: Multiple recipients of list ORACLE-L
  > Importance: High
  >
  > In general There are two problems in using the "top
  > five waits" out of
  > statspack: it reports idle waits; no matter how
  > well-tuned your database
  > there will always be a top five. The numbers
  > presented show total
  > time-waited in csecs for the time period. As Jared
  > said we don't know the
  > time period. We don't know the average wait time.
  >
  > I have learned some rudimentary gnuplot skills.
  > Each morning I produce
  > graphs of what went on the in the databases the
  > previous day on and hour by
  > hour basis. If something is really askew I break
  > the hour down into ten
  > minute blocks. This helps me to better recognize
  > patterns of database
  > usage.
  >
  > Ian MacGregor
  > Stanford Linear Accelerator Center
  > ian_at_slac.stanford.edu
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Orr, Steve
  > INET: sorr_at_rightnow.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: sorr_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon May 13 2002 - 11:53:25 CDT