TECHNOLOGY: Talking Tuning
Sharing Memory—Automatically (By Kimberly Floss) Published In
Put away your scripts and let Oracle automatically resize your memory pools.
It used to be a challenge to size the various memory pools that comprise the Oracle system global area (SGA) for optimal performance. But that was before Oracle Database 10g.
For example, the buffer cache had to be large enough to keep frequently used blocks readily available for fast retrieval—but not so large that the database couldn't allocate memory in one of the other SGA pools when needed. Since various types of applications and usage patterns tax the assorted pools differently, and since the workload can vary minute by minute, manuall
y resizing the SGA components could feel like a never-ending task.
If there wasn't enough free SGA to allocate memory to a specific pool when needed, the database would raise an out-of-memory error, such as
ORA-04031: unable to allocate ...
Also, before Oracle9i Database Release 2, adjusting the pool sizes required bouncing the server—hardly practical in a production environment.
That's why Oracle Database 10g's Automatic Shared Memory Management (ASMM) is such a welcome improvement. First introduced in Oracle Database 10g and further enhanced in Oracle Database 10g Release 2, ASMM automatically sizes many of the memory pools while the database is running, allocating and de-allocating memory as needed. As the workload composition changes, Oracle Database 10g enlarges the appropriate pools and reduces the sizes of other automatically sized pools accordingly. In short, ASMM can save you a lot of trouble—and improve overall performance as well.
Let's take a closer look at how it works and how to use it.
SGA Memory Components
The linchpin of Oracle Database 10g's memory management scheme is the new SGA_TARGETinitialization parameter. The value of this setting determines the total amount of SGA memory that can be allocated across both manually and automatically sized pools. (See Table 1, in the online version of this article at oracle.com/technology/oramag/oracle/ 05-sep/o55tuning.html.) TheSGA_TARGET value imposes a ceiling on the amount of RAM devoted to the Oracle SGA.
Oracle Database 10g Release 2 can automatically tune the size of the shared pool, buffer cache, Java pool, large pool, and streams pool. However, you must still manually tune several SGA components, including the log buffer (at startup time only), the keep buffer cache, the recycle buffer cache, and all of the nonstandard block-size buffer caches (for example, the 32K and 16K buffer caches set by the db_32K_cache_size and db_16K_cache_size parameters). Manually sized components consume SGA from the target value first, and then the remainder of SGA memory is spread across the various autosized pools. In other words, if you set parameter values for any of these manually tuned pools, Oracle Database 10g Release 2 subtracts their sizes from SGA_TARGET first, before allocating memory to the automatically allocated pools.
The SGA_TARGET setting value also includes a small amount of fixed-size SGA. Oracle Database 10g Release 2 sets the fixed size based on the operating system and other criteria. You can see the amount (in bytes) of the fixed-size SGA and totals of other major elements that occupy the SGA by querying the V$SGA view, as follows:
SQL> select * from v$sga;
NAME VALUE
------------------ ----------
Fixed Size 1247780
Variable Size 124319196
Database Buffers 41943040
Redo Buffers 262144
You can query this same view to determine an initial size for SGA_TARGET when you switch from manual to autotuning, by summing all the components as follows:
SQL> select sum(value)/1024/1024 "Megabytes" from v$sga;
Megabytes
------------------
160
Listing 1 shows an example of total real memory allocation for the current SGA from theV$SGA_DYNAMIC_COMPONENTS view (introduced in Oracle9i Database), which contains both manual and autotuned SGA components.
Code Listing 1: Query of V$SGA_DYNAMIC_COMPONENTS
SQL> select component, current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
------------------------------- ------------
shared pool 92274688
large pool 8388608
java pool 8388608
streams pool 12582912
DEFAULT buffer cache 33554432
KEEP buffer cache 4194304
RECYCLE buffer cache 4194304
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0
13 rows selected.
Using ASMM
As with the other manageability features of Oracle Database 10g, ASMM requires you to set theSTATISTICS_LEVEL parameter to at least TYPICAL (the default), so make sure this setting is correct before you try to enable ASMM. You can enable it in the following ways:
Set SGA_TARGET to a nonzero value in the initialization parameter file (pfile).
Use Oracle Enterprise Manager (or Oracle Grid Control) or ALTER SYSTEM to dynamically set a nonzero value for SGA_TARGET in the server parameter file (spfile).
To use the command line ( ALTER SYSTEM ), set a value for SGA_TARGET and then set the parameters for all of the autotuned pools to 0, as follows:
SQL> alter system set sga_target=160M scope=both;
System altered.
SQL> alter system set db_cache_size=0;
System altered.
SQL> alter system set shared_pool_size=0;
System altered.
<
repeat for each autotuned pool>
If you don't set the values for each of the autotuned pools to zero after switching to ASMM, whatever value you had set for a parameter will function as a lower threshold value for that pool—ASMM won't go below a nonzero value, even if it needs the memory for another autotuned pool.
Rather than entering each of these ALTER SYSTEM commands for each of the autotuned pools as shown above, you can use Oracle Enterprise Manager to accomplish the same thing in one step, as part of the switch from Manual to Automatic tuning, by clicking the Enable button on the Memory Parameters page. This also shows you at a glance the various memory allocations at any time. To open the Memory Parameters page, from Database Control's Administration page, click the Advisor Central link (under the Related Links heading near the bottom of the page), and then click the Memory Advisor link.
The Memory Parameters page shows the memory allocations across all major components over time, since the last restart of the database, as shown in Figure 1. Lower down on the page you'll see a pie chart and a table displaying the Current Allocations. The chart shows the allocations as percentages of the total SGA that can be allocated, while the table shows the component and a value, in megabytes, of the allocation.
Figure 1: Memory Parameters page
Also on the Memory Parameter page, adjacent to the Total SGA Size field, is an Advice button—new in Oracle Database 10g Release 2. This lets you assess the impact of increasing (or decreasing) the system's target SGA. The v$sga_target_advice view also provides this information, as shown in Listing 2.
The v$sga_target_advice view gives you the information you need to modify theSGA_TARGET parameter (up to the size of the SGA_MAX_SIZE , if necessary). For the example in Listing 2, we can increase the SGA_TARGET to 200 and thereby reduce physical reads by about 10%.
Code Listing 2: Query of V$SGA_TARGET_ADVICE
SQL> select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads
from v$sga_target_advice order by sga_size_factor;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
--------- ----------------- -------------- ------------------- -------------------
120 .75 421 1 26042
160 1 421 1 8223
240 1.5 421 1 7340
280 1.75 421 1 7340
200 1.25 421 1 7340
320 2 421 1 7340
6 rows selected.
SHARED_POOL_SIZE Value in Oracle Database 10g
In Oracle Database 10g, the shared pool size is the actual value of the parameter only—yet the overhead must still be accounted for in the shared pool. This means that if you're migrating to Oracle Database 10g from a prior release and you plan to manually size the various memory pools, you must make the shared_pool_size parameter value slightly larger in Oracle Database 10g than in your prior release to account for the startup overhead. In Oracle Database 10g, you can see precisely how much this startup overhead is by querying the v$sgainfodynamic view—you'll find a value for "Startup overhead in shared pool."If you're going to manually tune Oracle Database 10g SGA pools, note one key difference in how it interprets the shared pool value. In releases prior to Oracle Database 10g, the shared pool size was set equal to the shared_pool_sizeinitialization parameter value, plus the amount of internal SGA overhead—memory that the database allocated during startup to maintain state for various server components of the SGA. So if you set theshared_pool_sizeparameter to 48M in Oracle9i Database, for example, the actual shared pool size allocated by the database might have been 60MB, assuming a startup overhead amount of 12MB.
Conclusion
It doesn't matter how much memory the system has if it's not properly allocated across the appropriate SGA pools at runtime. Yet since the system requirements can change from one minute to the next, these pools must be constantly adjusted. Oracle Database 10g's ASMM is a huge time-saver, and it also reduces the chances of ORA-04031 errors. The result? Your system will perform better—and you'll save time and trouble.
