Your comments are correct. I overlooked the "9i" in the title.
But basically it still stands: large_pool_size=1M is far too small and
should be tuned.
Howard J. Rogers <howardjr2000_at_yahoo.com.au> schreef in berichtnieuws
CpMJ9.1345$jM5.3870_at_newsfeeds.bigpond.com...
|
| "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 - 15:17:56 CST
