Re: ORA-04031: unable to allocate 8388608 bytes of shared memory

"Mark" <mstern_at_xtra.co.nz> wrote in message news:1117032010.209562.97500_at_f14g2000cwb.googlegroups.com...
>I have a query returning the following error message:
>
> ORA-04031: unable to allocate 8388608 bytes of shared memory ("shared
> pool","unknown object","hash-join subh","QERHJ Hash Table Entries")
>
> What I really would like to know, is if an attempt to allocate "8388608
> bytes" is "normal"?
>
> Should a query be attempting to allocate this much memory? The database
> is not what I would call big (i.e. 300000 rows and 600000 rows in the
> main tables).
>
> TIA
>
> Mark
>
> ---
>
> Here are some details:
>
> The query is:
>
> SELECT "Units"."UnitKey", "Units"."UnitName", "Units"."StreetName",
> "Units"."StreetNmbr", "Units"."PostalZone", "Units"."Town",
> "Units"."AnimalMovementDBIDs" FROM "Units" WHERE 1=1
> AND "Units"."UnitKey" IN
> (SELECT "UnitKey" FROM "UnitCategories" WHERE
> ("CategoryKey"=HEXTORAW('8EFF2E9DBEE54A97B25303D4F50DB655') OR
> "CategoryKey"=HEXTORAW('0BF64F42B37446E1BB828E1889CE4929')) AND
> ("Units"."RVOKey" = HEXTORAW('E41B25063E744543B26419F190CF62B2')) )
> AND "Units"."UnitKey" IN
> (SELECT "Units"."UnitKey" FROM "Units" WHERE 1=1 AND
> (((lower("Units"."UnitName") LIKE '%l%') OR
> (lower("Units"."StreetName") LIKE '%l%') OR (lower("Units"."Town")
> LIKE '%l%') OR (lower("Units"."AnimalMovementDBIDs") LIKE '%l%')) ))
>
> Excuse the messy SQL, it comes from an ad-hoc quesry builder. There is
> actually a bug in the above SQL, it runs, but the second sub-query is
> wrong.
>
> Database: 9.2i
> Operating System: Solaris (not sure which version)
>

Yes, such an allocation could be normal.

It looks like you are trying to allocate 8M in the shared pool for a hash join - which suggests you are used shared servers (the technology formerly known as MTS), with a hash_area_size set to something like 16MB.

Check the execution plan for the query to see if the optimizer has transformed the subquery components into hash joins.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005