迁移 Oracle 到 PostgreSQL: SGA 和 PGA 内存配置

Oracle 实例分配多个单独的服务器 RAM “池”，用作数据库的各种缓存。其中包括缓冲区缓存、重做缓冲区、Java 池、共享池、大型池等。缓存驻留在系统全局区域（SGA）中，并在所有 Oracle 会话之间共享。

除了 SGA 之外，每个 Oracle 会话都被授予一个额外的内存区域，用于会话专用操作（排序、专用 SQL 游标元素等），称为专用全局区域（PGA）。

Oracle 用法

缓存大小可以针对单个缓存进行控制，也可以由 Oracle 数据库全局自动控制。设置统一的“内存大小”参数使 Oracle 能够自动管理单个缓存大小。

• 所有 Oracle 内存参数都使用ALTER SYSTEM命令进行设置。
• 对内存参数的某些更改需要重新启动实例。

控制内存分配的一些常见 Oracle 参数包括：

• db_cache_size— 用于数据库数据的缓存大小。
• log_buffer— 用于存储 Oracle 重做日志缓冲区的缓存，直到它们被写入磁盘。
• shared_pool_size— 用于存储共享游标、存储过程、控制结构和其他结构的高速缓存。
• large_pool_size— 用于并行查询和 RMAN 备份/恢复操作的缓存。
• Java_pool_size— 用于存储 Java 代码和 JVM 上下文的高速缓存。

虽然这些参数可以单独配置，但大多数数据库管理员选择让 Oracle 自动管理 RAM。数据库管理员配置 SGA 的总体大小，Oracle 根据工作负载特征调整各个缓存的大小。

• sga_max_size— 指定 SGA 的硬限制最大大小。
• sga_target— 为 SGA 及其中的各个缓存设置所需的软限制。

Oracle 还允许控制每个会话专用的专用内存量。数据库管理员配置所有连接会话的可用内存总大小，Oracle 从每个会话的可用内存总量中分配单个专用块。

• pga_aggregate_target— 控制所有会话组合可用内存总量的软限制。
• pga_aggregate_limit— 对所有会话组合的可用内存总量的硬限制（仅限 Oracle 12c）。

此外，您还可以为 SGA 和 PGA 配置一个总体内存限制，并让 Oracle 自动平衡各种内存池之间的内存，而不是手动配置 SGA 和 PGA 内存区域。此行为是使用memory_target和memory_max_target参数启用的。

有关详细信息，请参阅 Oracle 文档中的内存架构和数据库内存分配。

PostgreSQL 用法

PostgreSQL为我们提供了对服务器RAM分配方式的控制。下表包括一些最重要的 PostgreSQL 内存参数。

例子

查看数据库参数的配置值。

show shared_buffers;
show work_mem;
show temp_buffers;

查看所有数据库参数的配置值。

select * from pg_settings;

使用SET SESSION命令修改支持特定于会话的设置的参数的值。在一个会话使用SET SESSION命令更改值不会影响其他会话。

SET SESSION work_mem='100MB';

如果在中止或回滚的事务中发出SET SESSION命令，则SET SESSION命令的效果将消失。提交事务后，效果将持续到会话结束，除非被另一次SET SESSION命令的执行覆盖。

使用SET LOCAL命令修改可在本地设置为单个事务的那些参数的当前值。使用SET LOCAL命令更改一个事务的值不会对同一会话中的其他事务产生后续影响。发出COMMIT或ROLLBACK后，会话级别设置将生效。

SET LOCAL work_mem='100MB';

将运行时参数的值重置为其默认值。

RESET work_mem;

也可以通过直接更新pg_settings表来更改参数值。

UPDATE pg_settings SET setting = '100MB' WHERE name = 'work_mem';

总结

下表仅用作一般参考。Oracle 和 PostgreSQL 的功能可能并不相同。

有关详细信息，请参阅 PostgreSQL 文档中的预写日志和资源消耗。