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

Mark wrote:
> 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)

Your ad-hoc query builder does not use bind variables and is fragmenting your shared pool leading to the error you are getting. This is basically a bug in the query builder and should ideally be fixed, it potentially also leaves your database vulnerable to sql injection attacks. However in the meantime if you can create a log on trigger for users of that application then setting cursor_sharing = force for those sessions may help. You should read up on the parameter in the documentation because it can also have some unpleasant side effects.

-- 
MJB