Re: SORT_AREA_SIZE question

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Wed, 25 Jun 2003 11:27:21 -0700
Message-ID: <F001.005B9827.20030625104420@fatcity.com>

Hi!

Not entirely the truth. In huge reporting/OLAP environments you just can't have that much memory that all sorts are running entirely in memory. What you should look out for are multipass sorts, the ones which require reading and writing the same to disk multiple times. So keep the merge phases 0, but if not got enough memory, keep the merge phases as low as possible (1).

You can somewhat monitor the merge passes with following:

SQL> select name, value from v$sysstat where name like '%workarea%'; -- or use v$sesstat

<FONT

face=Arial>NAME                                                                  
VALUE----------------------------------------------------------------

----------workarea memory

allocated

0workarea executions -

optimal

1755workarea executions -

onepass

0workarea executions -

multipass

0

Also, if you use parallel queries for your reporting, it might be so that some parallel slaves do have two sort areas in use, one for sortin the queried data, other for merging the results with other slaves.

Read more from <A
href="http://www.ixora.com.au/newsletter/2000_11.htm#sort_area">http://www.ixora.com.au/newsletter/2000_11.htm#sort_area

Also you could set event 10032 and play with sort_area_size (reduce it), to see when you start seeing "intermediate runs" in addition to "initial runs". But more - if you got enough memory, then keep the size at 100M and don't worry about it - Oracle only allocates that much of memory for sort that it needs, no memory is wasted.

Tanel.

<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   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 - 13:27:21 CDT