RE: SORT_AREA_SIZE question

From: Farnsworth, Dave <DFarnsworth_at_Ashleyfurniture.com>
Date: Wed, 25 Jun 2003 10:08:22 -0700
Message-ID: <F001.005B92F9.20030625072450@fatcity.com>

You want a sorts to disk ratio of 1 percent or less. To find this run this;

dispatch> column name format
a30dispatch> select name, value 2 from v$sysstat 3 where name like 'sort%';

<SPAN

class=540032414-25062003>NAME                               
VALUE------------------------------ ---------sorts 
(memory)

283420sorts

(disk)

64sorts

(rows)

29507963

the ratio of sorts =
round((64/283420)*100,3)
ratio of sorts = 0.023%

Learned this from the Oracle DBA 101 book, one of the best buys I made.

Dave

  -----Original
  Message-----From: Bart Kersteter
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003   9:39 AMTo: Multiple recipients of list ORACLE-LSubject:   SORT_AREA_SIZE question
  Hello,

  I joined this list last week, so I apologize in advance if I'm asking a   question that has previously been answered.
  I am responsible for a reporting database/data mart that is approximately   175 GB. Our main fact table ranges from 1-14 GB depending upon how far   along we are into our financial year. I have large reports that run full   table scans on this table daily. In an effort to keep as much of the   sorting in memory as possible I have specified SORT_AREA_SIZE to be   100MB. Some of the tuning books I am reading now are making me   second-guess myself and I am wondering if this is overkill.
  Can anyone provide some advice on how large they are setting their   SORT_AREA_SIZE values for their DSS systems?
  Thanks in advance,

  Bart

Please NoteThe
information in this E-mail message is legally privilegedand confidential information intended only for the use of theindividual(s) named above. If you, the reader of this message,are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward thisE-mail message. If you have received this E-mail in error,please notify the sender. Thank you Received on Wed Jun 25 2003 - 12:08:22 CDT