ORA-04031 hash-join large-pool

We are having intermitent, but reproducable, ORA-04031 errors in our application, and I am looking for solutions.

To reproduce the problem I have to run the probelm query simultaneously on three sessions. Two sessions work fine, so there is obviously a loading component involved.

I have done a lot of reading of the news groups, and specificially I am looking for a solution that does not require simply increasing the large-pool and hoping the problem goes away (until next time)! But maybe we have to increase the large-pool, but I would like to have some idea by how much rather than using a "hit and hope" technique.

I have some specific questions.

1. When using the shared server configuration is the hash-area defined by hash_area_size used, or is this ignored and only the large pool used?
2. Is there a way to find out how much memory a query requires. I have run explain plan, and there is a column of bytes per step, but is this just the memeory used for that step, or an accumulated memory for the step and it's children, or what. Do I sum all the memory in the explain plan to find the total required? I can not find an answer to this on the web. But, anyway, I read that these figures are only estimates anyway.

The query is complex and is not optimised. It is generated by a query builder in a data mining application, and there is no immediate opportunity to optimise it.

TIA for any suggestions.

Mark

Details of environment follow:

Error:
ORA-04031: unable to allocate xxxxxx bytes of shared memory ("large pool","unknown object","hash-join subh","kllcqc:kllcqslt")

Oracle Version: 9.2
O/S: Solaris
Shared Servers enabled
Large Pool: 256M
workarea_size_policy: manual
pga_aggregate_target: 0
hash_area_size = 64M Received on Tue Sep 06 2005 - 03:57:10 CDT