• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17253
  • Last Modified:

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.
0
CRISTIANO_CORRADI
Asked:
CRISTIANO_CORRADI
1 Solution
 
ravindran_eeeCommented:
Please refer to the below link for some simple optimizing techniques.

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

Thanks,
Ravi
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
CRISTIANO_CORRADIAuthor 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??
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sujith80Commented:
>> ALTER SYSTEM FLUSH SHARED_POOL;
Never!
Oracle has a mechanism of parsing your queries/code and storing the parsed queries for re-use. Shared pool also stores the data dictionary definitions that are accessed frequently. This feature enables your code to skip the expensive parse phase and run faster. If you flush the shared pool frequently, it spoils the whole purpose of this. You should not do this as a scheduled activity.
0
 
CRISTIANO_CORRADIAuthor 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?
0
 
playernovisCommented:
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".

0
 
CRISTIANO_CORRADIAuthor Commented:
Thanks for having added your opinion!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now