企业官网建设流程全解析

1. Oracle 12c SQL执行监控全攻略

作为DBA日常运维中最频繁的操作之一，SQL执行监控直接关系到数据库性能分析和问题排查效率。Oracle 12c在SQL监控方面提供了比以往版本更强大的工具链，今天我们就来深度剖析如何查看历史执行SQL和实时监控运行中SQL的完整方案。

提示：本文所有操作均需具有DBA权限或授予的特定监控权限（如SELECT_CATALOG_ROLE）

2. 历史SQL查询方案

2.1 使用AWR报告

AWR（Automatic Workload Repository）是Oracle内置的性能数据仓库，默认每小时采集一次快照，保留8天数据。查询历史SQL最权威的方式就是分析AWR报告：

-- 查看最近快照ID SELECT snap_id, begin_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC; -- 生成AWR报告 @?/rdbms/admin/awrrpt.sql

在生成的HTML报告中，"SQL Statistics"部分会列出：

• 执行次数TOP SQL
• 逻辑读TOP SQL
• 物理读TOP SQL
• 执行时间TOP SQL

2.2 直接查询DBA_HIST_SQLSTAT

如需定制化查询，可以直接访问AWR底层表：

SELECT sql_id, executions_delta as executions, elapsed_time_delta/1000000 as elapsed_sec, disk_reads_delta as disk_reads, buffer_gets_delta as buffer_gets FROM dba_hist_sqlstat WHERE snap_id BETWEEN 1000 AND 1001 -- 替换为实际快照ID ORDER BY elapsed_time_delta DESC;

2.3 使用STATSPACK（兼容老版本）

对于未购买Diagnostic Pack许可的环境，可以使用STATSPACK：

-- 安装STATSPACK @?/rdbms/admin/spcreate -- 手动创建快照 EXEC statspack.snap; -- 生成报告 @?/rdbms/admin/spreport

3. 实时SQL监控方案

3.1 V$SESSION与V$SQL组合查询

最基础的实时监控方法：

SELECT s.sid, s.serial#, s.username, s.machine, s.sql_id, q.sql_text, s.status, s.last_call_et FROM v$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.type = 'USER' AND s.status = 'ACTIVE';

关键字段说明：

• last_call_et：SQL已执行时间（秒）
• sql_text：前1000个字符的SQL文本
• machine：发起客户端机器名

3.2 SQL Monitor实时监控

Oracle 11g引入的SQL Monitor功能在12c中更加强大：

-- 查看正在执行的SQL监控报告 SELECT dbms_sqltune.report_sql_monitor( sql_id => 'g8uqh3n4a5b7c', type => 'TEXT', report_level => 'ALL') FROM dual;

注意：该功能需要Tuning Pack许可

3.3 ASH（Active Session History）分析

ASH每秒采样一次活动会话，是排查瞬时性能问题的利器：

SELECT sample_time, session_id, sql_id, event, wait_time FROM v$active_session_history WHERE sample_time > SYSDATE - 5/1440 -- 最近5分钟 ORDER BY sample_time DESC;

4. 高级监控技巧

4.1 跟踪特定会话

对问题会话进行SQL跟踪：

-- 开启跟踪 EXEC dbms_monitor.session_trace_enable(session_id => 123, serial_num => 456); -- 关闭跟踪 EXEC dbms_monitor.session_trace_disable(session_id => 123, serial_num => 456); -- 跟踪文件位置 SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

4.2 使用DBMS_SQLTUNE捕获SQL

创建SQL调优集捕获特定SQL：

