|Subject:||Resize shared_pool / Avoid Ora-4030|
|Posted by:||Stephan Bressler (firstname.lastname@example.org)|
|Date:||Thu, 15 Jan 2004|
I'm thinking about avoiding ora-4030 errors due to many, many unique big
statements using the memory resizing feature of oracle 9i. Flushing the
shared pool usually is of no help.
Therefor I suggest the following procedure:
1. configure shared_pool_size initially to e.g. 300M
2. each night (or any other low-load-period),
resize shared_pool_size to 100M
3. immediately after that, resize to 300M
This would give a 200M totally free, unfragmented shared_pool.
In theory! In praxis the "alter system set shared_pool_size=100M" lasts
for 1 hour and is still running. CKPT eats up 1 CPU.
I'm running 18.104.22.168 on Linux.
Any ideas / suggestions?