Oracle Database - Automatic Memory Management

 Automatic Memory Management

When Automatic Memory Management is enabled, the database will automatically set the optimal distribution of memory. The distribution of memory will change from time to time to accomodate changes in the workload.

Automatic Memory Management has been successfully enabled. The Memory sizes will be adjusted automatically by the database to achieve optimal distribution. The distribution of memory will change from time to time to accomodate changes in the workload.

當Automatic Memory Management is enabled，你只需要調整下方兩個參數，其他的Oracle會自動調整。

• Total Memory Size
• Maximum Memory Size

 可以看到SGA參數會自動調整

PGA參數也是自動調整

當Automatic Memory Management is disabled，就可以去調整

• Total SGA Size
• Maximum SGA Size

•  調整 Aggregate PGA Target

如何手動開關  Automatic Memory Management ?

--關 Automatic Memory Management
ALTER SYSTEM SET memory_target='0' SCOPE=BOTH;
ALTER SYSTEM SET sga_max_size = 786432000 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 524288000 SCOPE=BOTH;

延伸閱讀: 如何完整關閉 AMM (Automatic Memory Management)

--開 Automatic Memory Management
ALTER SYSTEM SET memory_target='805306368' SCOPE=BOTH;
ALTER SYSTEM SET sga_target='0' SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target='0' SCOPE=BOTH;

延伸閱讀: 如果不小心設了sga_max_size，如何完整啟動 Automatic Memory Management