SGA size on Oracle 8i ?

Posted on 2003-03-20
Medium Priority
Last Modified: 2012-05-05
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.


Question by:rabindra_dba
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8172560
LVL 22

Accepted Solution

Helena Marková earned 100 total points
ID: 8173375
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')


Assisted Solution

watwat earned 100 total points
ID: 8174584

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.


Expert Comment

ID: 8177140
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question