Is it difficult to accurately assign the number of memory required for different pools? Automatic shared memory management features make it possible to automatically assign memory to the most needed.
Whether you are a Junited DBA or an experienced DBA, you must have seen at least a mistake similar to the following:
ORA-04031: Unable to allocate 2216 bytes of shared memory ("shared pool" ...
Or this error:
ORA-04031: Unable to allocate xxxx bytes of shared memory
("Large Pool", "Unknown Object", "Session HEAP", "Frame")
Or maybe this error:
ORA-04031: Unable to allocate bytes of shared memory ("shared pool",
"Unknown Object", "JoxLod: Init H", "JOX: IOC_Allocate_pal")
The first error is obvious: the memory assigned to the shared pool is not sufficient to meet the user request. (In some cases, the reason may not be the size of the pool itself, but the excessive analysis caused by the binding variable, this is a topic I like very much; but now let us focus on the hand On the issue.) Other errors are insufficient from large pools and Java pools, respectively. You need to solve these error conditions without any modifications related to the application. So what are the programs optional? The problem is how to divide the available memory between all the pools required by the Oracle routine. How is the pie? As you can know, a system global zone (SGA) of an Oracle routine (SGA) includes several memory areas (including buffer caches, shared pools, Java pools, large pools, and redo log buffers). These pools occupy a fixed amount of memory in the memory space of the operating system; their size is specified by DBA in the initialization parameter file. These four pools (database block buffer cache, sharing pool, Java pool, and large pool) have almost occupy all spaces in the SGA. (Compared with other regions, the redo log buffer does not occupy how much space, which is not critical to the discussion here.) As the DBA, you must ensure that their respective memory allocation is sufficient. Assume that you determine the values of these pools are 2GB, 1GB, 1GB, and 1GB, respectively. You will set the following initialization parameters to specify the size of the pool for the database routine. DB_CACHE_SIZE = 2G
Shared_pool_size = 1g
Large_pool_size = 1g
Java_pool_size = 1g
Take a closer look at these parameters. Frank, is these values accurate? I believe you will have doubts. In practice, no one can specify the exact amount of memory for these pools - they depend too dependent on the processing inside the database, and the processing is changed at any time. Here is an example scenario. Assume that you have a typical, most of which belong to OLTP, and a dedicated memory allocation to buffer cache is a pure OLTP database (now rarely). One day, your users have released some very large full-table scans to create the end of the day. Oracle9i database provides you with online modifying memory allocation, but you decide to remove some memory from large pools and Java pools because of the limited physical memory provided: ALTER SESTEM SET DB_CACHE_SIZE = 3G Scope = Memory;
ALTER system set large_pool_size = 512m scope = memory; alter system set java_pool_size = 512m scope = memory;
This solution works well for a period of time, but the RMAN job (they use large pool) at night, and the large pool will immediately have insufficient memory. Similarly, you remove some memory from the database cache to supplement the large pool to save this situation. The RMAN job is completed, then start a widely used Java batch program, then you start to see the error related to the Java pool. Therefore, you (again) reassign the pool to meet the memory requirements on the Java pool and database cache: ALTER system set db_cache_size = 2g scope = memory;
ALTER system set large_pool_size = 512m scope = memory;
ALTER system set java_pool_size = 1.5g scope = memory;
The next morning, OLTP job recovers online, this cycle is completely repeated! An alternative way to solve this malignant cycle is to permanently set the biggest demand for each pool. However, if you do this, the total SGA you assigned may exceed the available memory - thus increasing the risk of exchange and paging when the number of memory allocated for each pool is insufficient. Artificial redistribution method (although not actually) It seems very good. Another alternative is to set the value to an acceptable minimum value. However, performance will be affected when the demand grows and the memory cannot be fully met. Note In all of these examples, the total memory assigned to the SGA remains unchanged, and the memory allocation between the pool is modified according to the instant demand. If RDBMS will automatically detect the needs of users and re-distribute memory allocation accordingly, isn't that good? The automatic shared memory management feature in the Oracle Database 10G is enabled. You can decide the total size of the SGA, then set a parameter named SGA_TARGET, which determines the total size of the SGA. Each pool inside the SGA will be dynamically configured according to the workload. Implementing automatic memory allocation only requires a non-zero value of the SGA_TARGET parameter. Settings Auto Shared Memory Management Let us see how this feature works. First, determine the total size of the SGA. You can estimate this value by determining how much memory is now allocated. SQL> SELECT SUM (Value) / 1024/1024 from V $ SGA;
Sum (Value) / 1024/1024
--------------------
500 At this time, the current total size of the SGA is approximately 500MB, and this value will become the value of the SGA_TARGET. Next, executing statement: alter system set sga_target = 500m scope = Both;
This method does not need to set different values for each pool; thus, you will need to make them zero or all in the parameter file. Shared_pool_size = 0
Large_pool_size = 0
Java_pool_size = 0
DB_CACHE_SIZE = 0
Recycling the database so that these values take effect. This artificial process can also be implemented via Enterprise Manager 10g. From the database main page, select the "Administration" tab and select "Memory Parameters". For manually configured memory parameters, the buttons marked as "enable" will be displayed, and the value of all artificially configured pools. Click the "Enable" button to enable the automatic shared memory management feature. Enterprise Manager will complete the rest of the work. After configuring automatic memory allocation, you can use the following command to check their size: SQL> SELECT CURRENT_SIZE FROM V $ BUFFER_POOL; CURRENT_SIZE
----------------
340
SQL> Select Pool, SUM (Bytes) / 1024/1024 MBYTES from V $ SGASTAT GROUP BY POOL
Pool mbytes
------------ ----------
Java pool 4
Large pool 4
Shared Pool 148 As you can see, all pools are automatically assigned from the 500MB total target size. (See Figure 1.) The buffer cache size is 340MB, the Java pool is 4MB, the large pool is 4MB, and the shared pool is 148MB. They set a total size (340 4 4 148 =) 496MB, approximately the same size as 500MB of target SGA.
Figure 1: Initial allocation of the pool
It is now assumed that the host memory provided to Oracle is reduced from 500MB to 300MB, which means that we must reduce the size of the total SGA. We can reflect this change by reducing the target SGA size. Alter System Set SGA_TARGET = 300M Scope = Both;
Now look at each pool, we can see: SQL> SELECT CURRENT_SIZE FROM V $ BUFFER_POOL;
Current_size
----------------
244
SQL> Select Pool, SUM (Bytes) / 1024/1024 MBYTES from V $ SGASTAT GROUP BY POOL
Pool mbytes
------------ ----------
Java pool 4
Large pool 4
Shared pool 44
The total size of the occupied is 240 4 4 44 = 296MB, close to 300MB of the target. Note How to automatically reassign the pool when the SGA_TARGET is changed.
Figure 2: Reassign the pool after reducing the SGA size to 300MB
The size of these pools is dynamic. The pool will extend according to the workload to accommodate the demand growth, or shrink to accommodate the extension of another pool. This expansion or reduction is automatically occurring without the need for DBA intervention, which is different from the examples of this article. Let us temporarily return to that scene, assume that the RMAN job starts, indicating a larger pool, and the large pool will extend from 4MB to 40MB to accommodate demand. This additional 36MB will be drawn from the database buffer, and the database block buffer will shrink, as shown in Figure 3.
Figure 3: Changes of reassigned pools after the demand growth of large pools is based on the working load on the system, so there is no need to adjust the size of the pool for the worst case - they will be automatically adjusted according to the growth of the demand. In addition, the total size of the SGA is always within the maximum value specified by the SGA_TARGET, so there is no risk of increasing the growth ratio of memory requirements (this will result in paging and exchange). You can dynamically increase the SGA_TARGET to the absolute max, this absolute maximum is specified by adjusting the parameter SGA_MAX_SIZE. Which pools are not affected? Some pools in SGA are not affected by dynamic size, but these pools must be explicitly specified. Among them, it is worth noting that the non-standard block size buffer pool, and the KEEP pool or Recycle pool is not a size. If your database has a block size of 8K, you want to configure 2K, 4K, 16K, and 32K-size pools, then you must manually set them. Their size will remain unchanged; they will not be reduced or expanded according to load. This factor should be considered when using multiple sizes of buffer pools, KEEP pools, and Recycle pools. In addition, log buffer is not affected by memory adjustment - regardless of workload, the value set in parameter log_buffer is constant. (In 10G, a new pool can also be defined in SGA: Stream Pool, which is set with parameter streams_pool_size. The pool is not affected by automatic memory adjustment.) This has created an interesting problem. If you need a non-default block size, and want to automatically manage other pools, what should I do? If you specify any of these non-automatic adjustments (such as db_2k_cache_size), their total size will subtract from the SGA_TARGET value to calculate the automatic adjustment parameter value so that the total size of the SGA remains unchanged. For example, assume that the value looks like this: SGA_TARGET = 500M
DB_2K_CACHE_SIZE = 50M
The remaining pool parameters are not set. 50MB 2KB buffer pool is automatically adjusted pool (such as the default block size buffer pool (db_cache_size), shared pool, Java pool, and large pools) have retained 450MB. Dynamically adjust the non-automatic adjustment parameters (such as 2KB block size pool) - this method will affect the size of the automatically adjustable portion, and the portion that can be automatically adjusted will be re-adjusted. For example, the value of DB_2K_CACHE_SIZE is increased from 50MB to 100MB to only 400MB of the parameter that automatically adjusts. Thus, as shown in Figure 4, adjustable pools (such as shared pools, large pools, Java pools, and default buffer pools) are automatically reduced to reduce their total size from 450MB to 400MB.
Figure 4: Effect of configuring non-automatic buffer parameters
But if you have enough available memory, or the above risk may not be so obvious, what should I do? If this is, you can turn off the automatic size adjustment by setting it to 0 in the file without specifying the parameter SGA_TARGET in the file, or dynamically modify it into 0 using the ALTER SYSTEM. When the SGA_TARGET is set to 0, the current value of the pool is automatically set to their parameters. Use Enterprise Manager you can also use Enterprise Manager 10g to process these parameters. Click Hyperlink "Memory Parameters" from the database home page, which will display a screen similar to Figure 5.
Figure 5: Adjusting automatic shared memory management in Enterprise Manager
Note that the project in the red circle: The database is run in Automatic Shared Memory Management mode, with a total size of 564MB - the same value as the value specified in the parameter SGA_TARGET. You can modify it here, then click the Apply button to accept these values; the adjustable parameters will be adjusted automatically. Specify a minimum value for each pool, set the SGA_TARGET to 600MB, and each pool has automatically assigned: Pool Size (MB) Buffer Pool 404JAVA Pool 4 Large Pool 4 Shared Pool 148 Take a look at the above value, you may infer 4MB Java Pools and large pools may be a bit deficieted; this value is undoubtedly needed at runtime. Therefore, you may want to ensure that these pools have a higher value at the initial time, such as 8MB and 16MB, respectively. You can implement this (as shown below) by explicitly specifying the value or dynamically using the ALTER System (as shown) in the parameter file. ALTER system set large_pool_size = 16m;
Alter System Set Java_pool_size = 8m;
Now view these pools, you can see: SQL> Select Pool, SUM (Bytes) / 1024/1024 MBYTES from V $ SGASTAT GROUP BY POOL
Pool mbytes
------------ ----------
Java pool 8
Large pool 16
Shared pool 148
SQL> SELECT CURRENT_SIZE FROM V $ BUFFER_POOL;
Current_size
----------------
388
The redistribution of the pool is shown below:
Pool Size (MB) Buffer Pool 388JAVA Pool 8 Large Pool 16 Shared Pool 148 Note How to be reconfigured to 8MB and 16MB, respectively, and note how the total SGA is kept below 600MB, the buffer pool has been from 404MB Reduce 388MB. Of course, these pools are still controlled by automatic shared memory management - their size will shrink or expand according to demand. Your explicit specified value is set to a lower limit for the size of the pool; they will never be reduced to this limit. Conclusion The memory demand of various pools in Oracle SGA is not static-reverse, which varies depending on the needs of the system. The automatic shared memory management feature in Oracle Database 10g applies a specified maximum to the most needed to prevent paging and exchange by dynamically of reassigning resources to the most needed to prevent paging and exchange, so that DBA can manage system memory more efficiently. More efficient memory management also has fewer memory requirements, which makes more streamlined hardware more feasible. For more information on automatic shared memory management features, see Chapter 7 of the Oracle Database Performance Adjustment Guide.
