centos中sqlplus的性能调优方法

CentOS 上 SQL*Plus 性能调优方法

一 客户端 SQL*Plus 设置

• 增大数组抓取：提高 ARRAYSIZE（如 1000–5000），可显著减少往返次数（round-trips），提升大结果集取数性能。示例：SET ARRAYSIZE 2000。
• 减少冗余输出：关闭不必要的显示项，例如 SET FEEDBACK OFF、SET HEADING OFF、SET PAGESIZE 0（非交互式导出时）、SET TERMOUT OFF（仅在 spool 到文件时）。
• 控制行宽与换行：设置合适的 SET LINESIZE（如 32767）与 SET WRAP OFF，避免换行与截断导致的额外处理。
• 交互与编辑便利：使用 rlwrap sqlplus 提供命令行编辑与历史，提高批量执行与调试效率（不影响数据库性能）。
• 执行计划与统计：在 SQL*Plus 中通过 EXPLAIN PLAN FOR 与 SET AUTOTRACE ON 查看执行计划与统计，定位全表扫描、缺失索引等问题。
• 提示：SQL*Plus 为客户端工具，以上设置主要优化“取数/显示/往返”开销，真正的 SQL 执行效率仍取决于数据库侧执行计划与统计信息。

二 SQL 与对象层优化

• 只查需要的列：避免 **SELECT ***，减少网络与内存占用。
• 优化 WHERE 条件：尽量避免在 WHERE 中使用 OR，可改写为 UNION 或 UNION ALL（注意去重语义）。
• 合理使用索引：为高频过滤/连接列建立合适的 B-Tree 索引，避免隐式类型转换导致索引失效。
• 分区裁剪：对大表按时间/地域等维度使用 分区表，提升扫描与维护效率。
• 统计信息：定期收集表/索引统计，使用 ANALYZE TABLE 或 DBMS_STATS 包，保证优化器选择更优执行计划。
• 索引维护：避免过多索引（写放大），对高碎片索引执行重建/重组。

三 操作系统与硬件层优化

• 资源充足：确保 CPU、内存、磁盘空间 充足，避免资源争用成为瓶颈。
• 存储优先：使用 SSD 提升 I/O 性能，缩短物理读写与日志写入时间。
• 文件句柄与网络：提升 ulimit -n（打开文件数）与内核网络参数（如 /etc/sysctl.conf 中的 net.core.somaxconn、net.ipv4.tcp_tw_reuse 等），避免连接/网络瓶颈。
• 会话管理：避免频繁短连接/断开，尽量复用会话或采用连接池策略（见下节）。

四 连接与会话管理策略

• 短会话批处理：在脚本中合并多条语句、使用 / 或 ; 批量执行，减少登录/登出与网络往返。
• 连接复用与 DRCP：对于多脚本并发或工具化调用，建议在数据库启用 Database Resident Connection Pool（DRCP），客户端在连接串中指定 SERVER=POOLED，由数据库侧维护长驻连接，降低频繁建连开销。
• 服务器端连接上限：当并发连接数触顶（如 ORA-00020），可按需调整数据库参数（如 PROCESSES/SESSIONS），并重启实例；同时排查应用是否存在连接泄漏。
• 连接池与上限关系：经验公式 SESSIONS ≈ 1.1 × PROCESSES + 5，调整时需同步评估内存与 CPU 能力。

五 快速检查清单与示例脚本

• 快速检查清单
  • 客户端：ARRAYSIZE 是否足够大；是否关闭了 FEEDBACK/HEADING/PAGESIZE/TERMOUT（按需）；是否使用 rlwrap。
  • SQL：是否 SELECT *；WHERE 中是否可用 UNION 替代 OR；是否命中索引；是否分区裁剪；统计信息是否最新。
  • OS/硬件：CPU/内存/磁盘是否瓶颈；是否使用 SSD；ulimit -n 与网络参数是否合理。
  • 连接：是否复用会话；是否需要启用 DRCP；PROCESSES/SESSIONS 是否匹配并发。
• 示例脚本（SQL*Plus 交互式或脚本中使用）

  -- 1) 客户端性能相关
  SET ARRAYSIZE 2000
  SET FEEDBACK OFF
  SET HEADING OFF
  SET PAGESIZE 0
  SET LINESIZE 32767
  SET WRAP OFF
  SET TERMOUT OFF   -- spool 导出时建议关闭终端输出
  
  -- 2) 执行计划与统计
  EXPLAIN PLAN FOR
    SELECT /*+ INDEX(t idx_col) */ col1, col2
    FROM   t
    WHERE  col1 = :x AND col2 > :y;
  
  SET AUTOTRACE ON EXPLAIN STATISTICS
  SELECT /*+ INDEX(t idx_col) */ col1, col2
  FROM   t
  WHERE  col1 = :x AND col2 > :y;
  
  -- 3) 批量导出（减少往返与终端开销）
  SPOOL /tmp/result.csv
  SELECT col1 || ',' || col2 FROM t WHERE ROWNUM <= 1000000;
  SPOOL OFF
  

  说明：上述客户端设置与 AUTOTRACE/EXPLAIN 仅影响 SQL*Plus 的取数与显示行为；SQL 改写、索引/分区与统计信息才是决定执行效率的关键。