| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SORT_AREA_SIZE question
Bart,
Another consideration is the effect of S_A_S on the CBO. Setting this (and letting other dependent parameters such as HASH_AREA_SIZE which is 2 x S_A_S unless set) may adversely affect the decisions that the CBO would take for otherwise 'sane' plans. As for the original problem, an ON LOGON system trigger should allow specific connections to set a larger SAS.
And no one has mentioned SORT_AREA_RETAINED_SIZE yet... I wonder why!
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com
-----Original Message-----
Sent: Wednesday, June 25, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L
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.
----- Original Message -----
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, June 25, 2003 6:39 PM
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 Bishop
Snr. Oracle DBA
ioko
Tel DDI: +44 (0) 1904 435 458
Mobile: +44 (0) 7881 624 386
Fax: +44 (0) 1904 435 450
Email: [EMAIL PROTECTED]
www.ioko.com
-----Original Message-----
Sent: 25 June 2003 15:39
To: Multiple recipients of list ORACLE-L
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 Note
The information in this E-mail message is legally privileged
and confidential information intended only for the use of the
individual(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 this
E-mail message. If you have received this E-mail in error,
please notify the sender. Thank you
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jun 25 2003 - 16:52:05 CDT
 |
 |