Re: Statspack for RAC

From: DBA Infopower Support <support_at_dbainfopower.com>
Date: Fri, 6 Feb 2004 22:30:46 -0800
Message-ID: <bZ6dnYVs1-Q-Gbnd4p2dnA@comcast.com>

Hello Dias,

  Actually Oracle selects instance number internally:

SQL> select VIEW_DEFINITION from SYS.V_$FIXED_VIEW_DEFINITION where VIEW_NAME = 'V$FILESTAT'; VIEW_DEFINITION

---

---

select FILE# , PHYRDS , PHYWRTS , PHYBLKRD , PHYBLKWRT , READTIM , WRITETIM, A
VGIOTIM, LSTIOTIM, MINIOTIM, MAXIORTM, MAXIOWTM from GV$FILESTAT where inst_id
= USERENV('Instance')

SQL> desc gv$filestat

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 INST_ID                                            NUMBER
 FILE#                                              NUMBER
 PHYRDS                                             NUMBER
 PHYWRTS                                            NUMBER
 PHYBLKRD                                           NUMBER
 PHYBLKWRT                                          NUMBER
 READTIM                                            NUMBER
 WRITETIM                                           NUMBER
 AVGIOTIM                                           NUMBER
 LSTIOTIM                                           NUMBER
 MINIOTIM                                           NUMBER
 MAXIORTM                                           NUMBER
 MAXIOWTM                                           NUMBER

Regards,

Support

DBA Infopower

https://www.dbainfopower.com

The advice provided by DBA Infopower are provided "as is" with no warranty. DBA Infopower expressly disclaims any warranty, regarding the advice including any implied warranty of merchant fitness for a particular purpose aviating course of dealing and/or performance. DBA Infopower does not warrant that the advices provided by DBA Infopower will be free from bias, detests, errors, eavesdropping or listening. DBA Infopower shall not be responsible for the quality of information or the authentication of the services or details given by experts on the advice. By using this advice the user of the advice accepts the terms and conditions of this statement.

"dias" <ydias_at_hotmail.com> wrote in message news:55a68b47.0402061155.1f2f2f85_at_posting.google.com...
> Thanks,
>
> I've checked the statspack.snap proc. To gather stats for IO, it uses
> v$filestat. In this view, there is no reference to instance_number !
>
> This is the script of the view used to collect IO stats in
> statspack.snap. This view is owned by sys.
>
> CREATE OR REPLACE VIEW STATS$V_$FILESTATXS ( TSNAME,
> FILENAME, PHYRDS, PHYWRTS, READTIM,
> WRITETIM, SINGLEBLKRDS, PHYBLKRD, PHYBLKWRT,
> SINGLEBLKRDTIM, WAIT_COUNT, TIME ) AS select ts.name tsname
> , df.name filename
> , fs.phyrds
> , fs.phywrts
> , fs.readtim
> , fs.writetim
> , fs.singleblkrds
> , fs.phyblkrd
> , fs.phyblkwrt
> , fs.singleblkrdtim
> , fw.count wait_count
> , fw.time time
> from x$kcbfwait fw
> , v$filestat fs
> , v$tablespace ts
> , v$datafile df
> where ts.ts# = df.ts#
> and fs.file# = df.file#
> and fw.indx+1 = df.file#
>
> Thanks
>
> "DBA Infopower Support" <support_at_dbainfopower.com> wrote in message
news:<A7CdnQ58M4lf3b7dRVn-gg_at_comcast.com>...
> > Hello dais,
> >
> > Statspack records information on instance / session level. i.e. I/O
> > statistics are separate per instance and you'd need to sum them up.
> >
> > Regards,
> >
> > Support
> > DBA Infopower
> > http://www.dbainfopower.com
> >
> >
> > "dias" <ydias_at_hotmail.com> wrote in message
> > news:55a68b47.0402051257.2926dd80_at_posting.google.com...
> > > Hi,
> > >
> > > In a Rac environment with 2 nodes, statspack snapshots must be taken
> > > from each of the two instances or just from one isntance ?
> > >
> > > For example, the IO stats reported in the stats for the first instance
> > > inculde the IO for the second one or just the IO for this instance ?
> > >
> > > Thanks
Received on Sat Feb 07 2004 - 00:30:46 CST