Re: Gather IO stats

Sybrand Bakker wrote:
> On 30 Jul 2006 06:44:20 -0700, "Charles Hooper"
> <hooperc2000_at_yahoo.com> wrote:
>
> >If you can find a way to execute the above once every 30 minutes, and
> >insert the results into a log table (prefix the first run of the SQL
> >statement with CREATE TABLE FILE_ACTIVITY AS, and all subsequent runs
> >with INSERT INTO FILE_ACTIVITY), you will be able to monitor the
> >activity of read/write activity of the various files - simply subtract
> >the PHYRDS, PHYWRTS, PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM of a
> >previous run from the values of a more recent run (this can be done
> >using a simple SQL statement). The TIMESTAMP column will help
> >determine the average activity over the selected time period. The
> >above will work for data, indexes, and temp/sort. You can do something
> >similar with V$ROLLSTAT and the various other performance views.
>
>
> And so you will just re-invent statspack!!
>
> --
> Sybrand Bakker, Senior Oracle DBA

Well, maybe re-invent Statspack, maybe exceed what Statspack provides with a little more work. :-)

Off and on for the last five months I have been working on a tool to analyze Oracle performance. When logging is enabled, the program captures various performance views once a minute, and others once a second. Once logging is complete, the program plays back the log for a selected session: assume at 12:01 it observed a 40 second increase in wait events for db file sequential read, 10 second increase in waits for direct path read. When such events are found, the program reviews the log of V$SESSION_WAIT and attempts to determine the SQL statement that was being processed by the session, the file # and block number indicated, and the table or index at that file # and block #. Additionally, for that 1 minute interval, the program reviews the log of V$SESSTAT and V$SYSSTAT to observe loads placed on the system by sorts, parse, index leaf block splits, SQL Net round trips, etc. both system wide and for the session. Data segment growth/shrink and rollback usage are also reported. Only when that is done, does it reporting file access - if there are no sessions (or more specifically the important session being traced) reporting delays for the time period, is it important to look at the file access?

Statspack may be nice to use, but I prefer something that tells me what is happening in the system during the short time period when a performance problem affects a user. Understanding how to find the information in real time when it is needed helps a lot, and doing things the hard way once in a while is not all bad.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Jul 30 2006 - 14:02:48 CDT