Professional Web Applications Themes

Setting shared pool size and Java pool size on 9i - Oracle Server

"Anton Buijs" <aammbuijsxs4all.nl> wrote in message news:3df7905b$0$11753$e4fe514cnews.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 ...

  1. #1

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


    "Anton Buijs" <aammbuijsxs4all.nl> wrote in message
    news:3df7905b$0$11753$e4fe514cnews.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> schreef in berichtnieuws
    > [email]806a5f84.0212110644.111f6be0posting.google.com[/email]...
    > | 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
    >

    Howard J. Rogers Guest

  2. #2

    Default 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 <howardjr2000.au> schreef in berichtnieuws
    CpMJ9.1345$jM5.3870newsfeeds.bigpond.com...
    |
    | "Anton Buijs" <aammbuijsxs4all.nl> wrote in message
    | news:3df7905b$0$11753$e4fe514cnews.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> schreef in berichtnieuws
    | > [email]806a5f84.0212110644.111f6be0posting.google.com[/email]...
    | > | 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
    | >
    |
    |

    Anton Buijs Guest

Similar Threads

  1. Modem pool
    By Me in forum Windows Server
    Replies: 1
    Last Post: June 21st, 12:05 PM
  2. Setting page size to artboard size?
    By bongoman@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 4
    Last Post: February 10th, 10:05 PM
  3. How to pool web service
    By silvee in forum ASP.NET Web Services
    Replies: 0
    Last Post: August 1st, 04:32 PM
  4. Increasing shared pool in v7.3.4 hpux 10.20
    By Anton Buijs in forum Oracle Server
    Replies: 0
    Last Post: July 8th, 10:40 PM
  5. Java Pool
    By Telemachus in forum Oracle Server
    Replies: 1
    Last Post: January 6th, 12:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139