Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 16439 invoked from network); 19 Sep 2007 04:56:45 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 19 Sep 2007 04:56:35 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 47E1675D6E9; Wed, 19 Sep 2007 05:56:33 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29660-07; Wed, 19 Sep 2007 05:56:33 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 94D5175D6C6; Wed, 19 Sep 2007 05:56:32 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 19 Sep 2007 05:54:28 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D33C675D627 for ; Wed, 19 Sep 2007 05:54:27 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29270-02 for ; Wed, 19 Sep 2007 05:54:27 -0400 (EDT) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.187]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2EC9675D1E8 for ; Wed, 19 Sep 2007 05:54:26 -0400 (EDT) Received: by rv-out-0910.google.com with SMTP id b22so135803rvf for ; Wed, 19 Sep 2007 02:54:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:to:cc:references:subject:date:mime-version:content-type:x-priority:x-msmail-priority:x-mailer:x-mimeole:from; bh=5mPLqoKBfjyfPu0j8DfLKue3lgZMZET69s4VralsxGo=; b=KlHqCol/D7xssBaLAMWnw2UVqHy8Kjdbf6kyyiyu8r17rqMj6VQQohrBPOAyaupvIp7XGETs3IvBjFHKHV/6DlCWR/7uRYs0oADJ/zwtrFQlwCDx0az6/EELYBWfvX0hfYyUUH8a/uK79+FyQIH88dczPRIHRZr9qO482SG2T30= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:to:cc:references:subject:date:mime-version:content-type:x-priority:x-msmail-priority:x-mailer:x-mimeole:from; b=KA+LoUN6VUCLpoL8w0HN69gr6RLJDVGSQf/h9FuzPzs7Pky3MMWER1xAVvNL5/LKFtTGf7qmtlQy5DMjviVnQWdUuYr3bpEkqi74QPvgRFiEA0feaFeKpqb5Hdsxgot5tVgxEIyfNehbPikHB3ZhYAxDt/L9OxwCkSOrjR3gJBs= Received: by 10.114.158.1 with SMTP id g1mr507435wae.1190195666190; Wed, 19 Sep 2007 02:54:26 -0700 (PDT) Received: from tonypc ( [203.109.238.13]) by mx.google.com with ESMTPS id g39sm1124695rvb.2007.09.19.02.54.20 (version=SSLv3 cipher=RC4-MD5); Wed, 19 Sep 2007 02:54:23 -0700 (PDT) Message-ID: <003e01c7faa3$8c77c060$6801a8c0@tonypc> To: "Nigel Thomas" , "Alexey B. Danchenkov" , "Luis Fernando Cerri" Cc: "oracle-l" References: <831738.2788.qm@web58705.mail.re1.yahoo.com> Subject: Re: stats$filestatxs Date: Wed, 19 Sep 2007 21:57:46 +1200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0037_01C7FB08.1CC60C80" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896 From: Tony Adolph X-archive-position: 1681 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: tony.adolph.dba@gmail.com Precedence: normal Reply-to: tony.adolph.dba@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_NextPart_000_0037_01C7FB08.1CC60C80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 -----=20 From: Nigel Thomas=20 To: tony.adolph.dba@gmail.com ; oracle-l=20 Sent: Wednesday, September 19, 2007 8:09 PM Subject: Re: stats$filestatxs ----- Original Message ---- From: Tony Adolph Can anyone point me in the direction of some documentation for the = perfstat's tables, e.g. stats$filestatxs. =20 ----- 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.=20 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 ------=_NextPart_000_0037_01C7FB08.1CC60C80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thanks for your replies.
 
I guess I was just being lazy.... = looking at the=20 source is obviously the best place to look.
 
Cheers
Tony
----- Original Message -----
From:=20 Nigel Thomas
To: tony.adolph.dba@gmail.com = ; oracle-l=20
Sent: Wednesday, September 19, = 2007 8:09=20 PM
Subject: Re: = stats$filestatxs

-----=20 Original Message ----
From: Tony Adolph <tony.adolph.dba@gmail.com&g= t;

Can=20 anyone point me in the direction of some documentation for the = perfstat's=20 tables, e.g. stats$filestatxs. 

-----
 
Tony   Most=20 of the statspack tables are clones of corresponding V$ views, with a = couple of=20 extra columns to hold the relationship to a database (db_id), instance = (instance_number) and to a statistics snapshot (snap_id).   Look=20 into spcpkg.sql (in $ORACLE_HOME/rdbms/admin, or=20 C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN on my XE = install).=20 Find function SNAP which actually collects the data. You'll soon see = for=20 example: v$segstat=20 -> stats$seg_stat v$segment_statistics=20 joined to stats$seg_stat -> stats$seg_stat_obj v$sesstat=20 -> stats$sesstat and=20 so on.   As=20 well as the v$ clones, there are of course a number of tables used to = control=20 the whole process, including: STATS$DATABASE_INSTANCE=20 - the master for those (dbid, instance_number) columns STATS$LEVEL_DESCRIPTION=20 (see table and column comments) STATS$SNAPSHOT=20 - the handle for each snapshot (ie each execution of the SNAP = function) and so=20 the master for snap_id, recording date, time etc STATS$STATSPACK_PARAMETER=20 - contains a list of thresholds for each database instance STATS$IDLE_EVENT=20 - events which are considered 'idle' - ie not contributing to real = wait=20 time   NB=20 - the snaphot tables like stats$sesstat include all three of (snap_id, = dbid,=20 instance_number) and stats$snapshot includes all 3 in the PK. That's = to make=20 it easy to merge snapshots from multiple databases into a single=20 repository.   The=20 naming of the stats$ tables is not quite 100% consistent (occasional=20 discrepancies in use of underscores), and in some cases (like=20 stats$seg_stat_obj) some additional data is added on - but you get the = idea.=20 So in most cases you can look up the definition of the columns from = the=20 corresponding v$ view.  So=20 stats$filestatxs is populated from v$filestatxs, which you can Google. = It=20 records statistics for a datafile: create=20 table         =20 = STATS$FILESTATXS
(snap_id       &nb= sp;     =20 number          not=20 = null
,dbid          =       =20 number          not=20 null
,instance_number     =20 number          not=20 = null
,tsname         &nbs= p;    =20 varchar2 (30)   not=20 = null
,filename         &n= bsp;  =20 varchar2 (513)  not=20 = null
,phyrds         &nbs= p;    =20 number           =20         -- physical=20 = reads
,phywrts         &n= bsp;   =20 number           =20         -- physical=20 = writes
,singleblkrds        =20 number           =20         -- number of single block=20 = reads
,readtim         &n= bsp;   =20 number           =20         -- time spent=20 = reading
,writetim         = ;   =20 number
,singleblkrdtim      =20 = number
,phyblkrd         =    =20 number           =20         -- number of blocks actually=20 = read
,phyblkwrt         &= nbsp; =20 number           =20         -- number of blocks actually=20 = written
,wait_count        &nb= sp; =20 number           =20         -- number of wait=20 = events
,time         &nbs= p;      =20 number           =20         -- time spent=20 = waiting
,file#         &n= bsp;     =20 number
 Hope=20 that helps   Regards=20 Nigel
 
------=_NextPart_000_0037_01C7FB08.1CC60C80-- -- http://www.freelists.org/webpage/oracle-l