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 MB RAM session overhead + sort_area_size + hash_area_size
So Automatic Memory management is a best option for configuring SGA..
• What is involved in tuning the SGA?
- Check the statspack report.
- Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer.
- Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool.
- Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.
- Determine how to use keep pool and recycle pool efficiently.
