Bad idea to shrink SHARED_POOL_SIZE dynamically?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Bad idea to shrink SHARED_POOL_SIZE dynamically?

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    Bad idea to shrink SHARED_POOL_SIZE dynamically?

    Environment: Oracle 9.2.0.1 on Solaris 8

    Is it a bad idea to shrink SHARED_POOL_SIZE from 300MB to 200M using

    ALTER SYSTEM SET SHARED_POOL_SIZE=200M SCOPE=BOTH?

    It's been taking 15 mins since I issued the command and I am still waiting for the return prompt.

    I notice the checkpoint process has been pretty busy with consistent 50% CPU usage. Does it essentially flush the shared pool and allocate it?

    BTW, I do this because I have to increase JAVA_POOL_SIZE significantly (thus reduce SHARED_POOL_SIZE temporarily) in order to accomodate the 9.2.0.4 patch's requirement for JAVA_POOL_SIZE > 150M.

    Thanks

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I guess Oracle will flush shared_pool but even then it shouldn't take that long.
    I haven't yet applied 9.2.0.4 patchset.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Every time I've attempted to shrink the shared pool it's taken an absolute age. It's still alot quicker to bounce the instance if you can.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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