Re: stats$filestatxs - a query showing IO stats
- From: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
- To: "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>
- Date: Thu, 20 Sep 2007 11:19:34 +1200
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@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
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: stats$filestatxs - a query showing IO stats
