centos sqlplus性能监控工具

CentOS 上使用 SQL*Plus 的 Oracle 性能监控工具与用法

一 工具总览

• SQL*Plus 内置：AUTOTRACE、EXPLAIN PLAN + DBMS_XPLAN 用于单条 SQL 的执行计划与统计；配合 SET TIMING ON 获取语句耗时。适合开发/运维在命令行快速诊断。
• 轻量 SQL 脚本：基于 v$session_wait、v$sysstat、v$rollstat、v$filestat、v$librarycache、v$rowcache 等视图，覆盖会话等待、I/O、SGA 命中率、回滚段争用等核心指标，便于在最小依赖环境（仅 sqlplus）下巡检。
• Oracle 官方实时工具：oratop 提供类 top 的实时视图，覆盖 进程/SQL、等待事件、ADG 等，适合在终端快速定位活跃热点。
• 企业级监控：OEM（Cloud Control）、Zabbix、Prometheus + oracle_exporter 做长期可视化与告警；nmon、vmstat、iostat、sar、dstat、top/htop 负责操作系统层资源监控，与数据库层联动排查。
• 历史与趋势：AWR/ASH（需许可）配合 DBA_HIST_SNAPSHOT、DBA_HIST_SYS_TIME_MODEL 做时段对比与负载评估（如 DB time 占比）。

二 快速上手 SQL*Plus 内置工具

• 启用 AUTOTRACE 查看执行计划与统计：
  1. SET AUTOTRACE ON（同时显示结果、执行计划与统计）；2) SET AUTOTRACE ON STATISTICS（仅统计）；3) SET AUTOTRACE TRACEONLY（不显示查询结果，专注统计/计划）；4) SET TIMING ON（显示语句耗时）。
     解读要点：关注执行计划中的 Operation（如全表扫描/索引扫描/连接方式）、Cost；统计信息中重点看 consistent gets、physical reads、sorts(memory/disk) 等，物理读高通常意味着需要索引/改写 SQL/加大缓冲。
• 使用 EXPLAIN PLAN 获取并展示计划：
  EXPLAIN PLAN FOR <your_sql>;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  适合在变更前后对比计划是否退化。

三 轻量 SQL 脚本巡检示例

• 会话等待事件 TopN：
  SELECT event,
  SUM(DECODE(wait_time,0,0,1)) “Prev”,
  SUM(DECODE(wait_time,0,1,0)) “Curr”,
  COUNT(*) “Tot”
  FROM v$session_wait
  GROUP BY event
  ORDER BY 4 DESC;
• SGA 缓冲区命中率：
  SELECT a.value + b.value “logical_reads”,
  c.value “phys_reads”,
  ROUND(100 * ((a.value+b.value)-c.value)/(a.value+b.value),2) “BUFFER HIT RATIO”
  FROM v$sysstat a, v$sysstat b, v$sysstat c
  WHERE a.statistic# = 38 AND b.statistic# = 39 AND c.statistic# = 40;
• 库缓存命中率（Library Cache）：
  SELECT SUM(pins) “Total Pins”, SUM(reloads) “Total Reloads”,
  SUM(reloads)/SUM(pins)*100 “Reload%”
  FROM v$librarycache;
• 数据字典命中率（Row Cache）：
  SELECT (1 - SUM(getmisses)/SUM(gets))*100 “Hit Ratio”
  FROM v$rowcache;
• 表空间与数据文件 I/O：
  SELECT df.tablespace_name, df.file_name,
  f.phyrds, f.phywrts
  FROM v$filestat f, dba_data_files df
  WHERE f.file# = df.file_id
  ORDER BY df.tablespace_name;
• 回滚段争用：
  SELECT name, waits, gets, ROUND(waits/gets,4) “Ratio”
  FROM v$rollstat a, v$rollname b
  WHERE a.usn = b.usn;
  以上脚本可在 sqlplus 中直接执行，建议以 sysdba 或具备相应视图权限的用户运行，并保存输出用于对比。

四 实时监控与历史趋势

• 实时监控：
  • 终端实时热点：oratop（Oracle 官方），覆盖 进程/SQL、等待事件、ADG，适合临时排查“谁在消耗资源”。
  • OS 层联动：nmon、vmstat、iostat、sar、dstat、top/htop 观察 CPU、内存、I/O、网络，与数据库层现象交叉验证（如 I/O 飙升是否伴随 db file sequential/scattered read 等待）。
• 历史与趋势：
  • AWR/ASH（需许可）：基于 DBA_HIST_SNAPSHOT 做时段对比；通过 DBA_HIST_SYS_TIME_MODEL 计算 DB time，评估负载强度（如某小时 DB time=120 分钟/60 分钟=200%）。
  • 自动化巡检：将上述 SQL 脚本纳入 shell + sqlplus 定时任务，输出到日志/监控平台，形成 7×24 巡检基线。

五 实用命令清单

• SQL*Plus 常用：
  • SET TIMING ON（语句耗时）；SET AUTOTRACE ON/ON STATISTICS/TRACEONLY（计划与统计）；
  • SET LINESIZE 1000、SET PAGESIZE 50000（输出格式）；SPOOL 文件名（结果落盘）；
  • 执行脚本：@/path/script.sql；静默执行：sqlplus -s user/pass@sid @script.sql > out.txt。
• 系统监控：
  • nmon（交互式采集）；vmstat 1 60（CPU/内存/IO）；iostat -x 1（磁盘）；sar -u -r -b 1 60（系统资源历史/实时）；dstat（综合资源）；top/htop（进程）。
• 快速巡检组合建议：
  • 实时：oratop → 定位热点 SQL/会话 → 在 sqlplus 中用 AUTOTRACE/EXPLAIN PLAN 细看执行计划与统计；
  • 历史：AWR/ASH 报表 → 对照 OS 层 nmon/iostat 数据，确认是否为资源瓶颈引起。