Re: Gather IO stats

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 30 Jul 2006 06:44:20 -0700
Message-ID: <1154267060.451332.251590@p79g2000cwp.googlegroups.com>

mngong_at_gmail.com wrote:
> Would like to gather physical reads/writes from a semi hourly
> statspack run.
> Any one already done this and want to share the scripts that were used
> that
> will be appreciated.
>
> TIA
> MB

I don't use Statspack. However, you can quite easily build a logging table that stores the physical read/writes even without using Statspack. For instance:
SELECT
  'DATA' FILE_TYPE,
  TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI') TIME_STAMP,

  FS.FILE#,
  FS.PHYRDS,
  FS.PHYWRTS,
  FS.PHYBLKRD,
  FS.PHYBLKWRT,
  FS.READTIM,
  FS.WRITETIM,
  FS.AVGIOTIM,
  FS.LSTIOTIM,
  FS.MINIOTIM,
  FS.MAXIORTM,
  FS.MAXIOWTM,
  DF.NAME

FROM
  V$FILESTAT FS,
  V$DATAFILE DF
WHERE
  FS.FILE#=DF.FILE#
UNION ALL
SELECT
  'TEMP' FILE_TYPE,
  TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI') TIME_STAMP,

  TS.FILE#,
  TS.PHYRDS,
  TS.PHYWRTS,
  TS.PHYBLKRD,
  TS.PHYBLKWRT,
  TS.READTIM,
  TS.WRITETIM,
  TS.AVGIOTIM,
  TS.LSTIOTIM,
  TS.MINIOTIM,
  TS.MAXIORTM,
  TS.MAXIOWTM,
  TF.NAME

FROM
  V$TEMPSTAT TS,
  V$TEMPFILE TF
WHERE
  TS.FILE#=TF.FILE#; 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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Jul 30 2006 - 08:44:20 CDT