Shared Pool not flushing out properly
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Shared Pool not flushing out properly

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    13

    Question

    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

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Mar 2002
    Posts
    13

    Question 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

  4. #4
    Join Date
    Apr 2001
    Posts
    118
    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. :)

  5. #5
    Join Date
    Mar 2002
    Posts
    13

    Question

    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

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Mar 2002
    Posts
    13

    Question

    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

  8. #8
    Join Date
    Apr 2001
    Posts
    118
    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. :)

  9. #9
    Join Date
    Mar 2002
    Posts
    13

    Question

    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.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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
  •  



Click Here to Expand Forum to Full Width