SGA paramters

[tpclouis]

Dear All,

I have a database in which 2GB can be utilized for SGA. Based on certain calculations I'd come out with certain figures for the parameters which decides the size of SGA.
The values are listed down.

1) Database Buffers = 1340 MB
2) Shared_pool_size = 550 MB
3) large_pool_size = 8740258
4) Large_pool_size = 8 MB
5) shared_pool_reserved_size = 10MB
6)java_pool_size (Commented since not required)
7) Sort_area_size = 7 MB
8) log_buffer = 128 K * number of CPU’s = 262144


It looks like there is misproportion in the relative sizes allocated , mainly to Database Buffers & Shared_pool_size . Can anybody suggest the ideal values for a 2GB SGA. The application requires to run a lot of complex queries from high volume tables. If any thumb rule is there regarding there proportions pls note down.


Thanks in advance

Louis.

[mrvajrala]

Louis,
The task which you have now is not an easy one to answer..
I have found a good starting point for you.. pl click the link below...

http://metalink.oracle.com/metalink/...p_id=1008866.6

if you have any specific question post them here..

[pando]

if you dont have many PL/SQL program units there is not really need to set your shared pool to 500MB
You have large pool repeated
If you use extensively PL/SQL packages, functions, procedures you may want to increase shared pool reserve size
Sort area size is too big if your application is OLTP, too small if it´s DWH
log buffer 1MB should do good, more than that wont have much extra benefits, if you have vast amount of transactions then set log buffer to maximum 3MB, bigger than that you will have 0 extra benefits
You really have 262144 CPUs??? doubtful heh

Your physical RAM is too small to have those memory allocations, dont you see paging swapping going?You have allocated almost 1900MB for SGA for 2GB RAM machine plus sort area is per session....

[tpclouis]

Dear Pando,

My machine is having 4 GB RAM out of which approximately 2 GB is allocated to SGA. It is an OLTP kind of environment but with complex queries for reports which access several high volume tables.

What I've written is the quation what I used to calculate log_buffer . ie -- 128KB * number of CPU's . I have two CPU's . So the log_buffer = 128KB * 2 = 262144 .

What my main worry was the values 1340 MB
and 550 MB for Database Buffers and Shared_pool_size, in a proper proportion ?.


Louis

[julian]

tpclouis,

SHARED_POOL_SIZE should mostly depend on DB_CACHE_SIZE (the block buffers). SHARED_POOL_SIZE is usually 50-150% of DB_CACHE_SIZE depending on the PL/SQL code you have. If you have no PL/SQL code or only just few functions and procedures, SHARED_POOL_SIZE can be even only 10% of DB_CACHE_SIZE.

[patnams]

The Sizes you mentioned seem to be too large.

550 MB for Shared Pool is obviousely large unless you have a very large Userbase or lot of Stored Procedures or Packages.
Again you have a Large Pool to pin any objects into memory in mru areas.

As for Database Buffers, it is entirely dependent on the type of Applications.

Some of the questions to be answered are :
1. Are you comfortable with output speeds of all your queries
2. Are your tables properly indexed to your query requirements.
3. Do multiple applications share the database ?
4. Do the users of DB perform distinct operations. For example Finance Dept and Inventory Dept sharing the DB will perform very distinct operations fetching completely different sets of data.
5. Techincally, what is your DB Hit Ratio. See to it that it typically reaches around >94% in a production environment after running your DB say for about 1 week. Try reducing the Buffer Space until your hit ratio starts going below 95%.
6. Block Size: Buffer size is typically proportional to Block Size (just a thumb rule)


Sort Area Size seems OK, can go upto half a megabyte.

[Calvin_Qiu]

It's not a good configuration of LARGE_POOL_SIZE=8M and SORT_AREA_SIZE=7M, because if you use MTS, shared server process will allocate UGA from LARGE_POOL.