Re: Gather IO stats

On 30 Jul 2006 12:02:48 -0700, "Charles Hooper" <hooperc2000_at_yahoo.com> wrote:

>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.

I see, you are actually re-inventing the wheel... The functionality you are building is available in 10g, and in several other products.
Obviously using the commercial alternative is cheaper than you reinventing the wheel.
Probably someone should tell you to stop reinventing the wheel, and make sure you spend your employer's time no longer on exercises in futility.

--
Sybrand Bakker, Senior Oracle DBA