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