-
Hello everyone,
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 ?
Please suggest
Thanks in advance
Rajendra
-
Code:
ALTER SYSTEM FLUSH SHARED_POOL;
How do you measure the shared pool usage?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Automatic flushing of shared pool
Thanks Mr. Julian.
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?
Rajendra
-
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.
Rajendra
-
Re: Automatic flushing of shared pool
How do you measure shared pool usage? I personally very seldom flush the shared pool.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Mr. Julian, I used the following script:
column shared_pool_used format 9,999.99
column shared_pool_size format 9,999.99
column shared_pool_avail format 9,999.99
column shared_pool_pct format 999.99
spool rep_out\&db\shared_pool
select
sum(a.bytes)/(1024*1024) shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,
(max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)) shared_pool_avail,
(sum(a.bytes)/max(b.value))*100 shared_pool_pct
from v$sgastat a, v$parameter b
where a.name in (
'reserved stopper',
'table definiti',
'dictionary cache',
'library cache',
'sql area',
'PL/SQL DIANA',
'SEQ S.O.') and
b.name='shared_pool_size';
Rajendra
-
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. :)
-
At what intervel that oracle flushes the shared pool?
I want to flush only the 'Request and Response Queues Used with MTS' part of the shared pool. Is that possible.
-
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.
Rajendra
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|