centos上sqlplus内存优化方法 - 问答

CentOS 上 SQL*Plus 内存优化方法

一优化思路与适用场景

将优化分为两层：一是数据库实例层（SGA/PGA 与自动内存管理），它决定了 sqlplus 查询结果在服务器侧能“装多少、算多快”；二是客户端层（sqlplus 自身的行/列缓存与输出控制），它决定了本地终端/脚本的内存与网络占用。
若只是 sqlplus 脚本慢、卡顿或占用内存高，优先做客户端层优化；若出现登录慢、执行慢且系统内存紧张，再检查实例层内存配置与 OS 资源。

二客户端侧 SQL*Plus 设置（立竿见影）

调整一次读取行数与列宽，降低往返与内存拷贝
- SET ARRAYSIZE 500–2000（默认 15，视网络与内存而定；太大占用更多客户端内存）
- SET LINESIZE 最小可用值（默认 80；避免过宽导致每行占用过大）
- SET PAGESIZE 0 或较大值（0 禁用页头，减少频繁刷屏；大报表可设大值）
减少不必要输出与网络流量
- SET SERVEROUTPUT OFF（关闭 DBMS_OUTPUT 缓冲输出）
- SET TERMOUT OFF（在 @/@@/START 脚本时不在屏幕回显，仅在 spool 中记录）
- SET TRIMOUT ON / TRIMSPOOL ON（去除行尾空格，减少传输与内存）
- SET SQLPROMPT ‘SQL>’（避免复杂提示符的变量替换开销）
- COLUMN NOPRINT（对不展示的列隐藏，减少客户端处理）
- SET DEFINE OFF（脚本不使用替代变量 &，减少解析）
- SET APPINFO OFF（不调用 DBMS_APPLICATION_INFO，减少额外调用）
- 适当设置 LONGCHUNKSIZE（仅对 CLOB/LONG/XMLType 等大对象）
使用建议
- 交互式查询：ARRAYSIZE 200–500、LINESIZE 120–200、PAGESIZE 24–500
- 批量导出/报表：ARRAYSIZE 1000–5000、LINESIZE 最小可用、PAGESIZE 0、TERMOUT OFF、TRIMSPOOL ON
- 大对象列：仅在需要时 SELECT 必要列，避免 SELECT *；必要时调小 LONGCHUNKSIZE 控制单次抓取

三数据库实例层内存优化（减少 ORA-4031/内存争用）

启用自动内存管理（AMM）并合理设定上限
- 查看当前：SHOW PARAMETER memory_target; SHOW PARAMETER memory_max_target;
- 计算建议值：memory_target ≥ SGA_TARGET + MAX(PGA_AGGREGATE_TARGET, 历史 maximum PGA allocated)
  - 示例查询：SELECT value/1024/1024 FROM v$pgastat WHERE name=‘maximum PGA allocated’;
- 动态设置（需重启生效的用 scope=spfile）：
  - ALTER SYSTEM SET memory_max_target=8G SCOPE=SPFILE;
  - ALTER SYSTEM SET memory_target=6G SCOPE=SPFILE;
- 也可只调 SGA/PGA：
  - ALTER SYSTEM SET sga_target=4G SCOPE=SPFILE;
  - ALTER SYSTEM SET pga_aggregate_target=2G SCOPE=SPFILE;
使用内存建议视图做容量规划
- V$MEMORY_TARGET_ADVICE、V$SGA_TARGET_ADVICE、V$PGA_TARGET_ADVICE（按不同目标大小给出时间与 I/O 预估）
大页与系统资源
- 在支持的系统上启用大页（HugePages）以减少页表开销、提升 SGA 访问效率（需与实例参数及内核设置协同）
风险提示
- 修改 memory_max_target/sga_max_size 等需重启实例；总内存目标不得超过物理内存与 OS 预留；调大前在测试环境验证并保留回退方案

四 OS 层与连接层优化（避免系统内存耗尽与连接风暴）

内核与网络参数（示例，按内存与负载调优）
- fs.aio-max-nr=1048576；fs.file-max=6815744
- kernel.sem=250 32000 100 128
- net.ipv4.ip_local_port_range=9000 65500
- net.core.rmem_default=262144；net.core.rmem_max=4194304
- net.core.wmem_default=262144；net.core.wmem_max=1048586
- vm.swappiness=10（数据库主机建议较低）
- 共享内存与 HugePages：按内存与实例 SGA 规划设置 kernel.shmmax/kernel.shmall 与 vm.nr_hugepages
连接与超时
- 控制应用/脚本并发连接数，避免短时突发连接风暴
- 合理设置 inbound connect 超时（SQLNET.INBOUND_CONNECT_TIMEOUT），减少半开连接堆积
监控与排障
- 观察 vmstat/sar 与数据库 ALERT 日志；若出现 ORA-3136（连接超时）或系统 swap 升高，优先降并发、查慢 SQL 与网络质量

五快速检查与常用命令

客户端侧
- 查看当前设置：SHOW ARRAYSIZE; SHOW LINESIZE; SHOW PAGESIZE; SHOW SERVEROUTPUT; SHOW DEFINE;
- 一键“轻量”模板（交互式）：
  - SET ARRAYSIZE 300
  - SET LINESIZE 150
  - SET PAGESIZE 100
  - SET SERVEROUTPUT OFF
  - SET TRIMOUT ON
  - SET TRIMSPOOL ON
  - SET SQLPROMPT 'SQL> ’
实例侧
- SHOW PARAMETER memory_target; SHOW PARAMETER memory_max_target;
- SHOW PARAMETER sga_target; SHOW PARAMETER pga_aggregate_target;
- SELECT name, value/1024/1024 FROM v$pgastat WHERE name=‘maximum PGA allocated’;
- 参考建议：SELECT * FROM v$sga_target_advice ORDER BY sga_size;
变更与回退
- 动态生效：ALTER SYSTEM SET … SCOPE=BOTH;
- 需重启：ALTER SYSTEM SET … SCOPE=SPFILE; 后重启实例；保留 pfile 备份以便回退

0 赞

0 踩