In my previous posts we came across few parameters that can affect the performance of Oracle database, such as SHARE POOL, BLOCK BUFFERS, SORT AREA, etc. Adding to these lists there can be other parameters too, which can affect the database as per the database design. Now tuning is an ongoing process, as no database system can be tuned once and let it be that way. Performance issues are bound to happen, and the database has to be tuned to address such issues. Going by what I have shared till now, for every such issues, changing several of these parameters can be a headache. But fortunately, Oracle has made great strides in simplifying memory management over the last few versions of the database. Oracle 9i automated PGA management by introducing PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by automating SGA management using the SGA_TARGET parameter. Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.
Automatic memory management is configured using two new initialization parameters:
- MEMORY_TARGET : The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is “0”.
- MEMORY_MAX_TARGET : This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.
Enabling automatic memory management on a system that didn’t previously use it, is a simple task. Assuming one want to use a similar amount of memory to your current settings you will need to use the following calculation.
MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")
The following queries show you how to display the relevant information and how to combine it in a single statement to calculate the required value.
-- Individual values. SELECT name, value FROM v$parameter WHERE name IN ('pga_aggregate_target', 'sga_target') UNION SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value FROM v$pgastat WHERE name = 'maximum PGA allocated'; -- Calculate MEMORY_TARGET SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga, (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga, (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;
Assuming our required setting was 10G, we might issue the following statements.
CONN / AS SYSDBA -- Set the static parameter. Leave some room for possible future growth without restart. ALTER SYSTEM SET MEMORY_MAX_TARGET=11G SCOPE=SPFILE; -- Set the dynamic parameters. Assuming Oracle has full control. ALTER SYSTEM SET MEMORY_TARGET=10G SCOPE=SPFILE; ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE; ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE; -- Restart instance. SHUTDOWN IMMEDIATE; STARTUP;
Once the database is restarted the
MEMORY_TARGET parameter can be amended as required without an instance restart.
ALTER SYSTEM SET MEMORY_TARGET=9G SCOPE=SPFILE;
Once the AMM settings are in place, tuning the memory parameters can be done very easily. The V$MEMORY_TARGET_ADVICE view provides information to help tune the MEMORY_TARGET parameter. It displays a range of possible MEMORY_TARGET settings, as factors of the current setting, and estimates the potential DB Time to complete the current workload based on these memory sizes.
When you have large SGA sizes you can get considerable benefits from using HugePages. Automatic Memory Management and HugePages on Linux are not compatible, which means AMM is probably not a sensible option for any large systems. Instead, AutomaticSharedMemoryManagement and AutomaticPGAManagement should be used as they are compatible with HugePages.
We shall know about AutomaticSharedMemoryManagement and AutomaticPGAManagement along with HugePages in the next post. See you all soon.