Significance,impact of ALTER SYSTEM FLUSH SHARED_POOL!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Significance,impact of ALTER SYSTEM FLUSH SHARED_POOL!

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    286
    Hi all!

    Working on Oracle v8i 8.1.6.0.0 with OS as NT!

    Query:

    1.What is the significance and impact of the following command on DB:

    ALTER SYSTEM FLUSH SHARED POOL;

    In order to clean the SHARED SQL AREA in the LIBRARY CACHE,is the above command useful and does it clean all the components of Shared_pool!

    Thanks & Regards,

    Amit.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    This command is not ment to be used as some kind of standard cleaning operation!

    Is it usefull? Sure, but only in the extreme circumstances. For example, when you can't load a larger procedure or a package because your shared pool is so badly fragmented (ORA-4031 and simmilar).

    Does it clean all the components of Shared_pool? Yes, it does. Among other things, it will also flush cashed sequence values, so don't be surprise if you find gaps in your sequences.

    What is its impact? Huge. Everything that was loaded and parsed before will have to be reloaded and reparsed again. Huge I/O and CPU burden.

    Once more, use it only if you must. And if you realy can't find any workaround for your problems and you must use FLUSH SHARED POOL, then it is time to start looking for the real cause of the problems with shared pool and eliminate that cause, not its consequences. Not using bind variables is probably the most common cause that leeds to usage of ALTER SYSTEM FLUSH SHARED POOL.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Feb 2001
    Posts
    286

    Many thanks!Jmodic!!!

    Hi!

    Many thanks Jmodic for the help!

    Amit.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you wanna keep objects you can run dbms_shared_pool before flushing your pool, objects kept with the package are not subject to flush
    If you are using MTS then UGA in pool wont get flushed neither

  5. #5
    Join Date
    Feb 2001
    Posts
    286

    Hi pando!

    Hi pando!

    Many thanks for further info!

    I am having one problem with OEM while taking exp/imp/RMAN backups!

    Any idea on this subject!

    I keep on getting this error:

    VNI:2015:Authnetication error.

    Status:Failed.
    Owner:Sysman

    Thanks & Regards,

    Amit.

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