Oracle SGA Detailed

SGA (System global Area). This is a very large memory range and why it takes a lot of memory to start Oracle.

The SGA is divided into different pools, which we can view v$sgastat by view, as shown below.

sql> Select Pool, sum (bytes) bytes from V$sgastat group by pool;

POOL BYTES
------------ ----------
956298240
Java Pool 16777216
Streams Pool 16777216
Shared Pool 199783376
Large Pool 16777216

Sql>

We can see that the SGA consists of the Java pool, shared pool, large pool (large pool) and non-named pools. The memory that has no name also includes block buffers (cached database blocks), redo log buffers, and memory dedicated to the "fixed SGA" area.

The Oracle SGA memory structure is as follows:


The following are described individually:

1. Fixed SGA

This area can be thought of as the "self-starter" area of the SGA, where Oracle uses the area internally to find other areas of the SGA. In other words, in this memory there are other areas of the address, we can access this area to find the location of other areas!

2. Redo Buffer Redo Buffers

If the data needs to be written to the online redo log, the data is temporarily cached in the redo buffer (redo) before it is written to disk. Because memory-to-memory transfers are much faster than memory-to-disk transfers, using the Redo log buffers can speed up the operation of the database. My understanding is that if there is no such area, then we can access the disk once when we operate the database, and if so, then access the disk a few times before accessing it. The speed of course is much faster.

3. Block Buffer cache

When Oracle writes a database block to disk, and then reads the database block from disk, the database blocks are stored in the block buffer cache. For us, this is a very important area of the SGA. If it's too small, our query will never run out. If it is too large, it can cause other processes to starve (for example, not leaving enough space for the dedicated server to create its PGA, or even start). This is actually the cache data, and is why you do the same operation the second time faster than the first reason, because he can directly from the cache to take it! But if you continue the same operation the next day, it should be slow, because there is no data in that cache that you want to take! (It is commonly said that memory aging, that is, the memory of your data is used by others.) This is the LRU algorithm), in order to make their own data is not so fast aging. Oracle divides this piece of memory into three types:

Default pool: All segment blocks are generally cached in this pool. This is the original buffer pool (which originally had only one buffer pool).
Keep pool: By convention, access to fairly frequent segments will be placed in this candidate buffer pool, and if these segments are placed in the default buffer pool, although they are frequently accessed, it is still possible to age (aging) because other segments require space.
Recycling Pools (Recycle Pool): By convention, access to very random large segments can be placed in this candidate buffer pool, which causes an excessive buffer flush output, and does not bring any benefit, because when you want to use this block again, it may have aged out of the cache. To separate these segments from the default pool and the segments in the hold pool, this will not cause the default pool and keep the blocks in the pool to age and exit the cache.

4. Shared Pool

A shared pool is where Oracle caches some "program" data. When parsing a query, the parsed representation (representation) is cached there. Before completing the task of parsing the entire query, Oracle searches the shared pool to see if the work has been completed. The PL/SQL code that you run is cached in the shared pool, so Oracle will not re-read from disk again the next time it runs. The PL/SQL code is not only cached here, it is also shared here. If 1 000 sessions are executing the same code, only one copy of the code is loaded and shared by all sessions. Oracle stores system parameters in a shared pool. The data dictionary cache (cached information about database objects) is also stored here. Simply put, it's like a kitchen sink where everything goes in a shared pool. My understanding is: this is a common thing, everyone can use. For example, a user makes a query and, before parsing, looks at the shared pool and whether the SQL statement is already cached here. If in, he does not need to go to the analysis, because has been resolved, directly to use it! That's why binding variables can improve Oracle performance!

5. Large Pool

Candidates (large pool) is not named this way because it is a "big" structure (though it may indeed be large). It is called a large pool because it is used for the allocation of large chunks of memory, and the shared pool does not handle such a large chunk of memory.
Before Oracle 8.0 introduced a large pool, all memory allocations were made in the shared pool. If you are using a feature that takes advantage of "chunk" memory allocations (such as shared server UGA memory allocations), it is not good to allocate in a shared pool. In addition, processing, which requires a large amount of memory allocations, uses memory in a different way than the way the shared pool manages memory, so the problem becomes more complex. The shared pool manages memory based on LRU, which is appropriate for caching and reusing data. However, large memory allocations are used after a chunk of memory is obtained, and then there is no need to cache this memory. My understanding is: actually is originally belongs to the shared pool inside some special memory takes out carries on the different processing. Because these memory can be released immediately after use, and the memory of the shared pool does not have a release problem, because it is shared by everyone.

