Famous collections-Oracle System Parameter Optimization

This article is excerpted from the summary report of xuanjicang Yishi's ERP system optimization project for a listed company, hoping to help you.

In a 32-bit operating system, the upper limit of memory used by the Oracle system theoretically is 4 GB, that is, 2 ^ 32 bytes. Oracle will not use any extra part; in a 64-bit operating system, the memory used by the Oracle system is theoretically 2 ^ 64 bytes. For foreseeable future applications, this is almost the same as there is no upper limit. Therefore, when purchasing minicomputer server hardware devices (usually with their own operating systems), consider how many operating systems are used. Otherwise, even if the memory configuration is higher, it will not improve the performance. When a 32-bit operating system is used, if the server device is used as a database server (that is, other software such as application server is not installed on it, it should be appropriate to configure 6 GB memory. In addition to 4 GB memory used by Oracle, 2 GB memory is used to run the process of the operating system itself.

Note 1: If it is a UNIX or linux32-bit operating system, you must set the shmmax parameter of the operating system level. The space of SGA can be larger than shmmax. For details, refer to the Oracle Installation manual and operating system manual;

NOTE 2: operations listed in this section (below) may require certain permissions. In actual work, if you find problems with related permissions, authorize the relevant user in time or operate as a user with a higher permission.

Shows the overall Oracle memory structure:

The memory structure of Oracle mainly consists of SGA and PGA. One part of UGA is managed by SGA, and the other part is managed by PGA, CGA is not controlled by SGA or by PGA. It is always within Uga.

 

Perhaps because SGA is so important, we ignore the existence of PGA, UGA, and CGA. For example, many people do not know the difference between PGA and Uga. In fact, the difference between the two is similar to that between a process and a session. Although process and session are generally one-to-one relationships, they are actually more complicated than this. One obvious case is that MTS is configured, and there are usually more sessions than processes. In this configuration, each process has a PGA, and each session has a Uga. The information contained in PGA is not associated with the session, and the information contained in UGA is based on the specific session. We will introduce PGA, UGA, and CGA as follows:

 

The process global area (PGA) can be understood as process global area or program global area. Its memory segments are in the Process private memory area rather than the shared memory area ). It is a global zone, which means that it contains all the global variables and data structures that code may enter, but it is not shared by all processes. Each ORACLE Server process contains its own PGA, which only contains specific information about the process. The structure in PGA does not need to be protected by latches, because other processes cannot access it. PGA contains information about the operating system resources used by the process and the status of the process, while the Oracle shared resources used by other processes are in SGA. PGA is private rather than shared. This mechanism is necessary because these resources can be cleared and released when the process dies. PGA contains two major areas: Fixed PGA and variable PGA, or PGA heap. The role of fixed PGA is similar to that of fixed SGA. They both contain atomic variables (which cannot be divided), small data structures, and pointers to variable PGA. Variable PGA is a heap. Its chunks can be viewed from fixed table x $ ksmpp. The structure of this table is the same as that mentioned above. PGA heap contains some permanent memory related to fixed table, which is dependent on some parameter settings. These parameters include db_files, log_files, and control_files.

 

UGA (User global area) contains information about a specific session, including the following:

-The duration and running time of the opened cursor

-Package status information, specific variables

-Java session Status

-Applicable roles

-Events tracked by enable

-NLS parameter settings

-Dblink opened

-Session entry control

Like PGA, UGA is also composed of two zones: Fixed UGA and variable UGA, also known as Uga heap. Fixed UGA contains about 70 atomic variables, small data structures, and pointers to variable Uga. The chunks in UGA heap can obtain relevant information from their own sessions by viewing the table x $ ksmup. The structure of this table is the same as that of x $ ksmsp. UGA heap contains some permanent memory segments related to fixed tables, which are dependent on some parameter settings. These parameters include open_cursors, open_links, and max_enable_roles.

 

Unlike other global regions, the call global area is transient. It only exists during data calls. Generally, CGA is required for the lowest-level instance calls, as shown below:

-Analyze an SQL statement

-Execute an SQL statement

-Extract the output of a SELECT statement.

A separate CGA is required for recursive calls. During the analysis of SQL statements, recursive calling of Data Dictionary information is required, because syntax analysis of SQL statements is required, and execution plans must be calculated during statement optimization. When executing PL/SQL blocks, you also need to call them recursively when processing SQL statements. When executing DML statements, you also need to call them recursively to process trigger execution. CGA is a sub-heap of PGA, whether in PGA or in SGA ). An Important Inference of this fact is that a session must be a process during a call. It is important to understand this point when developing an MTS Oracle Database process application. If the number of corresponding calls is large, you need to increase the number of processes to adapt to the increase in the number of calls. Without the data structure in CGA, cballs cannot work. In fact, the data structures related to a call are generally put in UGA, such as SQL area, PL/SQL area, and sort area. They must all be in Uga, because they must always exist and be available between CILS. The data structure contained in CGA must be released after a call. For example, CGA contains information about recursive calls, direct I/O buffer, and other temporary data structures. Java call memory is also in CGA. This memory segment is more intensive than other Oracle memory segments. It is divided into three spaces: stack space, new space, and old space. Chunks that are no longer referenced in new space and old space are collected as unused chunks during call execution according to their length and size during use. New Space chunks the uncollected chunks will be put into the old space chunks during repeated chunks collection. This is the only waste collection in Oracle memory management. Other Oracle memory segments release dead chunks.

 

