CRISTIANO_CORRADI
asked on
ALTER SYSTEM FLUSH SHARED_POOL
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.
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.
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.
but i agree and understand that we can always do it anyway without any impact.
ASKER
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??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
// "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?
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
// "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".
personally I don't agree with comment "never do it", I say "do it if you know what you are doing".
ASKER
Thanks for having added your opinion!
http://www.dba-oracle.com/art_ault_optimization_parameters.htm
Thanks,
Ravi