1. Oracle 12c SQL执行监控全攻略在Oracle数据库运维中实时监控SQL执行情况是性能调优和故障排查的基础技能。当系统出现性能瓶颈时DBA需要快速定位问题SQL当应用出现异常时开发人员需要确认程序实际执行的SQL语句。本文将详细介绍Oracle 12c环境下查看历史SQL和实时SQL的完整方案包含7种实用方法和14个实战技巧。2. 核心监控视图解析2.1 V$SQL与V$SQLAREA深度对比这两个视图是监控SQL执行的基石但设计目的不同V$SQL存储所有共享池中的SQL语句及其执行统计信息V$SQLAREA聚合相同SQL语句的统计信息忽略大小写和空格差异关键字段说明SELECT sql_id, -- SQL唯一标识符 executions, -- 执行次数 elapsed_time, -- 总耗时(微秒) cpu_time, -- CPU耗时 disk_reads, -- 物理读 buffer_gets, -- 逻辑读 rows_processed, -- 处理行数 sql_text -- SQL文本(前1000字符) FROM v$sql WHERE last_active_time SYSDATE-1/24; -- 最近1小时注意v$sqltext存储完整SQL文本当sql_text截断时需关联此视图2.2 V$SESSION与V$SQLSTATS联动查询实时监控会话执行的SQLSELECT s.sid, s.serial#, s.username, s.machine, q.sql_id, q.sql_text, q.executions, q.elapsed_time/1000000 Elapsed(s) FROM v$session s JOIN v$sql q ON s.sql_id q.sql_id WHERE s.status ACTIVE AND s.type ! BACKGROUND;典型输出分析高ELAPSED_TIME但低EXECUTIONS可能为长事务或阻塞操作高DISK_READS可能缺少合适索引高BUFFER_GETS可能存在全表扫描3. 历史SQL查询方案3.1 AWR报告深度解析AWR(Automatic Workload Repository)是Oracle提供的性能诊断工具包含SQL执行历史生成AWR报告SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_text( l_dbid (SELECT dbid FROM v$database), l_inst_num (SELECT instance_number FROM v$instance), l_bid (SELECT min(snap_id)-1 FROM dba_hist_snapshot WHERE begin_interval_time SYSDATE-1), l_eid (SELECT max(snap_id) FROM dba_hist_snapshot) ));关键章节解读SQL ordered by Elapsed TimeSQL ordered by CPU TimeSQL ordered by Gets3.2 ASH(Active Session History)实时分析ASH每秒采样活动会话信息保留时间通常为1小时SELECT sample_time, session_id, sql_id, event, wait_time, time_waited FROM v$active_session_history WHERE sample_time SYSDATE-1/24 ORDER BY sample_time DESC;4. 高级监控技巧4.1 SQL Trace与TKPROF实战开启10046事件跟踪-- 会话级跟踪 ALTER SESSION SET tracefile_identifier my_trace; ALTER SESSION SET events 10046 trace name context forever, level 12; -- 对指定会话跟踪 EXEC DBMS_MONITOR.session_trace_enable(session_id123, serial_num456, waitsTRUE, bindsTRUE);使用TKPROF解析跟踪文件tkprof ora_12345_my_trace.trc output.txt sysno sortprsela,exeela,fchela4.2 实时SQL监控(Real-Time SQL Monitoring)Oracle 11g引入的功能对长时间运行的SQL自动监控SELECT dbms_sqltune.report_sql_monitor( sql_id g8uxf5abfz7v9, type TEXT, report_level ALL) FROM dual;5. 性能优化实战案例5.1 定位高负载SQLSELECT sql_id, executions, round(elapsed_time/1000000,2) 总耗时(s), round(elapsed_time/decode(executions,0,1,executions)/1000000,4) 单次耗时(s), round(buffer_gets/decode(executions,0,1,executions),2) 逻辑读/次, round(disk_reads/decode(executions,0,1,executions),2) 物理读/次, sql_text FROM ( SELECT * FROM v$sqlarea ORDER BY buffer_gets DESC ) WHERE rownum 10;5.2 识别低效执行计划SELECT p.sql_id, p.child_number, p.plan_hash_value, p.object_name, p.options, p.cost, p.cardinality FROM v$sql_plan p WHERE p.sql_id g8uxf5abfz7v9 ORDER BY p.id;6. 监控脚本集锦6.1 实时监控脚本SELECT s.sid, s.serial#, s.status, s.username, s.osuser, s.machine, s.program, s.module, s.action, q.sql_id, substr(q.sql_text,1,100) sql_text, q.executions, q.elapsed_time/1000000 elapsed(s), q.cpu_time/1000000 cpu(s), q.disk_reads, q.buffer_gets FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id WHERE s.type USER ORDER BY q.elapsed_time DESC NULLS LAST;6.2 历史SQL分析脚本SELECT h.sql_id, h.disk_reads_total, h.buffer_gets_total, h.elapsed_time_total/1000000 elapsed(s), s.sql_text FROM dba_hist_sqlstat h JOIN dba_hist_sqltext s ON h.sql_id s.sql_id WHERE h.snap_id BETWEEN begin_snap AND end_snap ORDER BY h.disk_reads_total DESC FETCH FIRST 20 ROWS ONLY;7. 常见问题排查7.1 SQL文本截断问题当SQL_TEXT被截断时使用DBMS_LOB获取完整SQLSELECT sql_id, dbms_lob.substr(sql_fulltext, 4000, 1) as full_text FROM v$sqlarea WHERE sql_id g8uxf5abfz7v9;7.2 共享池刷新导致SQL丢失配置AWR保留策略确保历史SQL不丢失-- 修改AWR保留时间(默认8天) EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention 43200, -- 分钟(30天) interval 60); -- 快照间隔(分钟)8. 安全审计方案8.1 细粒度审计(FGA)监控特定表的SELECT操作BEGIN DBMS_FGA.add_policy( object_schema HR, object_name EMPLOYEES, policy_name AUDIT_EMP_SALARY, audit_condition SALARY 10000, audit_column SALARY, handler_schema NULL, handler_module NULL, enable TRUE, statement_types SELECT); END; /查询审计结果SELECT timestamp, db_user, os_user, sql_text FROM dba_fga_audit_trail ORDER BY timestamp DESC;9. 性能监控视图维护9.1 统计信息收集策略-- 自动统计信息收集 BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name auto optimizer stats collection, operation NULL, window_name NULL); END; / -- 手动收集统计信息 EXEC DBMS_STATS.gather_schema_stats(SCOTT);9.2 AWR基线管理创建性能基线BEGIN DBMS_WORKLOAD_REPOSITORY.create_baseline( start_snap_id 1234, end_snap_id 1235, baseline_name Peak Load Baseline, expiration NULL); END; /10. 云环境特别注意事项在Oracle Cloud和Exadata环境中监控需额外关注IORM资源管理对SQL执行的影响Smart Scan卸载的SQL语句存储节点资源使用情况-- 检查资源管理器配置 SELECT * FROM dba_rsrc_plans; SELECT * FROM v$rsrc_consumer_group_stats; -- Exadata特定监控 SELECT * FROM v$sqlstats WHERE io_cell_offload_eligible 0;
