Shulou(Shulou.com)06/01 Report--

This article mainly introduces what Oracle SGA means. It has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

SGA (System Global Area). This is a very large memory range, which is why oracle takes up a lot of memory after opening.

The SGA is divided into different pools, which we can see through the view v$sgastat, 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 SGA consists of java pool, shared pool, large pool, and unnamed pool. The unnamed block of memory includes block buffers (cached database blocks), redo log buffers, and memory dedicated to the "fixed SGA" area.

Oracle SGA memory structure is as follows:

The following are introduced one by one:

Fixed SGA Fixed SGA

Think of this area as a "bootstrapping" area in the SGA that Oracle uses internally to find other areas of the SGA. In other words, there are addresses of other areas in this memory, and we can find the location of other areas by accessing this area!

2. Redo Buffer

If data needs to be written to the online redo log, it is buffered temporarily in a redo buffer before being written to disk. Because memory-to-memory transfers are much faster than memory-to-disk transfers, using redo log buffers can speed up database operations. My understanding is that if there is no such area, then we can access the disk once for every database operation, and if there is, we can access the disk after many operations. Of course, the speed was much faster.

Block Buffer Block Buffer

Oracle stores database blocks in a block buffer cache before writing them to disk and after reading them from disk. For us, this is a very important area in the SGA. If it is too small, our query will never run out. If it is too large, it will starve other processes (for example, not leaving enough room for dedicated servers to create their PGA or even launch). This is actually cache data, which is why the second time you perform the same operation is faster than the first time, because it can be taken directly from this cache! But if you continue the same operation the next day, it should be slower, because there is no data you want to fetch in that cache! (This is commonly referred to as memory aging, where the memory that caches your data is used by others.) This is the LRU algorithm), in order to make their own data not so fast aging. Oracle divides this memory into three types:

Default pool: All segment blocks are generally cached in this pool. This is the original buffer pool (originally there was only one buffer pool).

Keep pool: Traditionally, fairly frequently accessed segments are placed in this candidate buffer pool, and if these segments are placed in the default buffer pool, they may age because other segments need space despite frequent access.

Recycle pool: Conventionally, large blocks of random access can be placed in this candidate buffer pool, causing excessive buffer flush output and no benefit, because by the time you want to reuse the block, it may have aged out of cache. You want to keep these segments separate from those in the default and retention pools so that blocks in the default and retention pools do not age out of cache.

Shared pool (shared pool)

A shared pool is where Oracle caches some "program" data. When a query is parsed, the resulting 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 you run is cached in the shared pool, so Oracle doesn't re-read from disk the next time it runs. PL/SQL code is not only cached here, but shared here. If there are 1,000 sessions executing the same code, only one copy of the code is loaded and shared by all sessions. Oracle stores system parameters in shared pools. The data dictionary cache (cached information about database objects) is also stored here. Simply put, it's like a kitchen sink. Everything goes into a shared pool. My understanding is that this is a common thing, everyone can use it. For example, a user makes a query, before parsing, check the shared pool, whether the sql statement has been cached here. If it was, he would not need to analyze it anymore, because he had already analyzed it and could use it directly! This is why binding variables can improve oracle performance!

5, large pool

A large pool is not named that way because it is a "large" structure (although it may be large). It is called a large pool because it is used for allocating large blocks of memory, and shared pools do not handle such large blocks of memory.

Before Oracle 8.0 introduced large pools, all memory allocations were made in shared pools. If you use features that take advantage of "chunky" memory allocation (such as shared server UGA memory allocation), it's not good if they're all allocated in shared pools. In addition, processing (which requires a lot of memory allocation) uses memory in a different way than shared pools manage memory, so the problem becomes more complicated. Shared pools manage memory according to LRU, which is good for caching and reusing data. However, block memory allocation is to get a block of memory and use it, and then stop there, there is no need to cache this memory. My understanding is: In fact, some of the special memory that originally belonged to the shared pool was taken out for different processing. Because this memory can be released immediately after running out, and shared pool memory does not have a release problem because it is shared by everyone.

Large pools are designed for:

Shared server connections for allocating UGA zones in SGA, because UGA can be released immediately after a user disconnects!

Statement allows for the allocation of message buffers between processes that are used to coordinate parallel query servers. Once the buffer message is sent, it can be released immediately!

Backups, in some cases used for RMAN disk I/O buffers. Because after writing to disk, these caches can be released immediately!

Java pool Java pool

This memory is used when running Java code in a database. For example, write Java stored procedures to run inside the server. Note that this memory has nothing to do with common Java B/S systems. Use JAVA language instead of PL/SQL language to write stored procedures in the database will use this part of memory.

7. Stream pool

Flow technology added above 9iR2 and flow pool added to SGA above 10g. Streams are tools for sharing and replicating data.

SGA Settings

There is no universal setting, all settings have to be adjusted according to the load of the system, business requirements and hardware environment. This is just a summary of the general settings to avoid problems caused by improper SGA settings.

Automatic SGA memory management

Oracle 10g introduces automatic SGA memory management feature, DBA can set SGA_TARGET to tell Oracle how much SGA memory is available, Oracle dynamically adjusts the size of each component according to the system load, and the corresponding number will be saved in the control file, so that the database can remember the size of each component after restarting.

A few points need to be noted:

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 a value for a component that Oracle uses as the minimum size for that component.

Dynamically adjustable parameters:

DB_CACHE_SIZE，SHARED_POOL_SIZE，LARGE_POOL_SIZE，JAVA_POOL_SIZE。

Parameters to be manually set:

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

On a 32-bit operating system, Oracle's maximum available memory is 1.75g, which means SGA+PGA 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)Component settings:

JAVA_POOL_SIZE: 30MB is enough if you don't use the Java database system.

LOG_BUFFER: Default is MAX(512KB,128KB* number of CPUs). Generally, 1MB is enough for a system. For a system running large transactions, it can be set to 2MB, so that when 1/3 of the log file is full, the buffer can continue to be written. It is meaningless to be larger.

SHARED_POOL_SIZE: Too large or too 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 hits are too low. The first thing to tune is the application, not to grow the shared pool. Using binding variables reduces pool requirements, improves hit rates, reduces pool management burden, and reduces LATCH contention.

LARGE_POOL_SIZE: Use dedicated server mode can be set to 30MB, shared server mode is not recommended unless necessary.

DB_CACHE_SIZE: Except for the above memory, other available memory is allocated to this area.

summary

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, the above is only a general setting to avoid problems caused by improper SGA settings, which needs to be fine-tuned according to specific system loads and business logic in combination with tools such as Stackpack.

Thank you for reading this article carefully. I hope that the article "What does Oracle SGA mean" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.