Re: Hash area, sort area ...

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Thu, 16 Nov 2000 23:11:26 GMT
Message-ID: <3a1468dd.1890002149@nsw.nnrp.telstra.net>

Hi Sergey,

Check out http://www.ixora.com.au/q+a/sorts.htm#7_02_2000. It explains that unless you reduce the number of merge phases or avoid disk sorts entirely then you will see no difference in performance. The same principle applies to hash area memory sizing.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: sergey_s_at_my-deja.com

If I change HASH_AREA_SIZE and SORT_AREA_SIZE on a per session basis, is it guaranteed that Oracle will use as much as I have specified? I've been experimenting with these settings and haven't seen an increase in performance. I've tried setting both to almost 20 times higher than what they normally are and still gained almost nothing. My test query involves CRAETE TABLE AS with full table scans of two tables (45 mil rows and 3 mil rows) with a hash join and a group by (8 degrees parallel).

Oracle 8.0.5; 8 procs (400 MZ); 80 Gig memory; RAID 5 (66 disks);

Thank you!
Sergey

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 16 2000 - 17:11:26 CST