-- 创建调优集 EXEC dbms_sqltune.create_sqlset(sqlset_name => 'HIGH_LOAD_SQL'); -- 从游标缓存加载 DECLARE cur dbms_sqltune.sqlset_cursor; BEGIN OPEN cur FOR SELECT value(p) FROM table( dbms_sqltune.select_cursor_cache( 'parsing_schema_name = ''APP_USER''', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) p; dbms_sqltune.load_sqlset( sqlset_name => 'HIGH_LOAD_SQL', populate_cursor => cur); END; / -- 查看捕获的SQL SELECT sql_id, sql_text FROM dba_sqlset_statements WHERE sqlset_name = 'HIGH_LOAD_SQL';

5. 性能视图速查表

6. 常见问题排查

6.1 查不到历史SQL的可能原因

1. AWR快照间隔过长（默认1小时）

   • 解决方案：调整STATISTICS_LEVEL=ALL，减小快照间隔

   EXEC dbms_workload_repository.modify_snapshot_settings(interval => 15);

2. SQL已被aged out共享池

   • 解决方案：开启SQL追踪或使用SQL调优集主动捕获

3. 未购买Diagnostic Pack许可

   • 解决方案：使用STATSPACK替代

6.2 监控权限问题

如果遇到"ORA-00942: table or view does not exist"错误，需要授予权限：

GRANT SELECT ON v_$session TO your_user; GRANT SELECT ON v_$sql TO your_user; GRANT SELECT_CATALOG_ROLE TO your_user;

6.3 SQL文本截断问题

当SQL_TEXT被截断时，使用DBMS_LOB包获取完整内容：

SELECT sql_id, dbms_lob.substr(sql_fulltext, 4000, 1) as sql_text FROM v$sqlarea WHERE sql_id = 'g8uqh3n4a5b7c';

7. 自动化监控脚本

7.1 实时监控脚本

保存为monitor_active.sql：

SET LINES 200 PAGES 1000 COL username FOR A15 COL machine FOR A25 COL program FOR A20 COL sql_text FOR A60 SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.sql_id, q.sql_text, s.last_call_et, s.blocking_session FROM v$session s LEFT JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.status = 'ACTIVE' AND s.type = 'USER' ORDER BY s.last_call_et DESC;

7.2 历史TOP SQL脚本

保存为top_sql_last_hour.sql：

SELECT sql_id, executions, round(elapsed_time/1000000,2) as elapsed_sec, round(elapsed_time/decode(executions,0,1,executions)/1000000,4) as avg_sec, buffer_gets, disk_reads FROM ( SELECT sql_id, sum(executions_delta) as executions, sum(elapsed_time_delta) as elapsed_time, sum(buffer_gets_delta) as buffer_gets, sum(disk_reads_delta) as disk_reads FROM dba_hist_sqlstat WHERE snap_id IN ( SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE-1/24 ) GROUP BY sql_id ORDER BY elapsed_time DESC ) WHERE rownum <= 20;

8. 最佳实践建议

1. 关键生产系统建议设置：

   ALTER SYSTEM SET statistics_level=ALL SCOPE=BOTH; ALTER SYSTEM SET timed_statistics=TRUE SCOPE=BOTH;

2. 长期保留重要性能数据：

   EXEC dbms_workload_repository.modify_snapshot_settings( retention => 43200, -- 30天 interval => 15); -- 15分钟

3. 对重要应用创建SQL基线：

   -- 从AWR创建 DECLARE my_plans pls_integer; BEGIN my_plans := dbms_spm.load_plans_from_awr( begin_snap => 1000, end_snap => 1001); END; / -- 从游标缓存创建 DECLARE my_plans pls_integer; BEGIN my_plans := dbms_spm.load_plans_from_cursor_cache( sql_id => 'g8uqh3n4a5b7c'); END; /

4. 使用SQL Tuning Advisor自动优化：

   DECLARE tune_task VARCHAR2(100); BEGIN tune_task := dbms_sqltune.create_tuning_task( sql_id => 'g8uqh3n4a5b7c', scope => 'COMPREHENSIVE', time_limit => 3600); dbms_sqltune.execute_tuning_task(tune_task); END; / -- 查看建议 SELECT dbms_sqltune.report_tuning_task('TASK_123') FROM dual;

通过以上方法，DBA可以全面掌握Oracle 12c中的SQL执行情况。实际工作中建议将常用监控脚本保存为SQL文件，并设置定时任务自动收集性能数据。对于关键业务系统，还应考虑部署Enterprise Manager或第三方监控工具实现可视化监控。