We help IT Professionals succeed at work.

ALTER SYSTEM FLUSH SHARED_POOL

19,057 Views
Last Modified: 2013-12-19
In order to defragment Oracle memory, I have to schedule - every 1-2 hours - the following Oracle instruction:
ALTER SYSTEM FLUSH SHARED_POOL;
In YOUR opinion, what is the best way, and why?
(a) Schedule in cron;
(b) Schedule as an Oracle Job
... as SYS user.
Please describe how would you realize your preferred way to defragment Oracle memory.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Please refer to the below link for some simple optimizing techniques.

http://www.dba-oracle.com/art_ault_optimization_parameters.htm

Thanks,
Ravi
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
I prefer dba_jobs way because we can do that in oracle database itself..

but i agree and understand that we can always do it anyway without any impact.

Author

Commented:
I cannot access to SYS user using TOAD (by Quest Software).  The error is "insufficient privileges".  I have the system password: how can I fix this??
Data Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Okay sujith80, you have centered the problem.
Yesterday we have received thousand errors such the following:
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\
",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          dbms_shared_pool package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          INIT.ORA parameters "shared_pool_reserved_size" and
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size".

We thought that the only way to solve the problem is to ALTER SYSTEM FLUSH SHARED_POOL every 2 hours, so we wrote the following ksh script:

#!/usr/bin/ksh
echo `pwd`
sqlplus /nolog <<EOF
connect / as sysdba;
ALTER SYSTEM FLUSH SHARED_POOL;
exit
EOF

scheduling it in the crontab every 2 hour.  Are you sure that this is NOT correct, and that this can't help us to solve our problem?
We have 10.2.0.2 and hitting bug 5345437, flushing shared pool is the only workaround (if I don't want to restart database). I run it maybe once a week....  we are in the process of upgrading to 10.2.0.4 but it will take some time.

personally I don't agree with comment "never do it", I say "do it if you know what you are doing".

Author

Commented:
Thanks for having added your opinion!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.