+ Reply to Thread
Results 1 to 2 of 2

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

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


    "Anton Buijs" wrote in message
    news:3df7905b$0$11753$e4fe514c@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 schreef in berichtnieuws
    > 806a5f84.0212110644.111f6be0@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
    >




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

    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 schreef in berichtnieuws
    CpMJ9.1345$jM5.3870@newsfeeds.bigpond.com...
    |
    | "Anton Buijs" wrote in message
    | news:3df7905b$0$11753$e4fe514c@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 schreef in berichtnieuws
    | > 806a5f84.0212110644.111f6be0@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
    | >
    |
    |


+ Reply to Thread