We have Oracle8i ver 8.1.5 Parallel server on AIX 4.3. The Server config: 4GB RAM.
SGA Size is 920 MB
SHARED_POOL_SIZE is 286 MB.
Oracle DB is configured as dedicated server.
We have a n-tier application that is using the Oracle DB thru MSDTC /XA on Win2k Adv Server. The application is implementing disconnected recordset.
The Problem: When the no of user sessions is below 10, the Shared pool usage is below 10%. After 6 to 8 hours, the number of user sessions will be arounf 100. At that time, the shared pool usage is 89%. The shared pool usage is not coming down even after all the user sessions are closed.
What may be the problem? Is there any way to flushout the Shared Pool. Should I increase the Shared Pool ?
Instead of forcing by giving the command
ALTER SYSTEM FLUSH SHARED POOL
is there any way that once the shared pool usage exceeds a certain limit say 75%, the system should automatically flush the shared pool.
Will there be any problem by forcing the flushing of shared pool?
I'm still not sure why you even want to flush your shared pool. Let Oracle manage its memory the way that it wants to.
Problems with flushing the shared pool? Yes. All of your previously parsed and cached query plans will go away, which will force the database to reparse every query run after you flush the pool. This will slow you down. Of course, if you're not using bind variables, you're already reparsing most everything anyway and this may not be any different than what you're doing now. :)
As I said earlier, the shared pool is not getting flushed out even after 48 hrs when all the user sessions are terminated. So I thought that there was some problem and I want to find out why it is happening? Is it with the application design or with Oracle configuration.
My point is that you simply do not want Oracle to flush the shared pool. Doing so will kill your performance because of all the extra time that will be reused simply parsing SQL because all of the stored execution pans are lost.
What you are seeing is NOT a problem. It's simply how Oracle manages the memory in the shared pool. Don't worry about it. :)
Originally posted by kunche_rk As I said earlier, the shared pool is not getting flushed out even after 48 hrs when all the user sessions are terminated. So I thought that there was some problem and I want to find out why it is happening? Is it with the application design or with Oracle configuration.
that is expected behaviour ok, Oracle has to cache the stuffs in shared pool and it is managed by an LRU algorithm, it will flush when he has to, for example when usage is 100% and he needs more memory, it will then age out old stuffs otherwise there is no point flushing
as heath pointed out you are killing the performance by flushing it, for example all the shareable SQL will have to be parsed again (parsing uses CPU ok) and dictionary cache has to be filled up again (this involves I/O ok)
and if you are using MTS, dont store your UGA in SGA, set up large pool, designed to handle UGA in MTS. UGA in SGA when using MTS would fragment your shared pool and degrade database performance