Effects of Very Frequent Growth and Shrink Operations on Automatic Memory Management(AMM)

dbtut

6 years ago

In Oracle databases, when the Automatic Memory Management (AMM) is active, if the memory values grow and shrink too frequently, wait events can be observed in the database.

In this case, it will be necessary to reduce the frequency of growth and shrink operations performed in memory.

For this, it is necessary to increase the value of “_memory_broker_stat_interval _ parameter from Oracle parameters. The default value of this parameter is 30 seconds.

The last 800 resize operations can be seen with the following query.

SELECT START_TIME,
END_TIME,
COMPONENT,
OPER_TYPE,
OPER_MODE,
PARAMETER,
ROUND (INITIAL_SIZE / 1024 / 1204) AS INITIAL_SIZE_MB,
ROUND (TARGET_SIZE / 1024 / 1204) AS TARGET_SIZE_MB,
ROUND (FINAL_SIZE / 1024 / 1204) AS FINAL_SIZE_MB,
STATUS
FROM V$MEMORY_RESIZE_OPS
ORDER BY START_TIME DESC;

SELECT START_TIME,

END_TIME,

COMPONENT,

OPER_TYPE,

OPER_MODE,

PARAMETER,

ROUND (INITIAL_SIZE / 1024 / 1204) AS INITIAL_SIZE_MB,

ROUND (TARGET_SIZE / 1024 / 1204) AS TARGET_SIZE_MB,

ROUND (FINAL_SIZE / 1024 / 1204) AS FINAL_SIZE_MB,

STATUS

FROM V$MEMORY_RESIZE_OPS

ORDER BY START_TIME DESC;

We can change the parameter as follows.

SQL> alter system set "_memory_broker_stat_interval"=999 scope=both sid='*';

1	SQL> alter system set "_memory_broker_stat_interval"=999 scope=both sid='*';