"Go" Oracle Memory allocation and tuning summary

Transfer from http://blog.itpub.net/12272958/viewspace-696834/

Have always wanted to summarize Oracle memory tuning knowledge, recently just optimize a database memory parameters, find some data and Google a lot of. Now log it down and make a backup.

First, overview:             oracle memory can be divided into the system global and process global regions by shared and private perspectives, the SGA and PGA (Process global area, or private global area). For memory in the SGA region is shared globally, on UNIX, you must set up shared memory segments (which can be one or more) for Oracle, because Oracle is a multi-process on UNIX, and Oracle on WINDOWS is a single process (multiple threads), so you don't have to set Shared memory segment. The PGA is a private area of the process (thread). In the case of Oracle using Shared server mode (MTS), part of the PGA, that is, UGA, is placed in the shared memory large_pool_size.          Send map Oracle Memory architecture, according to the above display can see the key parameters and parameter name:         for the SGA section, We can see: sql> select * from V$sga; name                V in Sqlplus alue ----------           --------------------Fixed Size                   454032 variable Size             109051904 data Base buffers             385875968 redo buffers           &NBS P    667648   Fixed Size:The different platforms and versions of Oracle may not be the same, but for determining the environment is a fixed value that stores information about the components of the SGA, which can be seen as the region that guides the establishment of the SGA.Variable Size:Includes memory settings such as Shared_pool_size, Java_pool_size, large_pool_size, etc.Database buffers:Refers to the data buffer: In 8i contains Db_block_buffer*db_block_size, Buffer_pool_keep, buffer_pool_recycle three parts of memory. Include Db_cache_size, Db_keep_cache_size, Db_recycle_cache_size, db_nk_cache_size in 9i.Redo buffers:Refers to the log buffer, Log_buffer. An additional point to note here is that the query values for V$parameter, V$sgastat, and V$SGA may not be the same. The value inside the V$parameter is the value set by the user in the initialization parameter file, V$sgastat is the actual log buffer size assigned by Oracle (because the allocation value of the buffer is actually discrete and not allocated in the smallest unit of the block), V$SGA The value inside the query is that after Oracle allocates the log buffers, in order to protect the log buffer, set up some protection pages, usually we will find that the protection page size is about 11k (different environments may not be the same). ii. parameters and settings in the SGA:2.1 Log_buffer for the size of the log buffer, usually I don't think too much advice, because the reference LGWR write the trigger conditions, we will find that usually more than 3M meaning is not very large. As a formal system, you may consider setting this section to log_buffer=3-5m size first and then adjusting it for specific situations. Log_buffer is the buffer of redo log.

So here it is necessary to understand the triggering event of redo log (LGWR) 1, when the redo log buffer capacity reaches 1/32, the set write redo Log time interval is reached, typically 3 seconds. 3, redo log buffer in the redo logging capacity to reach 1M4, dbwn the buffer in the data before writing to the data file 5, every time commit--commit the transaction. The above conclusion can be in other words 1, the content in Log_buffer is 1/3, the cache refreshes once. 2, the longest interval 3 seconds, the cache refreshes once 3, the data in the Log_buffer reaches 1M, the cache refreshes once. 4. Each time a "transaction" is committed, the cache refreshes once

 2.2 large_pool_size   for large buffer pool settings, if you do not use MTS, recommended in 20-30m   enough. This section is primarily used to preserve some of the information in parallel queries, and it is possible for RMAN to use it when backing up. If you set up MTS, because the UGA part is moved in here, you need to consider this part of the size of the server process number and the settings of the relevant session memory parameters.  2.3  Java_pool_size   If the database does not use Java, we generally think that preserving the 10-20m size is sufficient. In fact, it can be less, or at least 32k, but specifically related to the components of the database installation (such as HTTP server). The overhead of  2.4  shared_pool_size shared_pool_size should normally be within 300M. Unless the system uses a large number of stored procedures, functions, packages, such as Oracle ERP applications, may be up to 500M or higher. So we assume a system of 1G memory, may consider setting this parameter to 100M,2G system consider set to 150m,8g system can consider setting to 200-300M 2.5SGA_MAX_SIZE SGA area includes various buffers and memory pools, Most can specify their size by specific parameters. However, as an expensive resource, the physical memory size of a system is limited. Although the actual physical memory size is not required for CPU memory addressing (this is explained in detail later), excessive use of virtual memory results in page in/out, which can greatly affect the performance of the system and may even cause the system to crash. So a parameter is needed to control the maximum size of the SGA using virtual memory, which is sga_max_size. When an instance is started, each memory area allocates only the minimum size required by the instance, and then expands their size as needed during subsequent runs, and their sum size is sga_max_size.   For OLTP systems, refer to: 

system memory	sga_max_size value
1G	400-500m
2G	1G
4G	2500M
8G	5G

