Oracle memory allocation and tuning __oracle

L Preface <?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

The management of Oracle memory, up to 9ir2, is a very important aspect, mismanagement, will likely cause serious performance problems to the database. We'll take a step-by-step approach to all aspects of memory management.

L Overview

Oracle memory can be divided into the system global zone and the process global area in terms of shared and private, that is, the SGA and the PGA (process global area or private global areas). For memory within the SGA area, it is shared global, and on UNIX, a shared memory segment (can be one or more) must be set up for Oracle, because Oracle is a multiple process on UNIX, and Oracle is a single process (multiple threads) on Windows. So you don't have to set up a shared memory segment. The PGA is part of a process (thread) private area. In the case of Oracle using Shared server mode (MTS), part of the PGA, i.e. UGA, is put into the shared memory large_pool_size.

For the SGA section, we can see through the Sqlplus query:

Sql> select * from V$SGA;

NAME VALUE

--------------------              ----------

Fixed Size 454032

Variable Size 109051904

Database buffers 385875968

Redo buffers 667648

Fixed Size

Oracle's different platforms and different versions may not be the same, but for the determination of the environment is a fixed value, which stores the contents of the SGA components of the information, can be seen as a guide to build the SGA area.

Variable Size

Contains memory settings such as Shared_pool_size, Java_pool_size, large_pool_size, and hash table, header information (such as x$bh memory consumption), which manages memory structures such as data buffers

Database buffers

Refers to the data buffer, which contains the default pool, Buffer_pool_keep, buffer_pool_recycle three-part memory in the 8i. Include Db_cache_size, Db_keep_cache_size, Db_recycle_cache_size, db_nk_cache_size in the 9i. Note here that the sum of three parts of memory in 8i is db_block_buffers*db_block_size.

Redo buffers

Refers to the log buffer, Log_buffer. The extra point here is that the query values for V$parameter, V$sgastat, V$SGA may be different. The value inside the V$parameter is the value that the user sets in the initialization parameter file, V$sgastat is the log buffer size that is actually allocated by Oracle (because the allocation value of the buffer is actually discrete, nor is it allocated with block as the smallest unit), v$ The value of the query in the SGA is that after Oracle allocates the log buffers, some protection pages are set up to protect the log buffers, and we often find that the protection page size is about 11k (different environments may be). Refer to the following content

Sql> Select substr (name,1,10) name,substr (value,1,10) value

2 from V$parameter where name = ' Log_buffer ';

NAME VALUE

--------------------     --------------------

Log_buffer 524288

Sql> select * from V$sgastat;

POOL NAME BYTES

----------- -------------------

FIXED_SGA 454032

Buffer_cache 385875968

Log_buffer 656384

Sql> select * from V$SGA;

NAME VALUE

--------------------              ----------

Fixed Size 454032

Variable Size 109051904

Database buffers 385875968

Redo Buffers 667648

About the role of each part of memory, refer to the Oracle architecture, no longer described here.

L the size of the SGA

So now we're going to look at the settings for the memory parameters. In fact, there are always different optimal settings for a particular environment, and there is no universally applicable optimal solution. But why do we have to talk about setting this up here, just for a purpose, to avoid excessive mistakes. In fact, we don't have any system running information to make adjustments until any production system is formally put into use, so there are only two possibilities, one based on the document recommendation, the other based on experience. In contrast, the settings based on experience are more reliable than those based on the document. Especially those 24*7 systems, we have to reduce the occurrence of errors. Then we try to understand the different systems of different applications of the specific settings, so as to provide a reference information to everyone.

In order to draw a reference setting, we must assume a reference environment. All of the following settings are based on the assumption that the hardware server only considers the existence of the operating system and the database, and in this single environment we consider the setting of the memory.

Before setting the parameters, let's start by asking ourselves a few questions.

One: How large is the physical memory

Second: The operating system estimated how much memory to use

Three: Whether the database is using a file system or a bare device

Four: How many concurrent connections

Five: Whether the application is an OLTP type or an OLAP type

Based on the answers to these questions, we can roughly estimate the memory settings for the system. So let's discuss it one at a time, first of all, how much physical memory is the easiest question to answer, and then the operating system estimates the amount of memory to use. From experience, not too much, usually should be within 200M (does not contain a large number of process PCB).

Next, we will explore an important issue, that is, the problem with the file system and the bare devices, which is often easily overlooked by us. The operating system uses a lot of buffer to cache operating system blocks for the file system. This way, when the database gets a block of data, although there is no hit in the SGA, it may actually be obtained from the operating system's file cache. If the database and operating system support asynchronous IO, then in fact when the database write process DBWR write disk, the operating system in the file cache marked the block for delay write, wait until the disk is actually written to the operating system to notify the DBWR write disk completed. For this part of the file cache, the required memory may be larger, as a conservative estimate, we should consider 0.2--0.3 times times the memory size. However, if we are using a raw device, we do not consider this partial caching problem. In this case, the SGA has the opportunity to increase the size.

