Re: stats$filestatxs

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Wed, 19 Sep 2007 01:09:33 -0700 (PDT)
Message-ID: <831738.2788.qm@web58705.mail.re1.yahoo.com>

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 - 03:09:33 CDT