10 Replies Latest reply: Nov 14, 2012 8:26 AM by EdStevens RSS

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

    666922
      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
          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
            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
              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
                bigger is not always better
                • 5. Re: migrated to a new server, need to upgrade 8.1.6.0 after pfile changes
                  666922
                  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
                    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
                      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
                        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
                          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
                            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.