Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1880
  • Last Modified:

SGA size on Oracle 8i ?

I am supposed to configure a new Oracle database.
My server conf. is IBM 5100 with RADI 0 and RAID 5 implementaion.
I have 1 GB of RAM and 108 GB of HDD capacity.

I may run it on LINUX or Windows2000.
I will configure Oracle 8i database.

I just want to know the actual settings for the
SGA in this configuraion.

shared_pool_size : ?
block_size       : ?

Pls. give me the exact size that to be configured for a real time server for both the OS.
If any other information u want then pls. let me know.

If anybody can provide the formula for this according to RAM, OS and HDD size available to set SGA,
I will be thankful to him.


2 Solutions
Helena Markováprogrammer-analystCommented:
This is from http://asktom.oracle.com site. Maybe it will be helpful (http://asktom.oracle.com/pls/ask/f?p=4950:8:158205906086086082::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:71012348056,)

The variable portion of the SGA holds all of the control structures for the SGA itself. The bigger the SGA, the bigger the variable portion.

To approximate the size of the SGA (Shared Global Area), use the following formula:
( (db_block_buffers * block size) + shared_pool_size +
                                              log_buffers) /.9


      From the init<SID>.ora
      DB_BLOCK_BUFFERS = 200
      LOG_BUFFERS = 8192
      SHARED_POOL_SIZE = 3500000
Default Block Size = 2048 bytes
The Block Size is an Operating System specific default.
db_block_buffers * block size + shared_pool_size + log_buffers
      409600 + 3500000 + 8192 = 3917792 bytes
dividing by 0.9 = 4,353,102 bytes or 4M
The division by .9 is used to take into account the variable portion of the SGA
-- this is only an approximation of the actual value.
Our calculations come up to 4353102 but the actual value is 4504072(see below).
To check the actual size of the SGA, issue these commands:
      % svrmgrl
      SVRMGR> connect internal
      SVRMGR> show sga
Example of Output:
Total System Global Area       4504072 bytes <-- total size loaded into memory
              Fixed Size         37704 bytes
           Variable Size       4048576 bytes
        Database Buffers        409600 bytes
            Redo Buffers          8192 bytes  ('log buffers')


There are a lot more variables that go into the equation than you have given us values for your environment. But a reasonable approach is to start with some typical values and change the settings over time to optimize your database usage. I suggest that you start with:

block size: 8K
db block buffers: 30,000
shared pool size: 100,000,000

This will support a moderate load on Oracle and leave memory for the OS, for other applications, and sorting.

As a general rule, I wouldn't set the block size to be less than 8K, and more usually 16K. In general smaller block sizes for OLTP and larger ones for data warehousing.

Shared pool sizing depends on your applications and usage. as a rough and ready, I normally use 50M (+ 50M if installing Java). I have some (really awful) applications that perform better with a much reduced shared pool.

block buffers depends on how much memory you can use for the SGA. For a single database instance on a dedicated server, I would normally start at about 30-35% of total physical memory.

Using the formula posted above, you can then calculate how much memory is left for block buffers. Given the block buffer size, you know how many buffers will fit.

So, as a very rough starting point,
1Gb physical memory -> 300Mb SGA

say 100M for shared pool -> 200M left
block size of 8K => 25600 block buffers

You would then need to get your database / applications under stress, and do several rounds of performance tuning to get 'correct' values.

Normally the default values Oracle supply are conservative, but be wary of changing too many things at once, otherwise you can't determine which one has made the difference.



Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now