DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: flush shared_pool

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    flush shared_pool

    I have a developpement database that has the shared_pool with less than 5% free memory. When it less than 5%, we have a significant performance problem. The shared_pool reach the 5% threshold once every 15 days.

    Would it be a good idea to create a job that flush the shared_pool when it reach 5% free memory?

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    What is the size of current shared_pool_size and shared_pool_reserved_size and what version of Oracle you are using? Monitor the shared pool usage using following query. This script tells whether larger objects have failed to obtain memory in the portion of the shared pool reserved for larger objects.

    COLUMN next_line FORMAT a60 newline
    TTITLE ' Shared Pool Reserved Size Recommendation'
    SELECT 'You may need to increase the SHARED_POOL_RESERVED_SIZE' next_line, '
    Request Failures = '||request_failures
    FROM v$shared_pool_reserved
    WHERE request_failures > 0
    AND 0 != ( SELECT to_number(value)
    FROM v$parameter
    WHERE name = 'shared_pool_reserved_size' );
    Last edited by sgodbole; 03-21-2003 at 05:42 PM.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    Nice script.
    My shared_pool_size is 500meg!!!

  4. #4
    Join Date
    Jan 2003
    Posts
    78
    It won't hurt much if you flush the shared pool because it's a development database. What's the setting of shared_pool_reserved_size and what is the result of above query/script?
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    The script wont return, cuse i did a flush shared_pool 1 hour ago.
    The shared_pool_size is 5meg.

  6. #6
    Join Date
    Jan 2003
    Posts
    78
    How much memory on the server? Any more databases on the server? Is there enough free memory on the server? 500MB shared pool is already big. IF you are using lot packages and procedures, I will recommend increasing shared_pool_reserved_size to 50% of shared_pool_size.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  7. #7
    Join Date
    Nov 2000
    Posts
    440
    Indeed, i will increase the reserved_size, we have lots of different owners, so lots of packages. We have 2 gig of ram, we have some space free, so a little 100 meg extra will do good.

  8. #8
    Join Date
    Nov 2000
    Posts
    224
    What ver. of Oracle you are using, I know Oracle has some memory leak problems. 817 has this problem which was fixed in 81732 patch.

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