l
For the management of Oracle's memory, as of 9IR2, it is quite important as an important link, and management is not good, it will give a serious performance issue to the database. Below we will discuss one step by step on the various aspects of memory management.
l overview
Oracle's memory can be divided into system global zones and processes in the shared and private perspective, which is SGA and PGA (Process Global Area or Private Global Area). For memory in the SGA area, shared globally, on UNIX, you must set the shared memory segment (one or more) to Oracle, because Oracle is multi-process on UNIX; Oracle on Windows is Single process (multiple threads), so you don't have to set the shared memory segment. PGA is a region belonging to a process (thread). In Oracle Use Sharing Server Mode (MTS), part of the PGA, that is, the UGA will be placed in shared memory large_pool_size.
For the SGA section, we can see in SQLPLUS:
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 environment is a fixed value, the information of the SGA partial components can be used as a region that boots the establishment of SGA.
Variable size
Contains SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE and other memory settings and Hash Table, block information (such as X $ BH consumption memory) such as memory structure, etc.
Database buffers
Refers to the data buffer, including the three partial memory, buffer_pool_pool_keep, buffer_pool_recycle in 8i. In 9i, DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECY_CACHE_SIZE, DB_NK_CACHE_SIZE are included in 9i. It is important to pay attention to the sum of the three parts in 8i in the sum of DB_BLOCK_BUFFERS * DB_BLOCK_BUFFERS * DB_BLOCK_SIZE.
Redo buffers
Refers to the log buffer, LOG_BUFFER. At this point here, you may not be the same for V $ Parameter, V $ SGASTAT, V $ SGA query value may be different. V $ Parameter's value refers to the value set in the initialization parameter file. V $ SGASTAT is the log buffer size actually allocated by Oracle (because the allocation value of the buffer is actually discrete, nor is the smallest in Block The unit is allocated), and the value of the query in V $ SGA is to protect the log buffer in Oracle. In order to protect the log buffer, some protection pages are set, usually we will find that the protection page size is about 11K (different environments may Different). Refer to the following
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
With regard to the role of each part, refer to the Oracle architecture, which is not described here.
l SGA size
Then we now come to examine the settings of the memory parameters. In fact, for a particular environment, there is always different optimal settings, and there is no universal applicable optimal solution. But why do we have to talk about this topic here, that is just a purpose, avoiding excessive mistakes. In fact, before any production system is officially put into use, we don't have any system running information, let us adjust, so there are only two possibilities, one is based on the document recommendation, and the other is based on experience. Relatively, based on the experience of the experience is reliable than the setting according to the document. Especially those systems of 24 * 7, we have to reduce the incorrect occurrence. Then we try to understand the specific settings of different applications of different systems, thereby providing a reference information to everyone.
In order to get a reference setting, we must assume a reference environment. All of the following we have assumed this, that is, the hardware server only considers the existence of operating systems and databases, in this single environment, let's consider the memory settings.
Before setting the parameters, we must first ask yourself a few questions.
First: Multi-physical memory
Second: How much memory needs to be used in operating system estimates
Third: The database is a file system or a naked device
Four: How many concurrent connections
5: Application is OLTP type or OLAP type
According to the answers to these questions, we can roughly estimate memory settings for the system. Then we will now discuss one by one. First, how much physical memory is the easiest answer, and then how much memory is used by operating system estimates? From experience, it will not be too much, usually within 200m (not containing a large number of process PCB).
Next we have to explore an important issue, that is, there is a problem with file systems and bare devices, which is often easily ignored by us. The operating system uses a large amount of buffer to cache the operating system block. This is when the database gets the data block, although there is no hit in the SGA, but actually may be obtained from the file cache of the operating system. If the database and operating system supports asynchronous IO, it is actually when the database write the process DBWR write disk, the operating system tagged the block in the file cache to delay write, wait until it is really written to disk, the operating system notifies the DBWR write disk completion . For this part of the file cache, the required memory may be relatively large, as a conservative estimate, we should consider at 0.2--0.3 times memory size. But if we use bare devices, this partial cache is not considered. In this case, the SGA has a chance to be large. About the database how many concurrent connections, this is actually related to the size of the PGA (Large_Pool_SIZE under MTS). In fact, this issue should be said to be related to the OLTP type or OLAP type. For OLTP Type Oracle tends to use MTS, use independent mode for OLAP types, and OLAP may also involve queries of a large number of sorting operations, which affect us of our memory. Then all questions are integrated, which is actually mainly reflected in the size of the UGA. UGA mainly includes the following memory settings
SQL> Show parameters area_size
Name Type Value
------------------------------------- --------
Bitmap_mege_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 our most attention, we are usually sort_AREA_SIZE, which is when the query needs to be sorted, the database session will use this part of the memory to sort using the temporary table space when the memory size is insufficient. This parameter setting is important because the disk sort efficiency and the memory sort efficiency are different. These four parameters are set for sessions, which is the size of the memory used by a single session, not the entire database. Occasionally, some people mistakenly misunderstood this parameter to be the size of the entire database, which is extremely serious. If MTS is set, the UGA is assigned in large_pool_size, which means that this part of the memory can be shared between the shared memory, and the different processes (threads) can be shared. On this basis, we assume that the data inventory is in concurrent execution of Server Process, according to our 4 parameters in Oracle8.1.7, we calculate the approximate size of the PGA in standalone mode. Since the session does not often use create_bitmap_area_size, bitmap_merge_area_size, so we usually do not ask four parameters. In consideration of variables saved by the four parameters, the stack and other information, we estimate to 2m, then 100 processes may use 200M PGA.
Now, according to these assumptions, let's see how much memory can actually reach. On 1G memory server, we can assign to SGAs from about 400-500m. If it is 2G memory, approximately 1 g of memory to SGA, 8 g of memory can be separated to 5 g of memory to SGA. Of course, we are measured by the default sorting part memory sort_area_size = 64k, if we need to zoom in to the parameters such as the parameter and haveh_area_size, then we should measure this problem based on the number of concurrent processes. In fact, usually we are more accustomed to expressing such problems through intuitive formulas:
OS uses memory SGA concurrent execution process number * (sort_AREA_SIZE HASH_ARA_SIZE 2M) <0.7 * total memory
(The formula is dead, the system is alive, the adjustment of the actual application is not necessarily the formula, this is not a reference suggestion)
In our practical application, if it is a naked device, we can increase SGA (if needed). Since the currently almost all operating systems use virtual cache, it is actually if it does not cause errors even if the SGA settings are large, but there may be frequent memory pages exchange and replacement (Page IN / OUT). If this phenomenon is observed at the operating system level, then we need to adjust the memory settings.
l SGA parameter settings
Log_buffer
For the size settings of the log buffer, I usually I don't think there are too many suggestions, because after referring to the trigger condition written by LGWR, we will find that it usually exceeds 3m significance is not very large. As a formal system, it is possible to consider setting this part to log_buffer = 1-3m size, and then adjust it for specific situations.
Large_pool_size
For the setting of the large buffer pool, if the MTS is not used, it is recommended to be enough for 20-30M. This part is mainly used to save some information at parallel inquiry, as if RMAN may be used when backed up. If MTS is set, since the UGA section is moved here, you need to specifically consider this part of the size according to the setting of the Server Process quantity and the relevant session memory parameters.
Java_pool_size
If the database does not use Java, we usually think that the size of 10-20m is sufficient. In fact, you can only need 32K, even at least 32K, but the components in the installation database (such as HTTP Server).
Shared_pool_size
This is the most controversial memory setting so far. According to the description of many documents, this part should be almost almost in size with the data buffer. But actually, is not the case. First of all, we have to be found, that is, this part of the memory role, it is to cache SQL that has been parsed, but it can be reused, no longer parsing. The reason for this is because, for a new SQL (there is no existence of the same SQL that has been parsed in Shared_Pool), the database will perform hard narratism, which is a very resource process. And if you already exist, it is only soft analysis (finding the same SQL in the shared pool), which consumes greatly reduced resources. So we expect to share some SQL more, and if the parameter setting is not large enough, ORA-04031 errors often appear, indicating that in order to analyze new SQL, there is no sufficient continuous free space available, so naturally we expect this parameter to be large some. However, the increase in this parameter also has a negative impact, because it is necessary to maintain a shared structure, the increase in memory will make the aging of SQL higher, bring a lot of management overhead, all of which may cause CPUs. Serious Problem.
In a relatively large system that fully uses the binding variable, the overhead of Shared_Pool_Size should usually remain within 300m. Unless the system uses a large number of stored procedures, functions, packages, such as Oracle ERP, may reach 500m or even higher. So we assume a 1G memory system, which may consider setting the parameter of 100m, 2G system considers to be set to 150m, 8G, can consider setting to 200-300m. This may give us a serious problem for a binding variable system without sufficient use or without using a binding variable. The so-called SQL did not use Bind Var, we called Literal SQL. That is, if such two SQL we think is different SQL, you need to perform 2 hard parsing:
Select * from Emp where name = 'Tom';
Select * from Emp where name = 'jerry';
If the 'Tom' and 'Jerry' are changed to variables V, that is, use Bind Var, we can think that the same SQL is used to share well. Sharing SQL is originally shared_pool_size This part of the memory existence, Oracle's purpose is there, and we don't use bind var to violate the original intention of Oracle, which will give our system a serious problem. Of course, if you monitor the operating system, there is no severe CPU problem, we find that the shared pool hits can increase SHRED_POOL_SIZE. But usually we don't advocate this part of the memory exceeds 800M (larger can be larger).
In fact, we may even want to avoid soft analysis, this is different in different program languages. We may also get help by setting the session_cached_cursors parameter (this will increase the PGA).
Data Buffer
Now let's talk about the data buffer, after the size of the SGA is determined and allocated, the rest is assigned to this part of the memory. Usually, in the case of allowing, we have tried to make this part of the memory larger. This part of the memory is mainly cache DB Block, which reduces even avoids data from the disk, which is determined by DB_BLOCK_BUFFERS * DB_BLOCK_SIZE in 8i (including default, keep, recycle). If we set buffer_pool_keep and buffer_pool_recycle, the size of these memory is included in the front setting (DB_BLOCK_BUFFERS * DB_BLOCK_SIZE).
Buffer_pool_keep is a MOST Used end of the previous cache that is used to replace the 8i version. By opening up a separate memory for a frequent small table, you can specify the storage parameters when you create a table, or you can dynamically modify the storage parameters of the table (ALTER TABLE TSOOL (BUFFER_POOL Keep);).
Buffer_pool_recycle as a separately opened memory, mainly for querying queries that are rarely executed, so that these large table scans do not affect the performance of the entire database buffer without affecting the LRU of Default. Although this is likely to reduce the performance of the full mete scan of a large table, the overall impact is protected from uninterrupted overall performance. Similarly, in addition to setting parameters, it is necessary to use storage parameters or dynamically modified storage parameters (ALTER TABLE TABLECLE) in the process of creation tables.
L 9i changes under the parameters
Oracle's version of the update is always accompanied by changes in parameters and increasingly tends to make the settings of the parameters easier, because complex parameter settings make DBA often focused. With regard to changes in memory, we can examine the following parameters. In fact, the database itself can give a parameter adjustment value for the SGA-related part of the current running system (refer to V $ db_cache_advice, v $ shared_pool_advice), regarding the PGA also related view V $ PGA_TARGET_ADVICE, etc. Data Buffer
The parameters in 8i are retained in 9i, such as setting new parameters, ignore the old parameters. 9i uses db_cache_size to replace db_block_buffers, replace buffer_pool_keep with db_keep_cache_size, replace buffer_pool_receth with db_recycle_cache_size; hereby pay attention to setting the actual cache size in 9i is no longer the number of blocks. The additional 9i adds DB_NK_CACHE_SIZE, which is set to support the use of different blocks in the same database. For different tablespaces, different data blocks can be defined, while the definition of the buffer relies on this parameter. Where N can be different values such as 2, 4, 6, 8, 16. One parameter referred to herein is DB_BLOCK_LRU_LATCHES, which has become a reserved parameters in 9i, and does not recommend manual settings.
PGA
There is also a big change in this section in 9i. In independent mode, 9i no longer advocates the use of the original UGA-related parameter settings, and in order to replace new parameters. If workera_size_policy = auto, all sessions' UGAs share a large number of memory, which allocates within the PGA_AGGREGATE_TARGET settings. After we evaluate the maximum PGA memory that all processes, we can set this parameters in the initialization parameters, so we can no longer care about other "* _Area_size" parameters.
SGA_MAX_SIZE
If SGA_MAX_SIZE is set in 9i, the size of the data buffer and the shared pool can be dynamically adjusted within the sum of the summates.
SQL> Show Parameters SGA_MAX_SIZE
Name Type Value
------------------------------------- --------
SGA_MAX_SIZE UNKNOWN 193752940
SQL>
SQL> ALTER SESTEM SET DB_CACHE_SIZE = 30000000;
SYSTEM altered.
SQL> ALTER System SET Shared_Pool_Size = 20480000;
SYSTEM altered.
l lock_sga = True problem
Since almost all operating systems support virtual memory, even if the memory we use is smaller than physical memory, it is not possible to avoid switching SGA to virtual memory (SWAP). So we can try to make SGA locks are not changed in physical memory to virtual memory, so that the page exchanges and exchanges, thereby improving performance. But here is unfortunate here, Windows cannot avoid this situation. Let's refer to how to implement Lock_SGA under different systems
AIX 5L (AIX 4.3.3 or more)
Logon Aix as root
CD / USR / Samples / Kernel
./VMTUNE (information is as follows) v_Pingshm is already 1./Vmtune -s 1
The Oracle user then modifies the loc_sga = true in INITSID.ORA
Restart the database
HP UNIX
Root login
Create the file "/ etc / privower": vi / etc / privget
Add Line "DBA Mlock" to file
As root, run the command "/ etc / setprivgrp -f / etc / privroup":
$ / ETC / SETPRIVGRP -F / ETC / PrivGroup
Oracle user modified loc_sga = true in INITSID.ORA
Restart the database
Solaris (Solaris 2.6 or more)
8i version The above database defaults use hidden parameters for use_ism = true, automatically lock SGA in memory, no set Lock_SGA, if set Lock_SGA = TRUE uses non-root users to start the database will return errors.
Windows
You can't set Lock_SGA = True, you can load all memory pages when the database starts, which can play a certain role.
l Adjustment of memory parameters
Regarding parameter adjustment, it is a concrete manifestation of the complexity of Oracle. In general, we prefer to let customers do the StatsPack report, then tell us the situation of OS monitoring, on the basis of these information, and then request specific details to the customer to diagnose the problem. The system is adjusted, and now we usually use methods from waiting for events. Because a system feels slow, it is inevitable to wait in a certain aspect, then we will gradually diagnose and solve problems from waiting for the most events.
For memory adjustments, relatively simple, we can first look at the size of the data buffer. First observe the hit rate
Data buffer hits
SQL> SELECT VALUE from V $ sysstat where name = 'Physical Reads'
Value
------------
14764
SQL> SELECT VALUE from V $ sysstat where name = 'physical reads direct';
Value
------------
50
SQL> SELECT VALUE from V $ sysstat where name = 'Physical Reads Direct (LOB)';
Value
------------
0
SQL> SELECT VALUE from V $ sysstat where name = 'consistent gets';
Value
------------
167763
SQL> SELECT VALUE from V $ sysstat where name = 'db block gets';
Value
------------
14305
The calculation of the hit rate here should be
Let X = Physical Reads Direct Physical Reads Direct (LOB)
Hit rate = 100 - (Physical READS - X) / (Consistent Gets DB Block Gets - x) * 100
Usually, if the hit rate is found below 90%, the application should be adjusted to consider whether to increase the data buffer.
The hit rate of shared pool
SQL> SELECT SUM (Pinhits) / SUM (Pins) * 100 "Hit Radio" from v $ librarycache; Hit Radio
------------
99.809291
If the hit rate of shared pool is less than 95%, it is necessary to consider adjustment applications (usually without bind var) or increase memory.
About the sort part
SQL> SELECT NAME, VALUE FROM V $ sysstat where name like '% sort%';
Name Value
-------------------------------------------------- ------------------------
Sorts (Memory) 67935
Sorts (Disk)
Sorts (ROWS) 7070
SQL>
If we find that Sorts (DISK) / (Memory Sorts (Disk)) is too high, the Sort_Area_SIZE part is small, and the corresponding parameters can be adjusted.
About LOG_BUFFER
SQL> SELECT NAME, VALUE FROM V $ sysstat
2 WHERE Name in ('Redo Entries', 'Redo Buffer Allocation Retries');
Name Value
-------------------------------------------------- ------------------------
Redo entries 2325719
Redo Buffer Allocation Retries 10
If the ratio of Redo Buffer Allocation Retries / Redo Entries exceeds 1% We can consider increasing log_buffer
In general, the focus of memory adjustment is concentrated in these aspects, more more detailed content, suggesting from Statspack to step by step. Finally, the adjustment of the memory, emphasizing this, must be measured in combination with the operating system, any theory must practice. Observe the status of the Page In / OUT in the operating system, and the problem is serious, and you should consider tune the SGA.
L 32bit and 64bit
For Oracle, there is a problem with 32bit and 64bit. This problem is affected by the SGA size. Under the 32bit database, usually Oracle can only use memory memory, even if we have 12G memory, but we can only use 1.7g, this is a great regret. If we install 64bit database, we can use a lot of memory, almost impossible to reach the upper limit. But the 64bit database must be installed on the 64bit operating system, but unfortunately, only 32bit databases can be installed on Windows. We can view the database through the following manner or 64bit: SQL> SELECT * FROM V $ VERSION;
Banner
-------------------------------------------------- ----------------
Oracle8i
Enterprise
Edition Release 8.1.7.0.0 - Production
PL / SQL Release 8.1.7.0.0 - Production
Core 8.1.7.0.0 Production
TNS for 32-Bit Windows: Version 8.1.7.0.0 - Production
NLSRTL VERSION 3.4.1.0.0 - Production
But under a specific operating system, it may provide a certain means so that we can use more than
1.7G
Memory, reaching
2G
The above or even more.
