Memory management for Oracle 10g and >= Oracle 11g

In 10g and 11g versions of Oracle Database, it has been simplified configuration memory structures for both the SGA and the PGA significantly.

Oracle version >=10g

From 10g, 2 new memory management parameters that enormously simplify this task are introduced.

SGA_TARGET, simply set a value and resizes demand values (if they are = 0):

  • Buffer cache (DB_CACHE_SIZE)
  • Shared pool (SHARED_POOL_SIZE)
  • Large pool (LARGE_POOL_SIZE)
  • Java pool (JAVA_POOL_SIZE)
  • Streams pool (STREAMS_POOL_SIZE)

PGA_AGGREGATE_TARGET, set a value and are resized related structures PGA dynamically (as long as they are = 0). Is to say all parameters:

  • SORT_AREA_SIZE
  • BITMAP_MERGE_AREA_SIZE
  • CREATE_BITMAP_AREA_SIZE
  • HASH_AREA_SIZE

Oracle so earnestly recommended to use these parameters and forget the old, something we should implement as soon as possible. I have seen real miracles in performance improvements simply by using the automatic memory management.

Having said that all we need to know is if the size is assigned we need our BD, ie if we are not oversizing or instead we are falling short.

Enhancements parameter PGA_AGGREGATE_TARGET:

Source   
SQL> SELECT PGA_TARGET_FOR_ESTIMATE,PGA_TARGET_FACTOR,ESTD_EXTRA_BYTES_RW FROM v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_EXTRA_BYTES_RW
----------------------- ----------------- -------------------
22020096              ,125            75026432
44040192               ,25            75026432
88080384                ,5            71868416
132120576               ,75             9814016
176160768                 1                   0
211392512               1,2                   0
246624256               1,4                   0
281857024               1,6                   0
317088768               1,8                   0
352321536                 2                   0
528482304                 3                   0
704643072                 4                   0
1056964608                 6                   0
1409286144                 8                   0

The value 1 in the column PGA_TARGET_FACTOR indicates current size of parameter PGA_AGGREGATE_TARGET = 176160768 (in bytes). What be searched is the first value in the column ESTD_EXTRA_BYTES_RW = 0, this indicates that the PGA has not required extra space in no time.

For example, if the first value 0 is found in the column PGA_TARGET_FACTOR with value 1.4, it indicates that the parameter PGA_AGGREGATE_TARGET should be 246624256.

Once you know the right value (provided we have sufficient resources), modify the value (for testing):

Source   
SQL> ALTER system SET PGA_AGGREGATE_TARGET=246624256 scope=memory;

And to make the final change (as long as we are working with spfile):

Source   
SQL> ALTER system SET PGA_AGGREGATE_TARGET=16777216 scope=spfile;

Improvement for the parameter SGA_TARGET:

Source   
SELECT SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME FROM v$sga_target_advice ORDER BY SGA_SIZE_FACTOR
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME
---------- --------------- ------------
140               1         3527
175            1,25         3239
210             1,5         3080
245            1,75         2935
280               2         2893

The value 1 in the SGA FACTOR SIZE column indicates the current size for the parameter SGA_TARGET = 140 (in megabytes). What be searched is the first column value ESTD_DB_TIME that are no longer reduced (or the difference is very small)

In the example we could take directly the value of SGA_SIZE = 280, as shown in reducing ESTD_DB_TIME.
Once you know the right value (provided we have sufficient resources), modify the value (for testing):

Source   
SQL> ALTER system SET SGA_TARGET=280M;

And to make the final change (as long as we are working with spfile):

Source   
SQL> ALTER system SET SGA_TARGET=280M scope=spfile;

SGA_TARGET reductions can be made dynamically, but surely encontermos extensions SGA_MAX_SIZE us that the parameter does not allow us to grow, it will be necessary to stop the database and make the change.

So here it is applicable to 10g and 11g versions of Oracle Database.

 

Oracle version >=11g

In 11g it has been added MEMORY_TARGET parameter still further simplified memory management instance. When we set this parameter what we are doing is to tell Oracle that SGA_TARGET resize and PGA_AGGREGATE_TARGET to demand the application (if not specified explicitly). That is we can fix 6Gb for MEMORY_TARGET and forget the rest of the memory management.

Detecting improvement MEMORY_TARGET:

Source   
SQL> SELECT MEMORY_SIZE,MEMORY_SIZE_FACTOR,ESTD_DB_TIME FROM v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME
----------- ------------------ ------------
308                  1         3563
385               1,25         3767
462                1,5         3538
539               1,75         3508
616                  2         3508

The value 1 in the MEMORY_SIZE FACTOR column indicates the current size for the parameter MEMORY_TARGET = 308 (in megabytes). What be searched is the first column value ESTD_DB_TIME that are no longer reduced (or the difference is very small)

In the example we can take the value of MEMORY_SIZE = 539, because with a higher value is not shown in ESTD_DB_TIME reduction.

Once you know the right value (provided we have sufficient resources), modify the value (testing):

Source   
SQL> ALTER system SET MEMORY_TARGET=539M;

And to make the final change (as long as we are working with spfile):

Source   
SQL> ALTER system SET MEMORY_TARGET=539M scope=spfile;

MEMORY_TARGET reductions can be made dynamically, but for extensions surely find ourselves the MEMORY_MAX_TARGET parameter does not allow us to grow, it will be necessary to stop the DB and make the change.

Leave a Reply