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.

. Then the same query, with similar

things down

I just realised you are also called Mark, in the words of the great Monty Python "can we call you Bruce just to keep it clear" Received on Fri Sep 09 2005 - 03:33:07 CDT