Re: Newbie DBA Problem: Hitting Temp Hard

If you do increase your sort_area_size, don't forget to rebuild your TEMP tablespace. i.e. the initial and next settings should be the same size as the sort_area_size. Also, make sure that it is temporary storage and not permanent. Don't be alarmed if you have to increase your sort_area_size considerable, we have ours set to approx. 4meg.

HTH
Eric Peterson, DBA
eric_peterson_at_NOmaurices.inrg.SPAMMERScom

Note that the views I have expressed here are solely my own and not that of my employer. To email me directly, remove the NO and the SPAMMERS from the email address above.

In article <20000512.4441100_at_noname.nodomain.nowhere>,   Jerry Gitomer <jgitomer_at_erols.com> wrote:
>
>
> Compare the number of sorts in memory vs the number on disk.
 If the=20
> number of disk sorts is greater than 5% you should increase the
 sort=20
> area size. If you have enough RAM you should consider increasing
 the=20
> 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=20
> 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=20
 queries
> > to reduce the need for on-the-fly sorting.
 

> > I've also considered increasing the sort_area_size from the
 default=20
 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=20
 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?
 =20
 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# =3D FS.File#
> > order by Drive, Weight desc
 

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

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