This discussion is archived
10 Replies Latest reply: Nov 14, 2012 6:26 AM by EdStevens RSS

migrated to a new server, need to upgrade 8.1.6.0 after pfile changes

666922 Newbie
Currently Being Moderated
Hi,
We have an Oracle RDBMS *8i (8.1.6.0)* running on a 32 bit windows server 2003.
This database needs to be upgraded.

But before that, it was migrated by another company from a small server to a better performing new server.
however i noticed that they kept the same init parameters.. shared pool at 200mb, and large pool at 100mb seems too low..
the new server has 12gb RAM and 16 processors,

as there is no SGA sizing in this version, i feel atleast the shared pool & large pool can be increased.. but being more accustomed to 10g/11g style of tuning, i'm unsure how much i can increase these values?

db_file_multiblock_read_count = 8
db_block_buffers = 99297
shared_pool_size = 271148032
large_pool_size = 104857600
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 115
parallel_max_servers = 5
log_buffer = 32768
db_block_size = 8192
sort_area_size = 65536
sort_area_retained_size = 65536

when i do a "show sga", it comes up to 1.12 gb
  • 1. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    EdStevens Guru
    Currently Being Moderated
    WHO wrote:
    Hi,
    We have an Oracle RDBMS *8i (8.1.6.0)* running on a 32 bit windows server 2003.
    This database needs to be upgraded.

    But before that, it was migrated by another company from a small server to a better performing new server.
    however i noticed that they kept the same init parameters.. shared pool at 200mb, and large pool at 100mb seems too low..
    the new server has 12gb RAM and 16 processors,
    IF you are running on 32-bit windows, you will still be constrained by the 4-gb limit imposed by the physical limits of a 32-bit address space.

    >
    as there is no SGA sizing in this version, i feel atleast the shared pool & large pool can be increased.. but being more accustomed to 10g/11g style of tuning, i'm unsure how much i can increase these values?

    db_file_multiblock_read_count = 8
    db_block_buffers = 99297
    shared_pool_size = 271148032
    large_pool_size = 104857600
    java_pool_size = 0
    log_checkpoint_interval = 10000
    log_checkpoint_timeout = 1800
    processes = 115
    parallel_max_servers = 5
    log_buffer = 32768
    db_block_size = 8192
    sort_area_size = 65536
    sort_area_retained_size = 65536

    when i do a "show sga", it comes up to 1.12 gb
    If you are planning on soon upgrading the DB (as you should) I wouldn't worry about it unless you are getting customer complaints about performance. Your database is already 6 generations behind, you are still on a memory limited OS. I would put as little effort as possible into this database and put maximum effort into migrating both db and OS to something that has been current in this millennium. And that includes upgrading the OS to 64-bits. I don't think its even possible to purchase 32-bit hardware any more, so why would one run a 32-bit os on 64-bit hardware?
  • 2. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    666922 Newbie
    Currently Being Moderated
    i suppose it rounds up to 2gb on a 32-bit installation.. but yes 2 or 4 both are a limit..
    however, i wish to increase it as the users are complaining on this.. and i have to give them some immediate relief after the migration and before upgrade.

    what parameters do you think i should increase?
  • 3. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    EdStevens Guru
    Currently Being Moderated
    WHO wrote:
    i suppose it rounds up to 2gb on a 32-bit installation.. but yes 2 or 4 both are a limit..
    however, i wish to increase it as the users are complaining on this.. and i have to give them some immediate relief after the migration and before upgrade.

    what parameters do you think i should increase?
    Like any other tuning exercise, you have to identify the bottleneck. Look into statspack. The good thing is none of these parms are cast in stone. You can tweak them to your heart's content.
  • 4. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    sb92075 Guru
    Currently Being Moderated
    bigger is not always better
  • 5. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    666922 Newbie
    Currently Being Moderated
    Like any other tuning exercise, you have to identify the bottleneck. Look into statspack. The good thing is none of these parms are cast in stone. You can tweak them to your heart's content.
    true, and at the first look itself 100-200mb looks very meagre.. i'll have to tweak them but it'll have to be in steps that i size them..
    i'd need some suggestions to increase the SGA.. but like i said in this version it seems only the sga components can be increased.. i'll need some suggestions on that please.
  • 6. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    EdStevens Guru
    Currently Being Moderated
    WHO wrote:
    Like any other tuning exercise, you have to identify the bottleneck. Look into statspack. The good thing is none of these parms are cast in stone. You can tweak them to your heart's content.
    true, and at the first look itself 100-200mb looks very meagre.. i'll have to tweak them but it'll have to be in steps that i size them..
    i'd need some suggestions to increase the SGA.. but like i said in this version it seems only the sga components can be increased.. i'll need some suggestions on that please.
    and what if SGA components are not your bottleneck? You said you migrated to another server? As said, bigger is not always better. What about the disk system?

    OK, if you insist of following the Ready, Fire, Aim tuning process, here it is

    1) take statspack reports every 30 minutes for a full business day.
    2) Increase your sga by 42%
    3) take statspack reports every 30 minutes for a full business day.

    compare 1 and 3.
  • 7. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    666922 Newbie
    Currently Being Moderated
    1) take statspack reports every 30 minutes for a full business day.
    2) Increase your sga by 42%
    3) take statspack reports every 30 minutes for a full business day.

    compare 1 and 3.
    thats precisely what i'm intending to do here..
    btw, statspack i usually run at 60 mins, pls tell me any benifit out of 30 mins?

    and my question is how to increase sga?
  • 8. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    EdStevens Guru
    Currently Being Moderated
    WHO wrote:
    1) take statspack reports every 30 minutes for a full business day.
    2) Increase your sga by 42%
    3) take statspack reports every 30 minutes for a full business day.

    compare 1 and 3.
    thats precisely what i'm intending to do here..
    btw, statspack i usually run at 60 mins, pls tell me any benifit out of 30 mins?
    Depends on how finely you want to see issues. The longer the interval, the more that outliers will be averaged out. You could go 60 minutes. You could go 240 minutes. You could go 42 minutes.
    >
    and my question is how to increase sga?
    Change the values for the relevant parameters in the init<orasid>.ora pfile.
  • 9. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    666922 Newbie
    Currently Being Moderated
    Change the values for the relevant parameters in the init<orasid>.ora pfile.
    yes, thats what i intend to do, could you tell me, roughly which ones can be changed
    and just to start with what do you think should be reasonable values..

    i reviewed
    How to determine SGA Size (7.x, 8.x, 9.x, 10g) [ID 1008866.6]
    and found a formula for
    ((db_block_buffers * block size) +
    (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

    and with that it comes up like this..
    99297 * 8192 + 271148032 + 104857600 + 0 + 32768 + 1MB = 1.1 GB

    i've done this before on 10g & 11g also.. and i know how it works, i'm just a little confused on this version + considerations for windows + 32 bit etc..
    so i just need a rough idea of values that can be reasonable to start with
  • 10. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
    EdStevens Guru
    Currently Being Moderated
    WHO wrote:
    Change the values for the relevant parameters in the init<orasid>.ora pfile.
    yes, thats what i intend to do, could you tell me, roughly which ones can be changed
    and just to start with what do you think should be reasonable values..

    i reviewed
    How to determine SGA Size (7.x, 8.x, 9.x, 10g) [ID 1008866.6]
    and found a formula for
    ((db_block_buffers * block size) +
    (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

    and with that it comes up like this..
    99297 * 8192 + 271148032 + 104857600 + 0 + 32768 + 1MB = 1.1 GB

    i've done this before on 10g & 11g also.. and i know how it works, i'm just a little confused on this version + considerations for windows + 32 bit etc..
    so i just need a rough idea of values that can be reasonable to start with
    You already have an 'opening bid' value from the cited MOS note. Since you are really shooting in the dark anyway (Ready, Fire, Aim!) why not just go with it? Why do you think any number you get on this forum is a better "rough idea" than what you get from an MOS note on the subject? There is also an MOS doc explaining memory management on Windows 32-bit, and the implications of dealing with the hard 4gb address limit. I leave it as an exercise to the student to search MOS and find it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points