Since we are talking of Sort area sizes here, I tried to collect some
statistics on the amount of memory used by each dedicated Oracle
connection. As I understand it, the PGA is allocated in chunks upto the
SORT_AREA_SIZE. Initially, I just started a session, and noted down the
memory used using pmap. Then, I did a small sort, and then, a huge sort,
noting the memory utilization each time. I was even monitoring the PGA
statistics in v$sysstat. I was expecting the sizes to increase, and then
decrease when the sorting was done with. (retained_size was 0). I dont
recall my observations rite now, but it was definitely not what I expected.
Has anyone done something similar? Are my expectations rite?
Thanks
Raj
DENNIS WILLIAMS
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
TOUCH.COM> cc:
Sent by: Subject: RE: SORT_AREA_SIZE question
[EMAIL PROTECTED]
ty.com
06/25/2003
12:30 PM
Please respond
to ORACLE-L
Bart -
No it hasn't been recently asked.
1. SORT_AREA_SIZE is per-process. So be a little wary. Keep in mind
that
in-memory sorts are much faster than disk sorts. But you knew that.
2. Ask your system administrator to monitor whether your system is
getting paging/swapping.
3. Find out how many disk sorts are occurring. I prefer STATSPACK. Try
to get a handle on how may disk vs. memory sorts are occurring. Try to
increase SORT_AREA_SIZE until you have few disk sorts, but not so large you
cause paging/swapping. Also look at your temp space settings to make sure
when disk is used, it is used most efficiently. With a data mart, you may
have some really large sorts that will always be too large for memory
sorts.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, June 25, 2003 9:39 AM
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 see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
