Re: ORA-04031 hash-join large-pool

Mark, get a copy of the query and see if it would run better using nested loops instead of hash joins since hash joins do not scale well. That is every concurrent user is going to need a hash_area_size chunk of memory while nested loops joins generally take a lot less memory to process. If nested loops joins actually runs better or very close to the hash join then reducing the hash_area_size might allow the optimizer to choose a nested loops join.

Another possibility would be to try to set up an outline for the query and tune it via hints. Perhaps if the query ran faster there would be enough memory in the large poor to handle the workload.

Still another option if the nested loops is as good or better might be to disable hash joins for the query in question though this would take altering the session. If you cannot change the code I believe you could set up a database logon event trigger and cause perform the alteration from there.

HTH -- Mark D Powell -- Received on Tue Sep 06 2005 - 11:01:06 CDT