2.6 PRE_PAGE_SGA When the Oracle instance starts, it loads only the smallest size of each memory area. Other SGA memory is allocated only as virtual memory and is displaced into physical memory only when the process touches the corresponding page. But we might want all the SGA to be allocated to physical memory once the instance is started. You can then set the PRE_PAGE_SGA parameter to achieve the goal. The default value for this parameter is false, which means that the entire SGA is not placed into physical memory. When set to True, instance initiation will place all of the SGA into physical memory. It enables the instance to start up to its maximum performance state, but the boot time is longer (because the Oracle process needs to touch all of the SGA pages in order to put all the SGA into physical memory). 2.7 Lock_sga to ensure that the SGA is locked in physical memory without page-in/page-out, which can be controlled by parameter LOCK_SGA. The default value of this parameter is false, and when specified as true, all SGA can be locked in physical memory. Of course, some systems do not support memory locking, and this parameter is not valid.2.8 Sga_target Here is a very important parameter introduced in the time oracle10g. Before 10g, the size of each memory area of the SGA needs to be specified by its respective parameters and cannot exceed the value of the parameter's specified size, although they may not have reached the maximum limit of the SGA. In addition, once allocated, the memory of each area can only be used in this area and cannot be shared with each other. Take the two most important memory areas of the SGA cache and the shared pool, two of them have the greatest impact on the performance of the instance, but there is a contradiction: in the case of limited memory resources, sometimes the data is very much required by the cache, in order to improve the buffer hit , you need to add buffer cache, but because the SGA is limited, it can only be "snatched" from other areas-such as shrinking the shared pool, adding buffer cache, and sometimes a chunk of plsql code is parsed into memory, causing the shared pool to be insufficient.         Even with a 4031 error and the need to expand the shared Pool, it may be necessary to have human intervention to recapture the memory from buffer cache. With this new feature, this memory contradiction in the SGA is solved. This feature is known as Automatic shared memory management (Automatic shared Memories Management ASMM). And the control of this characteristic is just this one parameter sga_targe. After setting this parameter, you do not need to specify the size for each memory area. SGA_TARGET Specifies the maximum amount of memory that the SGA can use, and the size of each memory in the SGA is controlled by Oracle and does not require human designation. Oracle can adjust the size of each area at any time to achieve the most reasonable size of the system's optimal performance, and to control their sum within the value specified by the Sga_target. Once you assign a value to Sga_target (the default is 0, that is, ASMM is not started), the ASMM feature is started automatically. iii. Oracle Memory tuning approachWhen a performance problem arises in the production environment of a project, how do we determine which parameters need to be adjusted? 3.1 Check the library cache hit rate of the Oracle instance:

Standard: General is greater than 99%

check mode: Select (SUM (reloads)/sum (Pins)) "Library Cache Hit Ratio" from V$librarycache;

Handling measures:

If the value of the library cache hit ratio is less than 99%, the size of the shared_pool_size should be increased. Adjust the size of the shared_pool_size by sqlplus the connection database by executing the following command:

sql>alter system flush Shared_pool;

sql>alter System set shared_pool_size= set value Scope=spfile;

 

3.2 Check the data buffer hit ratio of the Oracle instance:

Standard: General is greater than 90%

Check Method:

Select 1-(Phy.value/(Cur.value + con.value)) "Hit RATIO"

From

v$sysstat cur, v$sysstat con, v$sysstat PHY

where Cur.name = ' db block gets '

and

con.name = ' consistent gets '

and

phy.name = ' physical reads ';

Treatment measures:

if the hit ratio value is less than 90%, the size of the db_cache_size should be increased. Adjust the size of the db_cache_size by executing the following command from the Sqlplus connection database

sql>alter System Set db_cache_size= set value Scope=spfile

3.3  Check the dictionary cache hit rate of the Oracle instance: 

standard: generally greater than 95%

 

Check method:

Select 1-(SUM (Getmiss ES)/sum (gets)) "Data Dictionary hit Ratio"

  from V $rowcache; 

handling measures:

sql> alter system flush Shared_pool;

Sql>alter system set shared_pool_size= setpoint scope= SPFile

3.4 Check the log buffer hit ratio of the Oracle instance:

standard: Generally less than 1%

 

Check Method:

Select (Req.value *)/Entries.value "Ratio"

From

v$sysstat req, V$sysstat entries

where req.name = ' Redo log space requests '

and

entries.name = ' redo entries ';

 

Treatment measures:

If the ratio is above 1%, the size of the log_buffer should be increased. Adjust the size of the Log_buffer by executing the following command from the Sqlplus connection database:

sql>alter System Set log_buffer= set value Scope=spfile;

3.5 Check Undo_retention:

Standard: The value of undo_retention must be greater than the value of Max (Maxquerylen)

 

Check Method:

Col undo_retention format A30

Select Value "Undo_retention" from V$parameter where name= ' undo_retention ';

select Max (Maxquerylen) from V$undostat Where begin_time>sysdate-(quarter);

 

Treatment measures:

If you do not meet the requirements, you need to increase the value of undo_retention. Connect to the database by Sqlplus to perform the following life

to adjust the size of the undo_retention:

sql>alter System Set undo_retention= set value Scope=spfile;

Note:32bit and 64bit problems for Oracle, there are 32bit and 64bit issues. This problem affects mainly the size of the SGA. In 32bit database, Oracle can only use less than 1.7G of memory, even if we have 12G of memory, but we can only use 1.7G, this is a great pity. If we install a 64bit database, we can use a lot of memory, we almost can't reach the limit. But the 64bit database must be installed on the 64bit operating system, unfortunately, the current Windows can only install 32bit database, we can see in the following way the database is 32bit or 64bit but under the specific operating system, may provide some means, Allows us to use more than 1.7G of memory, up to 2G or more.

"Go" Oracle Memory allocation and tuning summary