这是我参与8月更文挑战的第22天，活动详情查看：8月更文挑战
内存管理方式，详情见上篇文章： juejin.cn/post/699889…
前提：
1） 操作系统内存要大于数据库内存。
2） Linux 环境，/etc/sysctl.conf配置文件中的 kernel.shmmax 参数需大于所要调整的内存。
3） Linux 环境，使用 AMM 内存管理方式时，/dev/shm 大小需大于所要调整的内存。
修改 ASMM 内存
--备份参数文件
create pfile='/home/oracle/pfile20210822.ora' from spfile;
--查看内存管理方式，确认为 ASMM 管理
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
parallel_servers_target integer 16
pga_aggregate_target big integer 108M
sga_target big integer 252M
--修改 sga
SQL> show parameter sga;
SQL> alter system set sga_max_size=4096M scope=spfile;
System altered.
SQL> alter system set sga_target=4096M scope=spfile;
System altered.
--重启使更改生效：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 2026296 bytes
Variable Size 805307592 bytes
Database Buffers 3472883712 bytes
Redo Buffers 14749696 bytes
Database mounted.
Database opened.
查看修改后额 sga
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
parallel_servers_target integer 16
pga_aggregate_target big integer 100M
sga_target big integer 4096M
--修改 pga，不用重启DB，直接在线修改。
查看 workarea_size_policy 参数值：
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
若 workarea_size_policy 参数值不是 auto，需要先使用下列语句修改为 auto。
SQL> alter system set workarea_size_policy=auto scope=both;
System altered.
修改 pga。
SQL> alter system set pga_aggregate_target=512m scope=both;
System altered.
查看修改后的 pga
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 536870912
修改 AMM 内存
--备份参数文件
create pfile='/home/oracle/pfile20210822.ora' from spfile;
--查看内存管理方式，确认为 AMM 管理
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 360M
memory_target big integer 360M
parallel_servers_target integer 16
pga_aggregate_target big integer 0
sga_target big integer 0
--修改 memory_max_target、memory_target。
sql>alter system set memory_max_target = 1024m scope = spfile;
System altered.
sql>alter system set memory_target = 1024m scope = spfile;
System altered.
--重启使更改生效：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 2026296 bytes
Variable Size 805307592 bytes
Database Buffers 3472883712 bytes
Redo Buffers 14749696 bytes
Database mounted.
Database opened.
--查看修改后的 memory_max_target、memory_target。
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1024M
memory_target big integer 1024M
parallel_servers_target integer 16
pga_aggregate_target big integer 0
sga_target big integer 0
