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 查看执行计划与统计：
- 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 数据，确认是否为资源瓶颈引起。
