Resize shared_pool / Avoid Ora-4030

From: Stephan Bressler <sbresslerx_no_spam_at_arcor.de>
Date: Thu, 15 Jan 2004 10:55:52 +0100
Message-ID: <bu5o32$rbt$1@news.mch.sbs.de>

Hi NG,

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 9.2.0.3 on Linux.

Any ideas / suggestions?

Stephan Received on Thu Jan 15 2004 - 03:55:52 CST