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 备份以便回退

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！