Re: Newbie DBA Problem: Hitting Temp Hard

        Compare the number of sorts in memory vs the number on disk. If the number of disk sorts is greater than 5% you should increase the sort area size. If you have enough RAM you should consider increasing the sort area size enough to bring the disk sorts down to 1%.

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 5/11/00, 2:02:16 PM, billmil_at_my-deja.com wrote regarding Newbie DBA Problem: Hitting Temp Hard:

> I'm a newish-dba with a potential performance problem: we're hitting
> our temporary data files pretty hard--especially compared to the index
 

> and data datafiles. (The output of one of our nightly queries is
> below).
 

> I've gone through once and created indexes for our commonly run
 queries
> to reduce the need for on-the-fly sorting.
 

> I've also considered increasing the sort area size from the default
 64K
> to 256K (though I'm not sure that will help the problem).
 

> My next step, I thought, would be to perform an sql trace on a
 session,
> execute the the most commonly executed functions (by accessing the web
 

> pages) and then use TKPROF with Explain plan to check the actual
> execution plans, then creating more indexes if needed.
 

> Is this the right approach? Do you have any other recommendations?
 Is
> this a problem after all?
 

> bill milbratz
 

> Database File IO
> Weights
> ordered by
> Drive

> DRIVE FILE NAME TOTAL IO
> WEIGHT
> ----- ---------------------------------------- ---------- -------
 

> /opt/ /opt/oracle/oradata/LINP/temp01.dbf 25921
> 100.00
> /opt/oracle/oradata/LINP/lin tab01.dbf 4425
> 17.07
> /opt/oracle/oradata/LINP/system01.dbf 3507
> 13.53
> /opt/oracle/oradata/LINP/lin idx01.dbf 2122
> 8.19
> /opt/oracle/oradata/LINP/rbs01.dbf 1314
> 5.07
> /opt/oracle/oradata/LINP/tools01.dbf
> 61 .24
> ***** -------
 

> sum
> 144.09

> Query:
> select
> substr(DF.Name, 1,5) Drive,
> DF.Name File Name,
> FS.Phyblkrd+FS.Phyblkwrt Total IO,
> 100*(FS.Phyblkrd+FS.Phyblkwrt)/MaxIO Weight
> from V$FILESTAT FS, V$DATAFILE DF,
> (select MAX(Phyblkrd+Phyblkwrt) MaxIO
> from V$FILESTAT)
> where DF.File# = FS.File#
> order by Drive, Weight desc

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri May 12 2000 - 00:00:00 CDT