Re: stats$filestatxs

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Wed, 19 Sep 2007 21:57:46 +1200
Message-ID: <003e01c7faa3$8c77c060$6801a8c0@tonypc>

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_at_gmail.com ; oracle-l Sent: Wednesday, September 19, 2007 8:09 PM Subject: Re: stats$filestatxs
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 - 04:57:46 CDT