Have always wanted to summarize the knowledge of Oracle memory tuning, recently optimized a database memory parameters, look for some information and Google a lot. Now write it down and do the backup.
First, 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, the Shared memory segment (which can be one or more) must be set for Oracle, because Oracle is a multiple process on UNIX, while on WINDOWS Oracle is a single process (multiple threads), so there is no need to set 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.
Send diagram Oracle Memory architecture, as shown above can be clear at a glance key parameters and parameter names:
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:or Acle 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: Includes memory settings such as Shared_pool_size, Java_pool_size, large_pool_size, etc.
Database buffers: Index buffer: Contains Db_block_buffer*db_block_size, Buffer_pool_keep, buffer_pool_recycle in 8i Three-part memory. Include Db_cache_size, Db_keep_cache_size, Db_recycle_cache_size, db_nk_cache_size in the 9i.
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$SGA The value of the query 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).
Two, the SGA internal parameters and settings:
2.1 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=3-5m size, and then adjusting it for specific situations.
Log_buffer is the buffer of the redo log. Therefore, it is necessary to understand the Redo Log trigger event (LGWR) 1, when the redo log buffer capacity reached 1/3 2, set the write redo log time interval to arrive, generally 3 seconds. 3, redo log buffer in the redo logs capacity to reach 1M 4, in the DBWN buffer to write data to the data file before 5, each commit--commit the transaction. The above conclusion can be in other words 1, log_buffer content is full 1/3, the cache refreshes once. 2, the longest interval of 3 seconds, cache refresh once 3, Log_buffer data to reach 1M, cache refresh once. 4, each commit a "transaction", cache refresh Once
2.2 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.
2.3 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.
2.4 shared_pool_size
The cost of shared_pool_size should normally be kept 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
2.5sga_max_size
The SGA area includes a variety of buffers and memory pools, and most can specify their size by specific parameters. However, as an expensive resource, the physical memory size of a system is limited. Although there is no need to relate the actual physical memory size to the CPU's memory addressing (which is described in detail later), excessive use of virtual memory causes page in/out, which can significantly affect system performance and may even lead to system crash. So a parameter is needed to control the maximum size of the SGA using virtual memory, which is sga_max_size. When the instance is started, each memory area allocates only the minimum size required for the instance, and in subsequent runs, expands their size as needed, and their total 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 an Oracle instance starts, it loads only the smallest size of each memory area. Other SGA memory is only allocated as virtual memory and will only be replaced in physical memory when the process is in touch with the appropriate 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 parameters to achieve the goal. The default value for this parameter is false, that is, the entire SGA is not placed in physical memory. When set to True, instance startup will place all the SGA into physical memory. It can cause the instance to start up to its maximum performance state, but the boot time will also be longer (because the Oracle process needs to touch all the SGA pages for all the SGA to be placed in physical memory).
2.7 Lock_sga
To ensure that the SGA is locked in physical memory without having to page/page out, it can be controlled by parameter LOCK_SGA. The default value of this parameter is False, and when True is specified, all of the SGA can be locked in physical memory. Of course, some systems do not support memory locking, and this parameter is invalid. 2.8 Sga_target
Here's 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 own parameters and cannot exceed the value of the parameter's specified size, although their sum may not be the maximum limit for the SGA. In addition, once allocated, the memory of each area can only be used for this area, and can not be shared between each other. Take two of the most important memory areas in the SGA buffer cache and shared pool, they have the most impact on the performance of two instances, but there is such a paradox: in the case of limited memory resources, some times the need for data cache is very large, in order to improve the buffer hit , you need to increase buffer cache, but because the SGA is limited, you can only "Rob" from other areas-such as reducing shared pool, increasing buffer cache, and sometimes large chunks of plsql code are parsed into memory, resulting in insufficient shared pool, Even a 4031 error, and the need to expand shared Pool, may require human intervention to retrieve memory from the buffer cache.
With this new feature, this memory paradox in the SGA is solved. This feature is called Automatic Shared memory management (Automatic shared Memory Management asmm). And the control of this characteristic, is only this one parameter sga_targe. After you set this parameter, you do not need to specify the size for each memory area. SGA_TARGET Specifies the maximum memory size 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 optimal state of system performance, and control their sum within the value specified by Sga_target. Once you assign a value to Sga_target (the default is 0, that is, the ASMM is not started), the ASMM feature is started automatically.
Three, Oracle memory tuning Method
When a performance problem arises in the production environment of the project, how do we determine which parameters need to be adjusted?
3.1 Check the library cache hit ratio for Oracle instances:
Standard: generally greater than 99%
Check mode: Select (SUM (reloads)/sum (Pins)) "Library cache Hit Ratio" from V$librarycache;
Treatment measures: If the library cache Hit ratio value is less than 99%, should be higher shared_pool_size size. Use the Sqlplus connection database to perform the following command to adjust the size of the shared_pool_size: Sql>alter system flush Shared_pool; Sql>alter system set shared_pool_size= set value Scope=spfile;
3.2 Check the data buffer ratio for Oracle instances:
Standard: generally greater than 90%
Check mode: 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 value of the hit ratio is less than 90%, the size of the db_cache_size should be adjusted higher. Use the Sqlplus connection database to perform the following command to resize the db_cache_size sql>alter system set db_cache_size= setting Scope=spfile 3.3 To check the dictionary cache hit ratio for an Oracle instance:
Standard: generally greater than 95%
Check mode: Select 1-(SUM (getmisses)/sum (gets)) "Data Dictionary Hit Ratio" from V$rowcache;
Treatment measures: If the value of the data Dictionary Hit ratio is less than 95%, the shared_pool_size size should be higher. Use the Sqlplus connection database to perform the following command to adjust the size of the shared_pool_size: Sql>alter system flush Shared_pool; Sql>alter system set shared_pool_size= set value Scope=spfile;
3.4 Check the log buffer hit ratio for the Oracle instance:
Standard: generally less than 1%
Check mode: Select (Req.value * 5000)/Entries.value "Ratio" from V$sysstat req, v$sysstat entries where req.name = ' Redo Lo G Space requests ' and entries.name = ' redo entries ';
Treatment measures: If the ratio is higher than 1%, should increase the size of log_buffer. Through the Sqlplus connection database execute the following command, adjust the size of the Log_buffer: Sql>alter system set log_buffer= setting value Scope=spfile;
3.5 Check Undo_retention:
Standard: The value of the undo_retention must be greater than the value of Max (Maxquerylen)
Check mode: 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-(1/4);
Treatment measures: If not meet the requirements, you need to increase the value of undo_retention. Through the Sqlplus connection database execute the following command, adjust the size of the Undo_retention: Sql>alter system set undo_retention= setting value Scope=spfile;
Note: Problems with 32bit and 64bit have 32bit and 64bit problems for Oracle. The main effect of this problem is the size of the SGA. In 32bit database, Oracle can only use no more than 1.7G of memory, even if we have 12G of memory, but we can only use 1.7G, this is a great regret. If we install the 64bit database, we can use a lot of memory, we are almost impossible to reach the upper limit. But 64bit database must be installed on the 64bit operating system, unfortunately, 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, it may provide a certain means, so that we can use more than 1.7G of memory to reach more than 2G or more.
http://blog.itpub.net/12272958/viewspace-696834/
