Re: stats$filestatxs
- From: Tony Adolph <tony.adolph.dba@xxxxxxxxx>
- To: "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>, "Alexey B. Danchenkov" <dabron@xxxxxxxxxxx>, "Luis Fernando Cerri" <lfcerri@xxxxxxxxx>
- Date: Wed, 19 Sep 2007 21:57:46 +1200
Thanks for your replies.
I guess I was just being lazy.... looking at the source is obviously the best
place to look.
Cheers
Tony
----- Original Message -----
From: Nigel Thomas
To: tony.adolph.dba@xxxxxxxxx ; oracle-l
Sent: Wednesday, September 19, 2007 8:09 PM
Subject: Re: stats$filestatxs
----- 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
Other related posts:
