Re: Improving Performance of Hash Joins

On Dec 25, 3:04 pm, zigzag..._at_yahoo.com wrote:
> I am on Oracle 10.2.0.3 on Windows 2003 32 bit. I have /3GB switch set
> on and have a value of sga_target set to 2.x GB,
>
> I am using a third party package and many queries are doing hash
> joins. I cannot modify any queries because of no source code available
> to me. I know in Oracle9i, there was some init.ora parameter for
> specifying memory used by hash joins which has been taken away in 10G.
> What are my options (if any) to improve performance of this hash joins
> which for some queries take 2-3 minutes.

Perhaps you mean hash_area_size which defaults to 2 * sort_area_size. The parameter still exists in 10g. From 9.2 Reference entry
>>

Oracle does not recommend using the HASH_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. HASH_AREA_SIZE is retained for backward compatibility.
<<

By the way on a 9.2 system you can potentially use the OUTLINE feature to provide hints to the vendor SQL without ever having to change the source.

HTH -- Mark D Powell -- Received on Thu Dec 25 2008 - 16:06:53 CST