Linux SQLPlus性能调优有哪些方法

Linux 上 SQL*Plus 性能调优方法

一 客户端显示与脚本层面的快速优化

• 增大一次取回行数：提高 ARRAYSIZE（1–5000） 可显著减少 SQL*Net 往返次数，提升大数据量抓取速度；但过大将增加客户端内存占用。
• 减少不必要输出与换行：关闭冗余信息（如 SET FEEDBACK OFF、SET HEADING OFF、SET PAGESIZE 0/大值、SET LINESIZE 合理值），并开启 SET TRIMOUT ON / SET TRIMSPOOL ON 去除行尾空格，降低网络与 I/O 负载。
• 关闭或限制客户端侧耗时功能：如 SET SERVEROUTPUT OFF（避免大量 DBMS_OUTPUT 缓冲）、SET APPINFO OFF、SET DEFINE OFF（脚本无替代变量时可关闭解析）、必要时 SET FLUSH OFF。
• 仅在必要时显示数据：使用 SET AUTOTRACE TRACEONLY 获取执行计划与统计而不打印结果集，适合大查询调优。
• 脚本执行与编辑效率：使用 @/@@ 批处理执行脚本；为长 SQL 使用 EDIT/ED 或在 glogin.sql 中预设常用环境（如 SET SQLPROMPT、SET TIME/TIMING ON），减少交互与重复设置。

二 执行计划与统计的正确获取

• 使用 AUTOTRACE 快速查看执行计划与统计：
  • SET AUTOTRACE ON（计划+统计）、ON EXPLAIN（仅计划）、ON STATISTICS（仅统计）、TRACEONLY（不打印结果集）。
  • 使用前需创建 PLAN_TABLE（执行 @$ORACLE_HOME/rdbms/admin/utlxplan.sql）并授予 PLUSTRACE 角色（执行 @$ORACLE_HOME/sqlplus/admin/plustrce.sql）。
• 解读关键统计：关注 consistent gets、physical reads、SQL*Net round-trips、bytes sent/received 等指标，以判断逻辑读、物理读与网络往返是否过多。
• 使用 TIMING 测量脚本或语句块耗时：便于定位瓶颈与验证优化效果。

三 SQL 与对象层面的优化要点

• 只查需要的列：避免 **SELECT ***，减少传输与内存占用。
• 合理使用索引与分区：为高频过滤/连接列建立索引；对大表按业务键做 分区表 提升扫描与维护效率。
• 统计信息及时更新：定期 ANALYZE TABLE 或使用 DBMS_STATS 收集统计，保证执行计划质量。
• 改写低效写法：在 WHERE 中尽量避免大范围使用 OR，可改写为 UNION 等更易走索引的形式（视数据分布与索引而定）。

四 Linux 与网络环境的系统层优化

• 保障资源充足与 I/O 优先：确保 CPU、内存、磁盘空间 充足，优先使用 SSD 降低 I/O 时延。
• 放宽系统限制与优化网络：适当提升 文件句柄限制（ulimit -n），并按需优化 /etc/sysctl.conf 中的网络参数，减少连接与传输瓶颈。
• 客户端便捷性（非性能项）：如 alias sqlplus=‘rlwrap sqlplus’ 提升命令行编辑体验，不影响查询性能。

五 实用配置示例与适用场景

• 批处理导出/迁移（重在吞吐）：
  • SET ARRAYSIZE 5000
  • SET FEEDBACK OFF
  • SET HEADING OFF
  • SET PAGESIZE 0
  • SET LINESIZE 32767
  • SET TRIMOUT ON
  • SET TRIMSPOOL ON
  • SET SERVEROUTPUT OFF
  • SPOOL large_query.out
  • @big_query.sql
  • SPOOL OFF
• 大查询调优（重在诊断）：
  • SET AUTOTRACE TRACEONLY EXPLAIN
  • SET TIMING ON
  • 执行目标 SQL
  • 关注统计中的 round-trips、consistent gets、physical reads 与执行计划是否走索引/分区剪枝
• 交互式开发（重在可读性）：
  • SET LINESIZE 150 PAGESIZE 9999
  • SET SQLPROMPT '&&_USER@&&_CONNECT_IDENTIFIER> ’
  • SET TIME ON TIMING ON
  • 按需开启 SET SERVEROUTPUT ON SIZE UNLIMITED（仅在调试时）
• 适用场景小结：ARRAYSIZE、TRIM/SPOOL、PAGESIZE、FEEDBACK、SERVEROUTPUT 主要影响客户端吞吐与网络往返；AUTOTRACE/TIMING 用于定位与验证；索引、分区、统计与 SQL 改写属于数据库与 SQL 层优化，通常收益更大。