Re: shared_pool_size=800M ???

I think the first question to ask the developers is how they've worked out that number, and whether they mean the 'shared_pool' parameter or the "shared pool" which isn't the same thing anyway.

It is posisble to require an 800M shared pool for valid reasons. There may be a very large number of sharable complex sharable SQL
statemements. You may have FGAC in effect with several different security predicats per table, which give you multiple child cursors for every statement you use. You may be using large numbers of anonymous (but sharable) pl/sql blocks, and so on...

On the other hand, you may be banging out a small number of trivial SQL statement with different literals embedded every time, as others have suggested, and a larger shared pool may cause more problems than it fixes.

-- 
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 appearances:
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO 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
____UK___June


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


"Marc Blum" <blumNOSPAM_at_marcblum.de> wrote in message
news:nah730d7metm3ce03ua1rh21l3m2hgp55p_at_4ax.com...


> 9.2.0.4 EE, SUN Solaris

>

> At a customer's site I installed a database/instance for a new app. All I

knew


> was, that there wouldn't be much code running in the db, perhaps some

plsql and


> a bit of java. The vast majority of work would be done by the clients,

written


> in java by some OO-fellows. I gave the instance 100m shared pool which is

pretty


> much, but to be on the safe side.

>

> Now the developers start complaining, the shared pool ought to have 800m

!!!


> I myself doesn't have any clue why any app needs so much shared pool. So

please


> enlighten me with some serious reasons for that.

>

> thanks in advance

>

>

> --

> Marc Blum

> mailto:blumNOSPAM_at_marcblum.de

> http://www.marcblum.de