Re: stats$filestatxs - a query showing IO stats (without div by zero trap)

Hi all,

Sorry for the dodgy earlier version (could fail with div by 0 error),
here's an improved version:

select  --snap_time
        trunc(snap_time,'HH24')
    snap_time_hr
        -- Don't forget the group by if you un-comment out here!
        --,tsname
        --,filename
        /* filesystem: e.g. /pdb/p31022 */
        --,substr(filename,1,instr(filename,'/',1,3) - 1)
    filesystem
        ,sum(delta_phyrds)
    total_reads
        --,sum(delta_readtim)
    readtime
        ,case
            when sum(delta_phyrds) = 0 then 0
            else round(sum(delta_readtim) / 100 / sum(delta_phyrds),3)
        end
    avg_readtime_secs
        ,sum(delta_phywrts)
    total_writes
        --,sum(delta_writetim)
    writetime,
        ,case
            when sum(delta_phywrts) = 0 then 0
            else round(sum(delta_writetim) / 100 / sum(delta_phywrts),3)
        end
    avg_writetime_secs
        --,sum(delta_singleblkrds)
    total_singleblkrds
        ,case
            when sum(delta_singleblkrds) = 0 then 0
            else round(sum(delta_singleblkrdtim) / 100 /
sum(delta_singleblkrds),3)
        end
    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
      trunc(snap_time,'HH24')
      --,tsname
      --,filename
      --,substr(filename,1,instr(filename,'/',1,3) - 1)
;

Tony



On 9/19/07, Nigel Thomas <nigel_cl_thomas@xxxxxxxxx> wrote:
>
>
> ----- Original Message ----
> From: Tony Adolph <tony.adolph.dba@xxxxxxxxx>
>
> 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

Other related posts: