Re: Resize shared_pool / Avoid Ora-4030

Error 4030 is not about shared pool memory, it's about processes being unable to acquire memory from the o/s for their local workspace.

It's 4032 that is about problems with the shared pool.

The 9i commands to resize the SGA are all likely to have a nasty overhead on big, busy systems when you try to resize downwards - the memory has to be cleared a granule at a time, so Oracle has to work hard at kicking objects out of memory, which means creating all sorts of exlusive locks and pins and doing lots of invalidations. Your 1 CPU for 1 hour seems excessive, though - but it wouldn't surprise me if this area weren't so sensitive to latch issues that you've found a bug.

As an experiment, you might try reducing the memory in steps of 16MB (which is one granule for larger unix systems) to see if this helped.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Stephan Bressler" <sbresslerx_no_spam_at_arcor.de> wrote in message
news:bu5o32$rbt$1_at_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 s`hared_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

>