RE: Help with Shared Pool Problem
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <ganesh.raja@xxxxxxxxx>
- Date: Thu, 14 Oct 2004 11:55:52 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ganesh Raja
Sent: Thursday, October 14, 2004 10:29 AM
To: Bobak, Mark
Cc: rlsmith@xxxxxxx; shaharul.anuar@xxxxxxxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