The large pool is dedicated to the following situations:
A shared server connection that allocates the UGA area in the SGA because UGA can be released immediately after a user disconnects!
The parallel execution of statements allows the allocation of message buffers between processes, which are used to coordinate parallel query servers. Once the buffered message is sent, it can be released immediately!
Backup, in some cases, for rman disk I/O buffers. Because these caches can be released immediately after writing to the disk!

6. Java Pool Java Pools

This portion of memory is used when running Java code in the database. For example, write a Java stored procedure that runs inside the server. It is important to note that this memory is not related to the common Java-written b/s system. This portion of memory is used in the database to write stored procedures in the Java language instead of the PL/SQL language.

7. Stream Pool

9ir2 above increases the "flow" technology, more than 10g in the SGA to increase the flow pool. A stream is a tool used to share and replicate data.

SGA Settings

Without a common setting, all settings are adjusted to the system's load, business requirements, and hardware environment. This is just a summary of the setting, to avoid problems caused by improper SGA settings.

1. Automatic SGA Memory Management
With the introduction of the automatic SGA Memory management feature in Oracle 10g, DBAs can set Sga_target to tell Oracle how much of the SGA is available, dynamically adjust the size of each component by Oracle based on the system load, and the corresponding number will be saved in the control file. The size of each component is remembered after the database restarts.
There are several points to note:
? To use automatic SGA memory management, the Statistics_level parameter must be set to typical or all, and the system automatically collects the appropriate information to dynamically adjust the SGA settings.
? You can set the value of a component that Oracle uses for the minimum size of the component.

Parameters that can be dynamically adjusted:
Db_cache_size,shared_pool_size,large_pool_size,java_pool_size.
Parameters to be set manually:
Log_buffer,streams_pool,db_nk_cache_size,db_keep_cache_size,db_recycle_cache_size.

2. Manual SGA Memory Management
1) 32bit and 64bit limits
In 32-bit operating systems, Oracle has a maximum available memory of 1.75g, which means that memory exceeding this limit will not be used by Oracle for sga+pga<=1.75g.
32-bit Oracle can be installed on a 64-bit operating system, and 64-bit Oracle cannot be installed on a 32-bit operating system.

2) View Oracle version:
Sql> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi
PL/SQL Release 10.2.0.1.0-production
CORE 10.2.0.1.0 Production

TNS for Linux:version 10.2.0.1.0-production
Nlsrtl Version 10.2.0.1.0-production

3) each component setting:
Java_pool_size: If it doesn't work on a database-side Java system, 30MB is enough.

Log_buffer: The default is max (number of 512kb,128kb*cpu). General system 1MB enough, the system running large transactions can be set to 2MB, so that 1/3 full write to the log file can continue to write the buffer, no matter how big.

Shared_pool_size: Excessive and small will seriously affect system performance, 1GB memory can be set to 100MB,2GB memory can be set to 150MB,4GB memory can be set to 300MB. Shared pool hit too low the first thing to adjust is the application instead of expanding the shared pool. Using bound variables reduces shared pool requirements, improves hit ratios, reduces shared pool management burdens, and latch competition.

Large_pool_size: Using the private service mode can be set to 30MB, unless necessary, the use of Shared server mode is not recommended.

Db_cache_size: All other available memory outside of the above memory is allocated to the zone.

Summarize
32-bit Oracle:
1G Memory: SHARED_POOL_SIZE=100MB,DB_CACHE_SIZE=0.5GB;
2G Memory: SHARED_POOL_SIZE=150MB,DB_CACHE_SIZE=1.25GB;

64-bit Oracle
4G Memory: SHARED_POOL_SIZE=200MB,DB_CACHE_SIZE=2.5GB;
8G Memory: SHARED_POOL_SIZE=400MB,DB_CACHE_SIZE=5GB;
12G Memory: SHARED_POOL_SIZE=500MB,DB_CACHE_SIZE=8GB

Again, this is just to avoid the general setup of problems caused by improper SGA setup, which needs to be finely tuned according to the specific system load and business logic combined with stackpack.

Oracle SGA Detailed