| how find SGA size is sufficient or not? [message #335585] |
Tue, 22 July 2008 17:46  |
skumar.dba
Messages: 82
Registered: October 2007
|
Member |
|
|
Experts,
how to find weather SGA size is sufficient or not. i am observing from EM console that DB_Buffer_cache is undersized.
is there any best way to find DB_BUFFER_CACHE,LIBRIRARY_CAHCE, SHARED_POOL SIZE is sufficient or not.
|
|
|
|
|
|
|
|
| Re: how find SGA size is sufficient or not? [message #335739 is a reply to message #335609] |
Wed, 23 July 2008 08:07  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
Sizing SGA 
Define the SGA
Oracle Instance is made up of Back ground process and memory, oracle uses shared memory for its working this shared memory is composed of SGA and PGA.
Simply stated, the system global area (SGA) is just shared memory structures that are created at instance startup, hold information about the instance and control its behavior.
• How you would configure SGA for a mid-sized OLTP environment?
Oracle Database 10g automates the management of shared memory used by an instance and liberates administrators from having to manually configure the sizes of shared memory components.
Automatic Shared Memory Tuning significantly simplifies Oracle database administration by introducing a more dynamic, flexible and adaptive memory management scheme. It makes more effective use of available memory therefore reducing the cost incurred on acquiring additional hardware.
In automatic memory management feature we can set value of SGA_TARGET in parameter file. Oracle it self manage space in SGA as per the requirement.
Allowing Oracle to take Control
There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter.
1. Take a look and see if you are already in automated sizing of SGA
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- --------
sga_target big integer 0
2. Alter system to begin automated sizing of SGA
SQL> alter system set sga_target=216m;
System altered.
3. Done
There is no thumb rule to configure SGA
SGA is made up of 5 parts
Suppose if we consider SGA of 1 GB than and we allocate
1. Shared pool as 48% of SGA
2. Database buffer as 48% of SGA.
3. Log Buffer as 4 MB of SGA.
4. Java pool 20 MB
5. Large pool 20 MB.
But if SGA is more than 1 GB suppose 10 GB than above mention % of all part will not suit, like for 1GB 4 MB was log buffer but for 10GB 40MB for log buffer may be too much.
Suppose only Oracle is running on the server and MTS is chosen for OLTP.
Reserve 10% of RAM for UNIX/Linux or 20% of RAM for Windows, the rest of RAM is allocated to SGA.
Log_buffer=3-4M
Large_pool_size: For dedicated Oracle server, 20-30M is enough. For MTS, the UGA will be here. Estimate parallel connection and MTS server processes.
Java_pool_size=10M
Shared_pool_size: If all the SQL statements that sent to ORACLE are using bind variable adequately, then 300M is enough in most cases and it should greater than 100M depending on total RAM.
Data buffer: All the rest RAM should be allocated to Data buffer.
Below is some referenced materials related to this issue:
If you only have Oracle on the server, start by reserving 10% of RAM for UNIX/Linux or 20% of RAM for Windows. With whatever RAM is left-over for SGA Sizing and PGA Sizing
For dedicated Oracle servers, the maximum total RAM SGA size can be computed as follows:
OS Reserved RAM — This is RAM required to run the OS kernel and system functions, 10% of total RAM for UNIX/Linux, and 20% of total RAM for Windows.
Oracle Database Connections RAM — Each Oracle connection requires OS RAM regions for sorting and hash joins. (This does not apply when using the Oracle multithreaded server or pga_aggregate_target.) The maximum amount of RAM required for a session is as follows:
2 megabytes RAM session overhead + sor_area_size + hash_area_size
So Automatic Memory management is a best option for configuring SGA..
• What is involved in tuning the SGA?
1. Check the statspack report.
2. Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer.
3. Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool.
4. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.
5. Determine how to use keep pool and recycle pool efficiently.
|
|
|
|
| Re: how find SGA size is sufficient or not? [message #335742 is a reply to message #335739] |
Wed, 23 July 2008 08:14 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I just read the last paragraph and I have to say:
2. Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer.
Wrong. Hit ratio is meaningless as it just misleading.
3. Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool.
Wrong. Hit ratio is meaningless as it just misleading.
4. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.
Wrong. Hit ratio is meaningless as it just misleading.
5. Determine how to use keep pool and recycle pool efficiently.
Wrong. Never use these pools unless you are an expert.
Regards
Michel
[Updated on: Wed, 23 July 2008 08:14] Report message to a moderator |
|
|
|
| Re: how find SGA size is sufficient or not? [message #335779 is a reply to message #335742] |
Wed, 23 July 2008 13:17 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
Micheal in my previous post the point that i mention was for what is involved in tunning SGA?,Answer i gave was in general... nothing specific... Any ways things which are involved is as below...
 TUNNING SHARED POOL
Shared pool is divided in to two parts
Library cache
Dictionary cache
• Shared pool should not be free.
• In ideal case it should be nearly 4-5 % free, free below it will apply LRU.
• It is good if it is filled.
TUNNING LIBRARY CACHE (USE IN REUSABILITY)
There is no parameter to define Library cache Size.
Goal
• Reusability of SQL.
• Increase hit Ratio.
If hit ratio is less Solution is increase size of shared pool.
Reasons For library cache miss,
1. Oracle is applying LRU
2. INVALIDATIONS
What is invalidation?
If already stored SQL in library cache is marked as invalid.
Why this happen?
Whenever objects involved in a SQL are identified after SQL is loaded into library cache.
Why pinning important PL/SQL and simple SQL into library cache?
So that oracle does not apply LRU to important SQL.
PL is stored in Database and SQL is stored in Application.
Keeping important PL/SQL code in memory.
==>/rdbms/admin/dbmspool.sql
TUNNING DICTIONARY CACHE
Dictionary cache stores details of Data dictionary.
Solution :-if dictionary cache hit ratio is less than 95% increase size of shared pool.
Dictionary cache hit ratio is there for performance tunning
Hit ratio should be greater than 95%.
TUNNING DATABASE BUFFER
TUNNING LOG BUFFER
Oracle must flush log buffer immediately. Tunning is related to the size of log buffer.
Size has 2 criteria.
1. No space is available in log buffer.
2. Not enough space is available in log buffer.
Solution increase the size of log buffer.
JAVA POOL and Large Pool are also in SGA... so if required we have to tune...
[Updated on: Thu, 24 July 2008 01:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
?