Monitoring Memory Resizing Operations

Problem
You’ve implemented automatic memory management in your database and would like to monitor how the database is currently allocating the various dynamically tuned memory components.

Solution
Under an automatic memory management mode, you can view the current allocations of memory in
any instance by querying the V$MEMORY_DYNAMIC_COMPONENTS view. Querying this view provides vital information to help you tune the MEMORY_TARGET parameter. Here’s how you execute a query against this view:

SQL> select * from v$memory_target_advice order by memory_size;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
———– —————— ———— ——————- ———-
468 .75 43598 1.0061 0
624 1 43334 1 0
780 1.25 43334 1 0
936 1.5 43330 .9999 0
1092 1.75 43330 .9999 0
1248 2 43330 .9999 0
6 rows selected.

Your current memory allocation is shown by the row with the MEMORY_SIZE_FACTOR value of 1 (624 MB in our case). The MEMORY_SIZE_FACTOR column shows alternate sizes of the MEMORY_TARGET parameter as a multiple of the current MEMORY_TARGET parameter value. The ESTD_DB_TIME column shows the time Oracle estimates it will need to complete the current workload with a specific MEMORY_TARGET value. Thus, the query results show you how much faster the database can process its work by varying the value of the MEMORY_TARGET parameter.

How It Works
Use the V$MEMORY_TARGET_ADVICE view to get a quick idea about how optimal your MEMORY_TARGET allocation is. You need to run a query based on this view after a representative workload has been processed by the database, to get useful results. If the view reports that there are no gains to be had by increasing the MEMORY_TARGET setting, you don’t have to throw away precious system memory by allocating more memory to the database instance. Oftentimes, the query may report that potential performance, as indicated by the ESTD_DB_TIME column of the V$MEMORY_TARGET_ADVICE view, doesn’t decrease at a MEMORY_SIZE_FACTOR value that’s less than 1. You can safely reduce the setting of the MEMORY_TARGET parameter in such cases.

You can also use the **V$MEMORY_RESIZE_OPS** view to view how the instance resized various memory components over a past interval of 800 completed memory resizing operations. You’ll see that the database automatically increases or shrinks the values of the SGA_TARGET and PGA_AGGREGATE_TARGET parameters based on the workload it encounters. The following query shows how to use the V$MEMORY_RESIZE_OPS view to understand Oracle’s dynamic allocation of instance memory:

SQL> select component,oper_type,oper_mode,parameter, final_size,target_size
from v$memory_resize_ops
COMPONENT OPER_TYPE OPER_MODE PARAMETER FINAL_SIZE TARGET_SIZE
——————— ———– ——— ————— ———— ————
DEFAULT buffer cache GROW DEFERRED db_cache_size 180355072 180355072
shared pool GROW DEFERRED shared_pool_size 264241152 264241152
…
20 rows selected.

The OPER_TYPE column can take two values - GROW or SHRINK, depending on whether the database grows or shrinks individual memory components as the database workload fluctuates over time. It’s this ability to respond to these changes by automatically provisioning the necessary memory to the various memory components that makes this “automatic” memory management. The DBA will do well by monitoring this view over time, to ensure that automatic memory management works well for his or her databases.