Whether you're a new or veteran DBA, you've almost certainly seen an error similar to this one at least once:
ORA-04031: unable to allocate 2216 bytes of shared memory ("shared pool"... ...
or this one:
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
or perhaps this one:
ORA-04031: unable to allocate bytes of shared memory ("shared pool",
"unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
The cause of the first error is obvious: the memory allocated to the shared pool is insufficient for answering the user request. (In some cases the cause may not be the size of the pool itself, but rather the fragmentation that results from excessive parsing due to non-usage of bind variables—a favorite topic of mine; but let's stay focused on the issue at hand right now.) The other errors derive from inadequate space in the large pool and Java pool respectively.
You need to resolve these error conditions without any application-related changes. What are your options? The question is how to divide available memory among all the pools required by the Oracle instance.
How Do You Split the Pie?
The System Global Area (SGA) of an Oracle instance, as you know, comprises several memory areas, including the buffer cache, shared pool, Java pool, large pool, and redo log buffers. These pools occupy fixed amounts of memory in the operating system's memory space; their sizes are specified by the DBA in the initialization parameter file.
The four pools—db block buffer cache, shared pool, Java pool, and large pool—occupy almost all the space inside the SGA. (Relative to the other areas, the redo log buffer does not occupy much space and is inconsequential to our discussion here.) You, as the DBA, must ensure that their respective memory allocations are sufficient.
Suppose you decide that the values of these pools should be 2GB, 1GB, 1GB, and 1GB respectively. You would set the following initialization parameters to mandate the sizes of the pools for the database instance.
db_cache_size = 2g
shared_pool_size = 1g
large_pool_size = 1g
java_pool_size = 1g
Now, take a close look at these parameters. Honestly, are these values accurate?
I'm sure you have your doubts. In real life, no one can specify these pools to an exact science—they depend too heavily on the processing inside the database and the nature of processing changes from time to time.
Here's an example scenario. Say you have a typical, "mostly" OLTP database and have dedicated less memory for the buffer cache than you would have for a purely OLTP one (few of which exist anymore). One day, your users turn loose some very large full table scans for end-of-the-day reporting. Oracle9i Database gives you the ability to change the allocation online, but because the total physical memory available is limited, you decide to pull something away from the large pool and the Java pool:
alter system 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 fine for a while, but then the nightly RMAN jobs—which use the large pool—begin and the pool immediately falls short. Again, you come to the rescue by supplementing the large pool with some memory from the db cache.
The RMAN jobs complete, but then a batch program that uses Java extensively fires up, and consequently, you start to see Java pool-related errors. So, you reallocate the pools (again) to accommodate the demands on the Java pool and db 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, the OLTP jobs come back online and the cycle repeats all over again!
One alternative to this vicious cycle is to set the maximum requirements of each pool permanently. By doing that, however, you may allocate a total SGA more than the available memory—thereby increasing the risk of swapping and paging when the allocation is less than adequate for each pool. The manual reallocation method, although impractical, looks pretty good right now.
Another alternative is to set the values to acceptable minimums. However, when demand goes up and memory is not available, performance will suffer.
Note that in all these examples the total memory allocated to SGA remained the same, while the allocation among the pools changed based on immediate requirements. Wouldn't it be nice if the RDBMS were to automatically sense the demand from users and redistribute memory allocations accordingly?
The Automatic Shared Memory Management feature in Oracle Database 10g does exactly that. You can decide the total size of the SGA and then set a parameter named SGA_TARGET that decides the total size of the SGA. The individual pools within the SGA will be dynamically configured based on the workload. A non-zero value of the parameter SGA_TARGET is all that is needed to enable the automatic memory allocation.
Setting up Automatic Shared Memory Management
Let's see how this works. First, determine the total size of the SGA. You can estimate this value by determining how much memory is allocated right now.
SQL> select sum(value)/1024/1024 from v$sga;
SUM(VALUE)/1024/1024
--------------------
500
The current total size of the SGA right now is approximately 500MB, which will become the value of SGA_TARGET. Next, issue the statement:
alter system set sga_target = 500M scope=both;
This approach obviates the need to set individual values for the pools; thus, you'll need to make their values zero in the parameter file or remove them completely.
shared_pool_size = 0
large_pool_size = 0
java_pool_size = 0
db_cache_size = 0
Recycle the database to make the values take effect.
This manual process can also be implemented via Enterprise Manager 10g. From the database home page, choose the "Administration" tab and then "Memory Parameters." For manually configured memory parameters, the button marked "Enable" will be displayed, along with the values of all manually configured pools. Click the "Enable" button to turn Automatic Shared Memory Management on. Enterprise Manager does the rest.
After the automatic memory allocations are configured, you can check their sizes with the following:
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 the pools were automatically configured from the total target size of 500MB. (See Figure 1.) The buffer cache size is 340MB, Java pool is 4MB, large pool is 4MB, and shared pool is 148MB. Together they total (340+4+4+148=) 496MB, approximately the same size as the target SGA of 500MB.
 |
Now suppose the host memory available to Oracle is reduced from 500MB to 300MB, meaning we have to reduce the size of the total SGA. We can reflect that change by reducing the target SGA size.
alter system set sga_target = 300M scope=both;Checking the pools now, we can see that:
SQL> select current_size from v$buffer_pool;The total size occupied is 240+4+4+44 = 296MB, close to the target of 300MB. Notice how the pools were automatically reallocated when the SGA_TARGET was changed, as shown in Figure 2.
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 size of the pools is dynamic. Based on the workload, the pools will expand to accommodate the increase in demand or shrink to accommodate the expansion in another pool. This expansion or contraction occurs automatically without the DBA's intervention, unlike the example in the opening of this article. Returning to that scenario for a moment, assume that after the initial allocation the RMAN job starts, indicating the need for a larger large pool; the large pool will expand from 4MB to 40MB to accommodate the demand. This additional 36MB will be carved out of the db buffers and the db block buffers will shrink, as shown in Figure 3.
 |
The changed sizes of the pools are based on the workload on the system, so the pools needn't be sized for the worst-case scenario—they will automatically adjust to the growth in demand. Furthermore, the total size of the SGA is always within the maximum value specified by SGA_TARGET, so there is no risk of blowing the memory requirement out of proportion (which will lead to paging and swapping). You can dynamically increase the SGA_TARGET to the absolute maximum specified by adjusting the parameter SGA_MAX_SIZE.
Which Pools are Not Affected?
Some pools in SGA are not subject to dynamic resizing, and must be specified explicitly. Notable among them are the buffer pools for nonstandard block sizes and the non-default ones for KEEP or RECYCLE. If your database has a block size of 8K, and you want to configure 2K, 4K, 16K, and 32K block-size pools, you must set them manually. Their sizes will remain constant; they will not shrink or expand based on load. You should consider this factor when using multiple-size buffer, KEEP, and RECYCLE pools. In addition, log buffer is not subject to the memory adjustment—the value set in the parameter log_buffer is constant, regardless of the workload. ( In 10g, a new type of pool can also be defined in the SGA: Streams pool, set with parameter streams_pool_size. This pool is also not subject to automatic memory tuning.)
This gives rise to an interesting question. What if you need a non-default block size pool yet want to manage the other pools automatically?
If you specify any of these non-auto-tunable parameters (such as db_2k_cache_size), their total size is subtracted from the SGA_TARGET value to calculate the automatically tuned parameter values so that the total size of the SGA remains constant . For instance, imagine that the values look like this:
sga_target = 500Mand the rest of the pool parameters are unset. The 2KB buffer pool of 50MB leaves 450MB for the auto-tuned pools such as the default block size buffer pool (db_cache_size), shared pool, Java pool, and large pool. When the non-tunable parameter such as the 2KB block size pool is dynamically adjusted in such a way that the tunable portion's size is affected, the tunable portion is readjusted. For example, raising the value of db_2k_cache_size to 100MB from 50MB leaves only 400MB for the tunable parameters. So the tunable pools such as shared, large, Java, and default buffer pools shrink automatically to reduce their total size to 400MB from 450MB, as shown in Figure 4.
db_2k_cache_size = 50M
 |
But what if you have sufficient memory available or the risks described above may not be that pronounced? If so, you can turn off automatic resizing by not specifying the parameter SGA_TARGET in the parameter file, by setting it to zero in the file, or by changing it to zero dynamically with ALTER SYSTEM. When SGA_TARGET is set to zero, the current values of the pools are automatically set to their parameter.
Using Enterprise Manager
You can also use Enterprise Manager 10g to manipulate these parameters. From the database home page, click the hyperlink "Memory Parameters," which will show you a screen similar to the one in Figure 5.
 |
Note the items circled in red: The database is running in Automatic Shared Memory Management mode and the total size is 564MB, the same value specified in the parameter SGA_TARGET. You can modify it here and click on the Apply button to accept the values; the tunable parameters will automatically adjust.
Pool Size (MB) Buffer 404 Java 4 Large 4 Shared 148
Looking at the above you might conclude that the Java and large pools are a bit inadequate at 4MB; this value will definitely need to be increased at runtime. Therefore, you may want to make sure the pools at least start with higher values—say, 8MB and 16MB respectively. You can do that by explicitly specifying the value of these pools in the parameter file or dynamically using ALTER SYSTEM as shown below. alter system set large_pool_size = 16M;
Checking the pools now, you can see:
alter system set java_pool_size = 8M;SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
The reallocation of the pools is shown below:
POOL MBYTES
------------ ----------
java pool 8
large pool 16
shared pool 148
SQL> select current_size from v$buffer_pool;
CURRENT_SIZE
------------
388
Pool Size (MB) Buffer 388 Java 8 Large 16 Shared 148
Note how the Java and large pools have been reconfigured to 8MB and 16MB respectively, and that to keep the total SGA under 600MB, the buffer pool has reduced to 388MB from 404MB. Of course, these pools are still governed by Automatic Shared Memory Management—their sizes will shrink or expand based on demand. The values you have specified explicitly put a lower limit on the pool size; they will never sink below this limit.
Happy Learning !
No comments:
Post a Comment
Thanks for you valuable comments !