Oracle 12c SQL执行监控实战技巧

1. Oracle 12c SQL执行监控概述在Oracle数据库运维中实时监控SQL执行情况是DBA日常工作的核心需求之一。当数据库出现性能瓶颈时我们往往需要快速定位问题SQL当应用出现异常时可能需要追溯历史SQL执行记录。Oracle 12c提供了多种视图和工具来满足这些监控需求。我管理过多个TB级Oracle生产环境经常需要处理突发的性能问题。掌握SQL执行监控技巧能在关键时刻快速定位问题源头。下面将详细介绍几种实用的监控方法包含大量实战中积累的经验技巧。2. 查看当前正在执行的SQL2.1 使用V$SESSION和V$SQL视图组合查询最基础的监控方法是通过V$SESSION和V$SQL视图关联查询。这个组合能显示当前所有活跃会话及其执行的SQL语句SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program, s.sql_id, q.sql_text, s.last_call_et as exec_seconds FROM v$session s JOIN v$sql q ON s.sql_id q.sql_id WHERE s.type USER AND s.status ACTIVE ORDER BY s.last_call_et DESC;关键点解析last_call_et字段显示SQL已执行时间(秒)排序后可快速发现长时间运行的SQLstatusACTIVE过滤只显示正在执行的会话关联v$sql获取完整的SQL文本我在实际运维中发现生产环境经常有会话伪装成应用服务器连接如通过JDBC。这时machine和program字段特别有用可以识别真正的来源。2.2 监控SQL执行进度对于长时间运行的SQL我们还需要了解其执行进度。Oracle 12c的V$SESSION_LONGOPS视图非常实用SELECT sid, serial#, opname, target, sofar, totalwork, round(sofar/totalwork*100,2) as pct_complete, time_remaining as remain_sec FROM v$session_longops WHERE totalwork 0 AND sofar totalwork;这个查询会显示所有长时间操作的进度包括全表扫描索引创建备份恢复等操作实战经验当pct_complete长时间不变化时通常意味着SQL遇到了性能瓶颈需要进一步分析执行计划。3. 查看历史执行SQL记录3.1 使用AWR报告分析历史SQLOracle的AWR(Automatic Workload Repository)自动收集性能数据包含SQL执行历史。获取最近1小时的TOP SQLSELECT sql_id, executions, round(elapsed_time/1000000) as elapsed_sec, round(elapsed_time/decode(executions,0,1,executions)/1000000,4) as avg_sec_per_exec, sql_text FROM dba_hist_sqlstat JOIN dba_hist_snapshot USING(snap_id, dbid) JOIN dba_hist_sqltext USING(sql_id, dbid) WHERE begin_interval_time sysdate-1/24 ORDER BY elapsed_time DESC;注意事项AWR默认保留8天数据具体保留时间取决于DBMS_WORKLOAD_REPOSITORY包的设置大并发系统可能产生大量AWR数据需定期清理3.2 使用ASH(Active Session History)分析瞬时性能问题ASH每秒采样一次活动会话数据对于诊断瞬时性能问题特别有用SELECT sample_time, session_id, sql_id, event, wait_class, time_waited FROM dba_hist_active_sess_history WHERE sample_time BETWEEN to_date(2023-01-01 14:00,YYYY-MM-DD HH24:MI) AND to_date(2023-01-01 14:05,YYYY-MM-DD HH24:MI) ORDER BY sample_time;ASH的优势在于能捕获那些执行时间很短但频繁出现的SQL这些SQL在AWR中可能被平均化而难以发现。4. 高级监控技巧4.1 实时SQL监控(Real-Time SQL Monitoring)Oracle 11g/12c企业版提供了强大的实时SQL监控功能SELECT dbms_sqltune.report_sql_monitor( sql_id sql_id, type TEXT, report_level ALL) as report FROM dual;这个功能需要诊断包许可它能提供执行计划实时状态每个操作的进度资源消耗详情并行执行情况我曾用这个功能成功诊断过一个并行查询导致的系统挂起问题发现某个并行服务器进程卡在了特定的执行计划步骤。4.2 使用SQL Trace获取详细执行信息对于需要深入分析的SQL可以使用10046事件跟踪-- 开启跟踪 ALTER SESSION SET tracefile_identifier my_trace; ALTER SESSION SET events 10046 trace name context forever, level 12; -- 执行需要跟踪的SQL SELECT * FROM large_table WHERE ... -- 关闭跟踪 ALTER SESSION SET events 10046 trace name context off;生成的trace文件可以用tkprof工具格式化分析tkprof orcl_ora_12345_my_trace.trc output.txt sysno sortprsela,exeela,fchela避坑指南跟踪会产生大量I/O不要在高峰期对大量SQL使用level 12会捕获绑定变量值可能包含敏感信息生产环境建议先在小范围测试5. 性能问题排查实战案例5.1 案例一CPU使用率突然飙升某日收到报警数据库服务器CPU使用率达到95%。我立即执行以下诊断步骤首先查询当前最耗CPU的SQLSELECT session_id, sql_id, cpu_time/1000000 as cpu_sec, elapsed_time/1000000 as elapsed_sec, sql_text FROM v$sqlarea ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;发现一个异常SQL后检查其执行计划SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id));发现该SQL使用了低效的全表扫描立即联系开发人员优化SQL并创建适当索引。5.2 案例二应用响应缓慢但数据库指标正常应用团队报告响应慢但数据库AWR报告显示各项指标正常。我使用ASH数据发现了问题SELECT sql_id, count(*) as samples, sum(time_waited)/1000 as total_wait_ms FROM dba_hist_active_sess_history WHERE sample_time sysdate-30/1440 AND wait_class ! Idle GROUP BY sql_id ORDER BY total_wait_ms DESC;分析发现大量短暂会话在等待log file sync事件最终确认是应用提交过于频繁导致。通过批量提交优化解决了问题。6. 监控自动化与管理建议6.1 创建常用监控脚本建议将常用监控查询保存为脚本文件。例如创建monitor_active.sql-- monitor_active.sql set lines 200 pages 100 col username for a15 col machine for a20 col program for a20 col sql_text for a50 SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program, s.sql_id, substr(q.sql_text,1,50) as sql_text, s.last_call_et as exec_seconds FROM v$session s JOIN v$sql q ON s.sql_id q.sql_id WHERE s.type USER AND s.status ACTIVE ORDER BY s.last_call_et DESC;6.2 设置定期AWR快照确保AWR快照间隔设置合理BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention 43200, -- 保留30天(分钟) interval 30); -- 每30分钟一次 END; /对于大型系统可能需要更频繁的快照如15分钟和更长的保留期。6.3 使用OEM进行可视化监控Oracle Enterprise Manager提供了完善的SQL监控界面可以实时查看SQL执行情况分析历史性能趋势设置性能告警阈值自动捕获问题SQL虽然命令行更灵活但OEM在可视化方面有明显优势。