As mentioned above, the primary component of the Oracle database system and confidence is the SGA (system global area), which consists of three parts: Shared Pool, database buffer cache, and redo log buffer, among them, the shared pool and database buffer cache are the most important for daily system performance adjustment. Now, assuming that Oracle runs on a 32-bit operating system and the system has enough memory to allocate, We can modify sga_max_size, make it equal to 60% of 4G (because PGA also needs to use memory, and it is not included in SGA), that is:

Sga_max_size = 2458 m

 

① Adjust the Shared Pool. The shared pool contains two caches: Library cache and data dictionary cache.

Library cache is used to store shared SQL statements and PL/SQL statements. It is managed using the LRU (least recently used) algorithm. Oracle can use SQL statements that have been cached in it, without re-parsing, we can use the following SQL statement to query the hit rate (hit ratio) of the library cache ):

SQL> select gethitratio

From v $ librarycache

Where namesapce = 'SQL region ';

If the result is less than 90%, the hit rate is not high and the library cache needs to be increased. To store a package in the library cache, run the following command:

SQL> execute dbms_shared_pool.keep ('package _ name ');

To remove a package from the library cache, run the following command:

SQL> execute dbms_shared_pool.unkeep ('package _ name ');

 

Data Dictionary cache is used to maintain information about dictionary objects. You can view the view v $ rowcache, which consists of the following columns:

Column name
Description
 
Parameters
Category of Data Dictionary items
 
Gets
Number of times this dictionary item is obtained
 
Getmisses
Number of failed Information Retrieval for such Dictionary items
 

If getmisses/gets * 100%> 15%, you need to increase the data dictionary cache.

 

It is very important that the library cache and data dictionary cache cannot be adjusted separately, you can only adjust the shared_pool_size (Oracle will automatically allocate the size of the library cache and data dictionary cache, And the allocation process is not controlled by others ). For example, to add a library cache, add shared_pool_size. The following is an empirical formula for allocating the shared pool size:

Shared_pool_size = sga_max_size * 40%

We have obtained sga_max_size = 2458m. Then, according to the above formula,

Shared_pool_size = 2458 * 40% = 983 m

 

② Adjust the database buffer cache. Buffer cache is also part of SGA, which stores data blocks that can be shared by all users. Use the following SQL statement to check the buffer cache hit rate:

SQL> select 1-(Phys. Value/(cur. Value + con. Value) "cache hit ratio"

From v $ sysstat cur, V $ sysstat con, V $ sysstat phys

Where cur. Name = 'db block gets' and

Con. Name = 'consistent gets' and

Phys. Name = 'physical reads ';

If the data result of the preceding command is less than 80%, you need to increase the database buffer cache. For versions earlier than Oracle9i, The init <Sid>. add the db_block_buffers value to ora (because db_block_size is fixed after the database system is installed). The database buffer cache capacity = db_block_buffers * db_block_size; in Oracle9i and later versions, modify the db_cache_size.

 

Empirical Formula for Calculating the size of database buffer cache:

Database buffer cache size = sga_max_size * 40%

We have obtained sga_max_size = 2458m. Then, according to the above formula,

Database buffer cache size = 2458 * 40% = 983 m

 

③ Adjust the PGA size. You can adjust the PGA size by setting the pga_aggregate_target parameter. When this parameter is set, SGA is subtracted from the total system memory that can be used for Oracle routines. You can then allocate the remaining memory to pga_aggregate_target. If we continue with the situation we mentioned above, then,

Pga_aggregate_target = 4G-2458 M = 1638 m

The main parameters affected by PGA are sort_area_size and hash_area_size. They are determined by the number/size of the sorting operation and the number/size of hash_join. In the existing system of jiangdrill, we found that there are many sorting operations and jion operations (the join operation is more complicated than sorting operations). You can consider the following formula:

Sort_area_size = pga_aggregate_target * 30%;

Hash_area_size = pga_aggregate_target * 40%;

 

④ Adjust other parameters.

Db_file_multiblock_read_count indicates the maximum number of blocks read during an I/O operation involving a full continuous scan. The default value is 8. Adding this value can increase the query speed, but the value must be determined based on the operating system. Generally, it is better not to exceed 32 (you can try it again in actual work, determine the most reasonable value ).

 

Java_pool_size, in bytes, specifies the size of the Java storage pool. It is used to store Java methods and class definitions in the shared memory representation, and the Java object transplanted to the Java session space at the end of the call. If you do not use services such as oralce HTTP, set java_pool_size to 64 MB.

 

Large_pool_size specifies the size of the allocation heap of a large pool. It can be used by the Shared Server as the session memory, as the message buffer for parallel execution, and as the disk I/O buffer for RMAN backup and recovery. Generally, large_pool_size = pga_aggregate_target * 10% can be set.

 

Finally, we must realize that the process of system parameter optimization is a complicated process, and there is a big difference between the given parameters of the system for different purposes, for example, some system data does not change frequently and is mainly used for query, while other systems are mainly used for data insertion. Therefore, a universal method or empirical formula does not exist. Even so, some methods or empirical formulas accumulated by many people provide a good starting point. On this basis, through repeated experiments, we can obtain a set of relatively optimized parameter values.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/pathuang68/archive/2009/04/16/4084139.aspx