large SORT_AREA_SIZE usefullness

From: Johnson Poovathummoottil <joni_65_at_yahoo.com>
Date: Wed, 20 Jun 2001 10:25:05 -0700
Message-ID: <F001.00330927.20010620102617@fatcity.com>

Hi All,

We have small data warehouse database. Some queries against some fact tables which are in the range of 30 to 40 GB are often causing full table scans. We had the SORT_AREA_SIZE set to 1M and we were seeing a memory sort of 99%. But the number of queries which used the disk sort was around 500 for a week. I plan to increase the sort area size according to the formula given in Steve Adams' website and tested a few queires to use a SORT_AREA_SIZE of 86M and found that all the quries which used the larger sort_area_size actually ran slower than the ones which used 1M sort_area_size.

Could some one explain this behaviour?

---

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Johnson Poovathummoottil
  INET: joni_65_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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 20 2001 - 12:25:05 CDT