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,
01-12-2002, 01:40 PM
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.
01-13-2002, 08:49 AM
Many thanks Jmodic for the help!
01-13-2002, 09:11 AM
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
01-13-2002, 10:12 AM
Many thanks for further info!
I am having one problem with OEM while taking exp/imp/RMAN backups!