Re: ORA-04031 hash-join large-pool

Mark, thanks for tips. I tried reducing the hash_area_size just for the

session to try and force the optimiser to use nested joins, but was unable to make a significant difference to the execution plan even with

a very small value (2000). So I disabled hash joins altogether. This removed all the hash joins from the execution plan, BUT running the query effectively hung Toad! I guess the query was executing somewhere,

but after 5 minutes and it had not returned a result, I shut down Toad (I don't have dba rights on this database so I can not kill sessions properly). So does not look like nested jions would be a solution. I started trying to use some hints inside some of the sub-queries, but then got diverted to other higher priority work. BTW the query, with hash joins takes around 30 sec to execute, and returns 3 rows.

There is another related issue. If I run this query on another Oracle instance on another computer (Windows 200 Server), with very similar Oracle settings as the Solaris computer, then I get the "query goes away and doesn't come back" problem again. On this computer I have DBA rights, and can see that the query causes a "FULL TABLE SCAN" of a table with around 300000 records, and the FULL TABLE SCAN appears to want to take around 1 hour to execute (looking at the LONG TRANSACTION information!!! Is this normal??? Can it be stopped??? Prevented???

I have analysed all the tables and indexes, and recreated all the tables used in the Query, but the problem persists. I thought it may have been a corruption in the database or the Oracle installation or disk defragmentation on Windows 2000, and I was going to rebuild the server from scratch. But now, with the experience with the Solaris box removing the hash joins, I am wondering if everything is normal!!!

Once again, any help gratefully recieved. Received on Fri Sep 09 2005 - 03:52:01 CDT