Re: stats$filestatxs - a query showing IO stats

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Thu, 20 Sep 2007 11:19:34 +1200
Message-ID: <4a38d9060709191619l5f92897cm3957716db7ba34cb@mail.gmail.com>

maybe a useful script for anyone suffering from free buffer waits etc or suspecting a the performance of their IO sub system....

It gives the stats (from stats pack) for the last 24hrs

select snap_time,

        sum(delta_phyrds)
    total_reads,

        --sum(delta_readtim)
    readtime,

        round(sum(delta_readtim) / 100 / sum(delta_phyrds),3)     avg_readtime_secs,

        sum(delta_phywrts)
    total_writes,

        --sum(delta_writetim)
    writetime,

        round(sum(delta_writetim) / 100 / sum(delta_phywrts),3)     avg_writetime_secs,

        --sum(delta_singleblkrds)
    total_singleblkrds,

        round(sum(delta_singleblkrdtim) / 100 / sum(delta_singleblkrds),3) avg_singleblkrdtime_secs,

        --sum(delta_wait_count),
    total_no_of_waits

        sum(delta_time)
    total_waittime_secs
from (

    select

           c.snap_time,
           tsname,
           filename,
           phyrds         - lag(phyrds) over (partition by b.dbid,
filename order by b.snap_id)         delta_phyrds,
           phywrts        - lag(phywrts) over (partition by b.dbid,
filename order by b.snap_id)        delta_phywrts,
           singleblkrds   - lag(singleblkrds) over (partition by
b.dbid, filename order by b.snap_id)   delta_singleblkrds,
           readtim        - lag(readtim) over (partition by b.dbid,
filename order by b.snap_id)        delta_readtim,
           writetim       - lag(writetim) over (partition by b.dbid,
filename order by b.snap_id)       delta_writetim,
           singleblkrdtim - lag(singleblkrdtim) over (partition by
b.dbid, filename order by b.snap_id) delta_singleblkrdtim,
           phyblkrd       - lag(phyblkrd) over (partition by b.dbid,
filename order by b.snap_id)       delta_phyblkrd,
           phyblkwrt      - lag(phyblkwrt) over (partition by b.dbid,
filename order by b.snap_id)      delta_phyblkwrt,
           wait_count     - lag(wait_count) over (partition by b.dbid,
filename order by b.snap_id)     delta_wait_count,
           time           - lag(time) over (partition by b.dbid,
filename order by b.snap_id)           delta_time
      from stats$filestatxs b, stats$snapshot c
     where b.snap_id = c.snap_id
       and b.dbid = c.dbid
       and b.instance_number = c.instance_number
       and c.snap_time > sysdate - 1

)
where delta_phyrds is not null
group by snap_time

Cheers
Tony

On 9/19/07, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> wrote:
>
>
>
>
> ----- Original Message ----
> From: Tony Adolph <tony.adolph.dba_at_gmail.com>
>
> Can anyone point me in the direction of some documentation for the perfstat's tables, e.g. stats$filestatxs.
>
> -----
>
> Tony
>
> Most of the statspack tables are clones of corresponding V$ views, with a couple of extra columns to hold the relationship to a database (db_id), instance (instance_number) and to a statistics snapshot (snap_id).
>
> Look into spcpkg.sql (in $ORACLE_HOME/rdbms/admin, or C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN on my XE install). Find function SNAP which actually collects the data. You'll soon see for example:
> v$segstat -> stats$seg_stat
> v$segment_statistics joined to stats$seg_stat -> stats$seg_stat_obj
> v$sesstat -> stats$sesstat
> and so on.
>
> As well as the v$ clones, there are of course a number of tables used to control the whole process, including:
> STATS$DATABASE_INSTANCE - the master for those (dbid, instance_number) columns
> STATS$LEVEL_DESCRIPTION (see table and column comments)
> STATS$SNAPSHOT - the handle for each snapshot (ie each execution of the SNAP function) and so the master for snap_id, recording date, time etc
> STATS$STATSPACK_PARAMETER - contains a list of thresholds for each database instance
> STATS$IDLE_EVENT - events which are considered 'idle' - ie not contributing to real wait time
>
> NB - the snaphot tables like stats$sesstat include all three of (snap_id, dbid, instance_number) and stats$snapshot includes all 3 in the PK. That's to make it easy to merge snapshots from multiple databases into a single repository.
>
>
>
> The naming of the stats$ tables is not quite 100% consistent (occasional discrepancies in use of underscores), and in some cases (like stats$seg_stat_obj) some additional data is added on - but you get the idea. So in most cases you can look up the definition of the columns from the corresponding v$ view.
> So stats$filestatxs is populated from v$filestatxs, which you can Google. It records statistics for a datafile:
> create table STATS$FILESTATXS
> (snap_id number not null
> ,dbid number not null
> ,instance_number number not null
> ,tsname varchar2 (30) not null
> ,filename varchar2 (513) not null
> ,phyrds number -- physical reads
> ,phywrts number -- physical writes
> ,singleblkrds number -- number of single block reads
> ,readtim number -- time spent reading
> ,writetim number
> ,singleblkrdtim number
> ,phyblkrd number -- number of blocks actually read
> ,phyblkwrt number -- number of blocks actually written
> ,wait_count number -- number of wait events
> ,time number -- time spent waiting
> ,file# number
>
> Hope that helps
>
> Regards Nigel
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 19 2007 - 18:19:34 CDT