Re: object placement

From: Jean-Francois Leguillier <jfleguillier_at_tiscali.fr>
Date: 14 Nov 2002 14:44:35 GMT
Message-ID: <20021114-154435-115026@foorum.com>

Volume is not the real indicator for activity. Search io rate/io activity. Query v$filestat to obtain the more accessed file : write/read.

select f.name "File",
       s.phyrds,s.phywrts,
       s.phyblkrd,s.phyblkwrt
  from v$datafile f,
       v$filestat s

 where s.file#=f.file#
 order by s.phywrts desc; -- change sort with phyrds for psysical reads sorts

You can also query x$kcbfwait (with user SYS) : you'll get waits by datafile, more useful than v$waistat.
If timed_statistics is set to true, you'll see wait time for each datafile. You will can see which files are hotest. Cross results with dba_segments to see which objects include the hot datafiles. try this query :

column	file#		format 999	Heading "File"
column	ct		format 999999	heading "Waits"
column	time		format 999999	heading "Time"
column	avg		format 999.999	heading	"Avg time"

Col Fichier for a50 wrap
select f.name "Fichier",

       indx+1 file#,
       count  ct,
       time,
       time/(decode(count,0,1,count)) avg

  from sys.x$kcbfwait x,v$datafile f
 where indx < (select count(*) from v$datafile)    and f.file#=indx+1
 order by count desc;

-- 
Use our news server 'news.foorum.com' from anywhere.
More details at: http://nnrpinfo.go.foorum.com/

Received on Thu Nov 14 2002 - 08:44:35 CST