Re: SORT_AREA_SIZE question

Hi!
 
At the least the wording is incorrect, a user who *connects* to instance, doesn't allocate any sort memory unless there is a need for sort. And if there's need for 100k, only that much will be allocated. And when the sort is over (and rows returned), the sort memory is freed.
 
On the other hand, if all of the 10 users do huge sorts with sort_area_size 100M, they could have 1GB allocated altogether, even more than that, check my other mail. So yeah, this could introduce paging if having not enough memory.
Btw, in mixed environments you could set sort_area_size to small value and create logon trigger which sets s_a_s big for reporting users.. just a thought about swapping issues...   
Tanel.
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Nigel Bishop To: <A [EMAIL PROTECTED] href="mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L

  Sent: Wednesday, June 25, 2003 6:39
  PM
  Subject: RE: SORT_AREA_SIZE
question   

  Bart
 

  Each user which connects to the DB will   consume that amount of RAM on top of any memory used by the application.    A lot depends on how much memory your server has i.e. 10 users will use at   least 1Gb of RAM.  It could induce swapping which is a   killer.
 

  Our DSS systems use 20Mb for sort area   mind you we have 18Gb temp tablespace
 

  Regards
 
 

  Nigel BishopSnr. Oracle DBAiokoTel DDI:  +44 (0) 1904   435 458Mobile:   +44 (0) 7881 624
  386Fax:      +44 (0) 1904 435
  450Email:   
  [EMAIL PROTECTED]         
  www.ioko.com        

    -----Original
    Message-----From: Bart Kersteter
    [mailto:[EMAIL PROTECTED] Sent: 25 June 2003     15:39To: 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:20:33 CDT