As to how many concurrent connections the database has, this is actually related to the size of the PGA (there are also large_pool_size under MTS). In fact, this problem should also be related to OLTP types or OLAP types. Oracle tends to be able to use MTS for OLTP types, using standalone mode for OLAP types, and OLAP may involve a large number of queries for sorting operations that affect the use of our memory. Then all the problems are combined, in fact, are mainly reflected in the size of the UGA. The UGA mainly contains the following parts of memory settings

Sql> Show Parameters Area_size

NAME TYPE VALUE

------------------------------------               -------          -------------

Bitmap_merge_area_size integer 1048576

Create_bitmap_area_size integer 8388608

Hash_area_size integer 131072

Sort_area_size integer 65536

Sql>

In this part of the memory we are most concerned about sort_area_size, this is when the query needs to sort, the database session will use this part of memory to sort, when the memory size is insufficient, use temporary table space for disk sorting. The setting of this parameter is important due to the fact that disk sorting efficiency and memory sorting efficiency differ by several orders of magnitude. These four parameters are set for the session, the size of the memory used by a single session, not the entire database. It is an extremely serious mistake to occasionally see someone misunderstand the size of the parameter that is used by the entire database. If MTS is set, the UGA is assigned to large_pool_size, which means that it is placed in shared memory, which can be shared between different processes (threads). On this basis, we assume that the database exists concurrent execution of the server process to 100, according to the above our 4 parameters under the oracle8.1.7 of the default value, we calculate the independent mode PGA's approximate size. Since sessions do not often use create_bitmap_area_size and bitmap_merge_area_size, we usually do not sum four parameters. Taking into account information such as variables, stacks, and so on that are stored in a session other than these four parameters, we estimate 2M, then 100 processes are most likely to use the 200M PGA.

Now, based on these assumptions, let's see how much memory the SGA can actually achieve. On a 1G memory server, the memory we can allocate to the SGA is approximately 400-500m. If 2G of memory, approximately can be divided into 1G of memory to sga,8g memory can be divided into 5G of memory to the SGA. Of course we are here with the default sort part of the memory sort_area_size=64k, if we need to adjust the parameters such as this parameter and hash_area_size, then we should measure the number of concurrent processes to consider this problem.

In fact, we are often more accustomed to expressing such questions through intuitive formulations:

OS using Memory +sga+ number of concurrent execution processes * (SORT_AREA_SIZE+HASH_ARA_SIZE+2M) < 0.7* Total Memory

(The formula is dead, the system is alive, the actual application of the adjustment does not have to box formula, which is only a reference suggestion)

In our practical applications, if the use of the bare devices, we can appropriately increase the SGA (if necessary). Since almost all operating systems currently use virtual caching, the fact is that if the SGA settings are large it will not cause errors, but it may appear that frequent memory pages are swapped in and out (page in/out). If this phenomenon is observed at the operational system level, then we need to adjust the memory settings.

L parameter setting in SGA

Log_buffer

For the size setting of the log buffer, I usually don't think there are too many suggestions, because after referring to the trigger condition of LGWR write, we will find that it is usually not significant to exceed 3M. As a formal system, you might consider setting this part to log_buffer=1-3m size, and then adjusting it for specific situations.

Large_pool_size

For large buffer pool settings, if MTS is not used, it is recommended that 20-30m be sufficient. This section is mainly used to save some information in parallel queries, and Rman may use it when backing up. If MTS is set, because the UGA part is to be moved here, the settings for this part of the size need to be considered in detail based on the server process number and the settings of the associated session memory parameters.

Java_pool_size

If the database does not use Java, we usually think that the reserved 10-20m size is sufficient. In fact, it can be less, or at least 32k, but it is related to the component (such as HTTP server) when the database is installed.

Shared_pool_size

This is by far the most controversial part of the memory setup. According to a lot of documentation, this part should be almost the same size as the data buffer. But that's not actually the case. The first thing we need to do is to get into the problem of this part of memory, which is to cache the parsed SQL, so that it can be reused and no longer resolved. The reason for this is that the database performs hard parsing for a new SQL (the same SQL that is not already resolved in Shared_pool), which is a resource-intensive process. And if it does, it's only soft analysis (looking for the same SQL in a shared pool), and the resources consumed are significantly reduced. So we expect to share some more SQL, and if the parameter is not set large enough, there is often a ora-04031 error, which means that in order to parse the new SQL, there is not enough contiguous free space available, so naturally we expect the parameter to be larger. But the increase in this parameter, but also has a negative impact, because the need to maintain a shared structure, the increase in memory will also make the aging of SQL more expensive, resulting in a large number of management overhead, all of which can cause serious CPU problems.

In a larger system with full use of binding variables, the overhead of shared_pool_size should normally remain within 300M. Unless the system uses a large number of stored procedures, functions, packages, such as Oracle ERP applications, may reach 500M or even higher. So we assume a 1G memory system that might consider setting this parameter for 100M,2G system considerations set to 150m,8g system can be considered set to 200-300m.

For a system that is not fully used or is not using a binding variable, this may pose a serious problem for us. The so-called no use bind Var sql, we call the <