Oracle 9i SGA Sizing on 32 bit machine
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Oracle 9i SGA Sizing on 32 bit machine

  1. #1
    Join Date
    Oct 2006
    Posts
    8

    Thumbs down Oracle 9i SGA Sizing on 32 bit machine

    I am in a way to tune oracle 9i Instance on 32 bit windows, system have 4 GB memory, Since i dont want to enable VLM so i want to create SGA upto 1.7 GB. Now the question i have is that how i will manage my instance within 1.7 GB. I applied following settings

    SGA_MAX_SIZE = 1552.57 MB
    DB_CACHE_SIZE = 1200 MB
    SHARED_POOL_SIZE = 256 MB
    LARGE_POOL_SIZE = 56 MB
    JAVA_POOL_SIZE = 8 MB
    LOG_BUFFER = 8 MB
    PGA_AGGREGATE_TARGET = 300 MB

    when i tried to give 600MB to pga_aggregate_target and restart the instance it crashed, not enough memory available on system to accomodate pga_aggregate_target size.
    With above setting i successfully restart the server again, cache_hit_ratio is 60%.

    Now my question is that how i will size the oracle instance within 1.7 GB memory. This 1.7 GB limit is with pga_aggregate_target i mean
    ORACLE_INSTANCE + PGA_AGGREGATE_TARGET < 1.7G ?

    if yes then how i will come to know that how much memory i will give to
    shared_pool
    db_cache_size
    pga_aggregate
    sga_max_size
    Please suggest me for 1.7GB SGA.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    they are all completely application dependant, how much memory you need for your shared pool depends on how much sql you use. buffer cache - how much data you are dealing with.

    So there is no answer to your question, you need to know how your aplication behaves

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    yeah i agree...it doesnt mean that 1.7Gb memory will make ur system
    attain peak performance....but maybe it will suffice at 500MB...then
    gradually adjust it from there as u tune it. Tuning is sometimes
    trial n error.

    I hope this helps
    Behind The Success And Failure Of A Man Is A Woman

  4. #4
    Join Date
    Oct 2006
    Posts
    8
    I need to adjust SGA/PGA to keep in my mind that i have limit of 1.7GB on windows, regarding performance i dont care right now about perfromance. I just want to know that how i will distribute the memory with in 1.7GB.

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    hmnn...maybe u have to increase gradually by 10Mb its effect to the
    whole sum might be great.
    Behind The Success And Failure Of A Man Is A Woman

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by navisd
    I need to adjust SGA/PGA to keep in my mind that i have limit of 1.7GB on windows, regarding performance i dont care right now about perfromance. I just want to know that how i will distribute the memory with in 1.7GB.
    if you dont care about performance, why do you need to adjust it at all?

  7. #7
    Join Date
    Oct 2006
    Posts
    8
    Performance is the next step first thing i need to utilize memory upto 1.7 G as system have 4 GB memory.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why do you 'need' to, if you arent changing it for performace why are you changing at all

  9. #9
    Join Date
    Oct 2006
    Posts
    8
    Well performance is one concern but i again say i want to utilize maximum memory on windows box as there is 4 GB ram and most of it is free.Right now OS utilizing some memory and oracle utilizing only 1 GB of ram, 2-2.5GB is free most of the time. So i decided to utilize the free memory. This is the reason behind all this. I know how to enable VLM. As my favourite OS is Linux, I have configured 12GB instance on 32 bit machine with 16GB RAM. I successfully hit more than 1000 active users on that server and it didn't gave any error. So here my goal is to create 1.7GB oracle instance on 32 bit windows plateform which will work efficiently. Please suggest me how i will size my sga with in 1.7GB Limit and also how much memory i will give to PGA. Remember I need efficient, effective, optimized settings with 1.7GB restriction.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you are coming at it from totally the wrong direction, you need to work out what you need, how your application works and how much memory you need.

    You cant just say give Xmb to shared pool, Ymb to buffer cache, it just doesnt work like that.

    It makes no sense to use memory just cos you have it - why not use it for something that actually needs it

Posting Permissions

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



Click Here to Expand Forum to Full Width