Re: Setting shared pool size and Java pool size on 9i

"Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:3df7905b$0$11753$e4fe514c_at_news.xs4all.nl...
> The problem is in the large pool I think.
> Large pool is used for:
> a. pararallel executions
> b. dbwr_io_slaves
> c. in a MTS (multithreaded server) environment.

Just to be picky, but since the thread title indicates the use of 9i, it's not called MTS anymore, but "shared server"

> I assume the first two are not applicable here.
> Are you running MTS? (Check the mts% init.ora parameters)

And hence there aren't any MTS parameters to check in the init.ora. They are now 'DISPATCHERS' and 'SHARED_SERVERS' (and MAX_DISPATCHERS and MAX_SHARED_SERVERS etc).

>If not: set
> large_pool_size=0, any other setting is a waste.
>

Er, it's a bit iffy to set it to zero when his error message clearly indicates that *something* is using the large pool. Without a large pool, it will then attempt to pinch memory from the shared pool, and he's already got enough out of memory errors there to make such a move highly dubious.

> In MTS mode the PGA and/or UGA (I always forget: one of them or both)
> memory moves to the shared pool.
> When large pool is set, it moves from the shared pool to the large pool.
> This is to prevent fragmentation in the shared pool.
> Or you set large_pool_size=0 (PGA stays in the shared pool) or you set it
to
> an appropriate value, and 1M is not appropriate. The error mentions the
> large pool is the problem.
>
> In V8.0 large pool was taken from the shared pool, so shared_pool_size -
> large_pool_size remains for usual shared pool purposes. Don't know if this
> is still the case in V8.1 (what are you running??)
>

I'm not entirely sure that was ever true... large_pool_size was always an add-on to the shared_pool_size, not a subtraction from it. But whatever: he's using 9i, it would seem, and it certainly isn't that now.

> A query on V$SESSTAT (join with V$STATNAME to find the proper statistic)
> shows you the amount of PGA each session currently uses and can be used to
> find a proper value for the large_pool_size parameter.
> The size is very application depended but if I recall it well it was about
> 250Kb per session in my situation.
> V$SGASTAT shows how much of each pool is used (but shared_pool free_memory
> is not the real free memory, it's better described as "never used")
> Check the description of V$SHARED_POOL_RESERVED to see what it can tell
you
> (like how many ORA-4031 errors have occured since db uptime and how much
> memory was lacking).
>

Make the large_pool 25M. It's unlikely to need to be much bigger than that.

And then sort out the fragmentation on the shared pool. Use dbms_shared_pool.keep to pre-load and keep any packages and procedures that are frequently used at instance startup. Check your shared_pool_reserve_size, for sure. Make sure your SQL is using bind variables. Consider setting CURSOR_SHARING=SIMILAR if not.

Regards
HJR
>
> Eric Giguere <rikgig_at_yahoo.com> schreef in berichtnieuws
> 806a5f84.0212110644.111f6be0_at_posting.google.com...
> | Hi all
> |
> | Ok, we have quite a nasty problem here. We are currently developping a
> | web service using Tomcat, jsps and so on. We are running on a Solaris
> | server with Oracle on the same machine as DB server.
> |
> | On one specific server, we have 1Gig of RAM and we constantly hit
> | ourselves with out of memory errors from Oracle. Typically, we hit
> | this error :
> |
> | ORA-04031: unable to allocate 8132 bytes of shared
> | memory ("large pool","unknown object","cursor work
> | he","qesaQBInit:buffer")
> |
> | And often, we also have the "shared pool" instead of the large pool.
> | Our system is of course aimed at serving many different users at the
> | same time. Right now, logging in with 5 people is enough to make it go
> | down in about 30 - 45 minutes, Oracle getting out of memory in one of
> | those pools (shared or large).
> |
> | Our current settings for the shared pool is : 592 Megs! The large pool
> | is at 1 Meg.
> |
> | Can anyone explain the dynamics behind those pools and the reasonable
> | values to put there so that the system may, for instance, serve 20
> | clients simulatneously?
> |
> | Any help on this issue will be greatly appreciated :)
> | Eric
>
Received on Wed Dec 11 2002 - 13:42:15 CST