RE: Help with Shared Pool Problem

Ganesh,

While ORA-4031 can mean that there is no space in the shared pool, it can also mean there is not enough contiguous memory in the shared pool. So, if you need 1k for a particular SQL statement, and the largest available chunk is 900k, then=20 Oracle will signal an ORA-4031. In this case, sometimes, flushing the shared pool can help, but not always. =20

Yes, there is an LRU mechanism for certain components in the shared pool. See the scripts I previously mentioned on Steve Adams' website, IxOra.

-Mark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ganesh Raja Sent: Thursday, October 14, 2004 10:29 AM To: Bobak, Mark
Cc: rlsmith_at_kmg.com; shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org
Subject: Re: Help with Shared Pool Problem

But mark if that is the case then we should never get a 4031 Because Oracle Should be able to FLush Stmts out of the Shared Pool as and when the execution is completed.

I have seen 4031 on a Single User Instance where Oracle is trying to allocate more space to the sql stmt and when no other sql is executing .. There should be a Way by which oracle Identified Least used SQL Stmts in the POOL and flushes them ..Something similar to the BUFFER_CACHE where Blocks keep moving on to an LRU list with each new Block fetched into the Memory...

Same LRU Mechanics should be applicable to Shared_pool also rite ???

Rgds
Ganesh
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 14 2004 - 10:56:47 CDT