★ FEATURED ARTICLE 1. 项目概述作为一名DBA我们经常需要排查数据库性能问题或者审计SQL执行情况。Oracle 12c作为企业级数据库提供了多种方式来查看历史执行过的SQL以及当前正在执行的SQL语句。掌握这些方法对于数据库性能调优、故障排查和安全审计都至关重要。在实际工作中我经常遇到需要追踪某个性能问题的SQL源头或者需要监控当前数据库负载情况。通过系统视图和动态性能视图我们可以获取到丰富的信息包括SQL文本、执行计划、资源消耗等关键数据。2. 核心视图与工具解析2.1 V$SQL与V$SQLAREA视图V$SQL和V$SQLAREA是Oracle中最常用的两个动态性能视图用于查看当前共享池中的SQL语句。V$SQL视图包含共享SQL区域中每个子游标的信息而V$SQLAREA视图则包含共享SQL区域的统计信息按SQL文本分组。这两个视图的主要区别在于粒度不同V$SQL更细粒度每个子游标一条记录V$SQLAREA按SQL文本聚合相同SQL文本只显示一条记录查询示例SELECT sql_id, sql_text, executions, buffer_gets, disk_reads FROM v$sql WHERE sql_text LIKE %SELECT%FROM employees%;2.2 V$SESSION与V$SQLSTATS视图V$SESSION视图显示当前会话信息结合V$SQL可以查看当前正在执行的SQLSELECT s.sid, s.serial#, s.username, s.status, q.sql_id, q.sql_text FROM v$session s JOIN v$sql q ON s.sql_id q.sql_id WHERE s.status ACTIVE;V$SQLSTATS视图提供了SQL语句执行的统计信息对于性能分析特别有用SELECT sql_id, executions, elapsed_time/1000000 as elapsed_sec, cpu_time/1000000 as cpu_sec, buffer_gets, disk_reads FROM v$sqlstats ORDER BY elapsed_time DESC;3. 历史SQL查询方法3.1 AWR报告分析AWR(Automatic Workload Repository)是Oracle提供的自动负载信息库默认每小时采集一次快照保存7天。生成AWR报告SELECT output 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 begin_snap_id, l_eid end_snap_id));报告中的SQL Statistics部分包含了这段时间内消耗资源最多的SQL语句。3.2 ASH(Active Session History)分析ASH每秒采样一次活动会话信息保存在内存中默认保留1小时。查询最近15分钟的高负载SQLSELECT sql_id, COUNT(*) as active_sessions FROM v$active_session_history WHERE sample_time SYSDATE - 15/1440 GROUP BY sql_id ORDER BY active_sessions DESC;3.3 审计日志查询如果启用了标准审计可以通过DBA_AUDIT_TRAIL视图查询SELECT username, action_name, sql_text, timestamp FROM dba_audit_trail WHERE sql_text IS NOT NULL ORDER BY timestamp DESC;4. 实时监控与性能分析4.1 实时SQL监控Oracle 12c提供了实时SQL监控功能对于长时间运行的SQL特别有用SELECT dbms_sqltune.report_sql_monitor( sql_id sql_id, type TEXT, report_level ALL) as report FROM dual;4.2 SQL跟踪与分析对于特定会话或SQL可以启用SQL跟踪-- 会话级别跟踪 EXEC DBMS_MONITOR.session_trace_enable(session_id 123, serial_num 456); -- 实例级别跟踪 ALTER SYSTEM SET sql_trace TRUE; -- 生成TKPROF报告 tkprof ora_12345.trc output.txt explainscott/tiger5. 实用查询脚本集锦5.1 查找高资源消耗SQLSELECT sql_id, executions, ROUND(elapsed_time/1000000,2) as elapsed_sec, ROUND(cpu_time/1000000,2) as cpu_sec, buffer_gets, disk_reads, sql_text FROM ( SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text, RANK() OVER (ORDER BY elapsed_time DESC) as elapsed_rank FROM v$sqlstats WHERE executions 0 ) WHERE elapsed_rank 10;5.2 查找长时间运行会话SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program, s.module, q.sql_id, q.sql_text, ROUND(s.last_call_et/60,2) as minutes_running FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id WHERE s.status ACTIVE AND s.last_call_et 300 -- 运行超过5分钟 ORDER BY s.last_call_et DESC;5.3 查找锁等待SQLSELECT l.session_id, s.serial#, s.username, o.object_name, o.object_type, l.oracle_username, l.os_user_name, q.sql_text FROM v$locked_object l JOIN dba_objects o ON l.object_id o.object_id JOIN v$session s ON l.session_id s.sid LEFT JOIN v$sql q ON s.sql_id q.sql_id;6. 性能优化建议6.1 SQL执行计划分析获取SQL执行计划EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id 10; SELECT * FROM TABLE(dbms_xplan.display);6.2 绑定变量窥视问题绑定变量窥视可能导致执行计划不优可以通过以下SQL检查SELECT sql_id, child_number, executions, is_bind_sensitive, is_bind_aware, is_shareable FROM v$sql WHERE sql_id sql_id;6.3 SQL调优建议使用SQL Tuning Advisor获取调优建议DECLARE l_task_name VARCHAR2(30); l_sql_id VARCHAR2(13) : sql_id; BEGIN l_task_name : dbms_sqltune.create_tuning_task( sql_id l_sql_id, scope COMPREHENSIVE, time_limit 60, description Tuning task for SQL_ID || l_sql_id); dbms_sqltune.execute_tuning_task(l_task_name); END; / SELECT dbms_sqltune.report_tuning_task(task_name) FROM dual;7. 安全与权限考虑7.1 必要权限要查询这些视图用户通常需要以下权限SELECT_CATALOG_ROLE或者针对特定视图的SELECT权限GRANT SELECT ON v_$sql TO monitoring_user; GRANT SELECT ON v_$session TO monitoring_user;7.2 敏感信息处理查询结果中可能包含敏感数据建议在生产环境谨慎分享完整SQL文本对输出进行脱敏处理遵守公司数据安全政策8. 自动化监控方案8.1 创建监控脚本可以创建定期运行的Shell脚本自动捕获高负载SQL#!/bin/bash ORACLE_HOME/u01/app/oracle/product/12.2.0/dbhome_1 export ORACLE_SIDorcl export PATH$ORACLE_HOME/bin:$PATH sqlplus -s / as sysdba EOF SET PAGESIZE 1000 SET LINESIZE 200 SET HEADING ON SET FEEDBACK OFF SPOOL /tmp/high_load_sql_$(date %Y%m%d_%H%M).log SELECT sql_id, executions, ROUND(elapsed_time/1000000,2) as elapsed_sec, ROUND(elapsed_time/1000000/NULLIF(executions,0),4) as sec_per_exec, buffer_gets, disk_reads, SUBSTR(sql_text,1,100) as sql_text FROM ( SELECT sql_id, executions, elapsed_time, buffer_gets, disk_reads, sql_text, RANK() OVER (ORDER BY elapsed_time DESC) as elapsed_rank FROM v\$sqlstats WHERE executions 0 AND last_active_time SYSDATE - 1/24 -- 最近1小时 ) WHERE elapsed_rank 20; SPOOL OFF EXIT EOF8.2 使用Oracle Enterprise ManagerOracle EM提供了图形化界面查看SQL执行情况访问EM控制台导航到Performance - SQL Monitoring可以按资源消耗、执行时间等排序查看SQL8.3 第三方监控工具考虑使用专业的数据库监控工具如Oracle Cloud ControlSolarWinds Database Performance AnalyzerQuest Foglight for Oracle9. 常见问题排查9.1 查询返回空结果可能原因及解决方案权限不足 - 确认用户有查询相关视图的权限共享池已刷新 - 查询V$SQL只会显示当前共享池中的SQL没有活跃SQL - 检查V$SESSION中确实有活跃会话9.2 SQL文本被截断Oracle中很多视图的SQL_TEXT列只显示前1000字符要获取完整SQLSELECT sql_id, sql_fulltext FROM v$sql WHERE sql_id sql_id;9.3 历史SQL查询不到如果AWR中查不到历史SQL可能SQL不在快照采集期间执行SQL资源消耗不够高未被捕获AWR保留策略设置太短检查AWR设置SELECT * FROM dba_hist_wr_control;10. 最佳实践建议定期收集基线建立SQL性能基线便于对比分析EXEC DBMS_WORKLOAD_REPOSITORY.create_baseline( start_snap_id 100, end_snap_id 110, baseline_name Normal_Workload);重点关注TOP SQL定期分析资源消耗最高的SQL使用SQL Plan Management防止执行计划退化-- 捕获SQL计划基线 DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded : DBMS_SPM.load_plans_from_cursor_cache( sql_id sql_id); END; /合理设置AWR保留期根据存储容量和需求调整EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention 21600, -- 单位分钟15天 interval 60); -- 快照间隔分钟建立自动化监控设置阈值告警及时发现性能问题11. 高级技巧与经验分享11.1 使用SQLHC进行健康检查SQL Health Check (SQLHC)是Oracle提供的免费工具可以深入分析SQL性能问题下载SQLHC脚本针对特定SQL_ID运行sqlhc.sql TARGET sql_idsql_id分析生成的报告文件11.2 使用DBMS_SQLTUNE捕获SQL对于偶发但重要的SQL可以主动捕获到SQL调优集-- 创建调优集 EXEC DBMS_SQLTUNE.create_sqlset( sqlset_name Critical_SQL_Set, description Captured critical SQL statements); -- 从游标缓存加载SQL DECLARE l_cursor DBMS_SQLTUNE.sqlset_cursor; BEGIN OPEN l_cursor FOR SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.select_cursor_cache( sql_id IN (sql_id1,sql_id2), NULL, NULL, NULL, NULL, 1, NULL, ALL)) p; DBMS_SQLTUNE.load_sqlset( sqlset_name Critical_SQL_Set, populate_cursor l_cursor); END; / -- 分析调优集中的SQL SELECT dbms_sqltune.report_sql_detail( sql_id sql_id, type TEXT, level ALL) as report FROM dual;11.3 使用SQL Patch修复问题SQL对于无法修改的应用SQL可以使用SQL Patch临时修复-- 创建SQL Patch DECLARE l_patch_name VARCHAR2(30); BEGIN l_patch_name : DBMS_SQLDIAG.create_sql_patch( sql_id sql_id, hint_text INDEX(employees emp_dept_idx), name fix_sql_||sql_id); END; / -- 验证SQL Patch SELECT name, sql_text, status, created FROM dba_sql_patches WHERE sql_id sql_id;11.4 使用SQL Plan Baseline稳定性能对于关键业务SQL使用SPM稳定执行计划-- 手动加载计划基线 DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded : DBMS_SPM.load_plans_from_cursor_cache( sql_id sql_id, fixed YES); END; / -- 验证加载的计划 SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE %sql_text%;12. 性能数据持久化方案12.1 创建历史性能表CREATE TABLE sql_performance_history ( capture_date DATE, sql_id VARCHAR2(13), executions NUMBER, elapsed_time NUMBER, cpu_time NUMBER, buffer_gets NUMBER, disk_reads NUMBER, sql_text CLOB, CONSTRAINT pk_sql_perf_hist PRIMARY KEY (capture_date, sql_id) ) TABLESPACE tools; CREATE INDEX idx_sql_perf_hist_sqlid ON sql_performance_history(sql_id);12.2 定期捕获性能数据BEGIN INSERT INTO sql_performance_history SELECT SYSDATE, sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, (SELECT sql_fulltext FROM v$sql s WHERE s.sql_id a.sql_id AND ROWNUM 1) FROM v$sqlstats a WHERE executions 0 AND last_active_time SYSDATE - 1/24; -- 最近1小时 COMMIT; END; /12.3 性能趋势分析SELECT TRUNC(capture_date, HH) as hour, sql_id, SUM(executions) as executions, ROUND(SUM(elapsed_time)/1000000,2) as total_elapsed_sec, ROUND(SUM(elapsed_time)/1000000/NULLIF(SUM(executions),0),4) as avg_elapsed_sec, MAX(SUBSTR(sql_text,1,100)) as sql_text_sample FROM sql_performance_history WHERE capture_date SYSDATE - 7 GROUP BY TRUNC(capture_date, HH), sql_id HAVING SUM(executions) 100 ORDER BY hour, total_elapsed_sec DESC;13. 资源消耗分析与优化13.1 识别高资源消耗模式SELECT SUBSTR(sql_text,1,60) as sql_part, COUNT(*) as sql_count, SUM(executions) as total_executions, ROUND(SUM(elapsed_time)/1000000,2) as total_elapsed_sec, ROUND(SUM(buffer_gets)/NULLIF(SUM(executions),0)) as avg_buffer_gets, ROUND(SUM(disk_reads)/NULLIF(SUM(executions),0)) as avg_disk_reads FROM ( SELECT sql_id, executions, elapsed_time, buffer_gets, disk_reads, (SELECT sql_text FROM v$sql s WHERE s.sql_id a.sql_id AND ROWNUM 1) as sql_text FROM v$sqlstats a WHERE executions 0 ) GROUP BY SUBSTR(sql_text,1,60) HAVING SUM(elapsed_time)/1000000 10 -- 总耗时超过10秒 ORDER BY total_elapsed_sec DESC;13.2 I/O密集型SQL分析SELECT sql_id, ROUND(disk_reads/NULLIF(executions,0)) as reads_per_exec, ROUND(buffer_gets/NULLIF(executions,0)) as gets_per_exec, ROUND(rows_processed/NULLIF(executions,0)) as rows_per_exec, SUBSTR(sql_text,1,100) as sql_text FROM ( SELECT s.sql_id, s.executions, s.disk_reads, s.buffer_gets, s.rows_processed, t.sql_text FROM v$sqlstats s JOIN v$sqltext_with_newlines t ON s.sql_id t.sql_id WHERE t.piece 0 AND s.executions 0 ) WHERE disk_reads/NULLIF(executions,0) 1000 -- 每次执行超过1000次物理读 ORDER BY reads_per_exec DESC;13.3 CPU密集型SQL分析SELECT sql_id, ROUND(cpu_time/NULLIF(executions,0)/1000) as cpu_ms_per_exec, ROUND(elapsed_time/NULLIF(executions,0)/1000) as elapsed_ms_per_exec, SUBSTR(sql_text,1,100) as sql_text FROM ( SELECT s.sql_id, s.executions, s.cpu_time, s.elapsed_time, t.sql_text FROM v$sqlstats s JOIN v$sqltext_with_newlines t ON s.sql_id t.sql_id WHERE t.piece 0 AND s.executions 0 ) WHERE cpu_time/NULLIF(executions,0) 100000 -- 每次执行CPU时间超过100ms ORDER BY cpu_ms_per_exec DESC;14. 执行计划稳定性管理14.1 执行计划对比分析-- 获取SQL的所有子游标和执行计划 SELECT s.child_number, s.plan_hash_value, s.executions, s.buffer_gets, s.disk_reads, s.elapsed_time/1000000 as elapsed_sec, s.cpu_time/1000000 as cpu_sec FROM v$sql s WHERE s.sql_id sql_id ORDER BY s.child_number; -- 比较两个子游标的执行计划差异 SELECT * FROM TABLE(DBMS_XPLAN.compare_cursors( sql_id sql_id, child_number1 0, child_number2 1, type TEXT));14.2 执行计划历史分析-- 查询AWR中的执行计划历史 SELECT snap_id, plan_hash_value, executions_delta as execs, ROUND(elapsed_time_delta/1000000/NULLIF(executions_delta,0),4) as avg_sec_per_exec, ROUND(buffer_gets_delta/NULLIF(executions_delta,0)) as avg_buffer_gets, ROUND(disk_reads_delta/NULLIF(executions_delta,0)) as avg_disk_reads FROM dba_hist_sqlstat WHERE sql_id sql_id AND executions_delta 0 ORDER BY snap_id;14.3 强制使用特定执行计划-- 创建SQL Profile固定执行计划 DECLARE l_profile_name VARCHAR2(30); BEGIN l_profile_name : DBMS_SQLTUNE.accept_sql_profile( task_name task_name, name PROFILE_sql_id, description Profile for SQL_ID sql_id, category DEFAULT, force_match TRUE); END; / -- 验证SQL Profile SELECT name, category, status, sql_text FROM dba_sql_profiles WHERE sql_text LIKE %sql_text%;15. 自动化监控与告警15.1 创建阈值告警BEGIN DBMS_SERVER_ALERT.set_threshold( metrics_id DBMS_SERVER_ALERT.elapsed_time_per_call, warning_operator DBMS_SERVER_ALERT.operator_ge, warning_value 0.5, -- 0.5秒 critical_operator DBMS_SERVER_ALERT.operator_ge, critical_value 1, -- 1秒 observation_period 5, -- 5分钟 consecutive_occurrences 3, -- 连续3次 instance_name orcl, object_type DBMS_SERVER_ALERT.object_type_service, object_name SYS$USERS); END; / -- 查看当前告警设置 SELECT metrics_name, warning_value, critical_value FROM dba_thresholds;15.2 使用DBMS_SCHEDULER创建监控作业BEGIN DBMS_SCHEDULER.create_job( job_name MONITOR_HIGH_LOAD_SQL, job_type PLSQL_BLOCK, job_action BEGIN INSERT INTO sql_performance_history SELECT SYSDATE, sql_id, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, (SELECT sql_fulltext FROM v$sql s WHERE s.sql_id a.sql_id AND ROWNUM 1) FROM v$sqlstats a WHERE executions 0 AND last_active_time SYSDATE - 5/1440; -- 最近5分钟 COMMIT; END;, start_date SYSTIMESTAMP, repeat_interval FREQMINUTELY;INTERVAL5, enabled TRUE, comments Job to monitor high load SQL every 5 minutes); END; / -- 查看作业运行情况 SELECT job_name, enabled, last_start_date, next_run_date FROM user_scheduler_jobs;16. 实战案例解析16.1 案例一突然性能下降现象某关键报表SQL平时运行很快突然变慢。排查步骤确认SQL_IDSELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE %报表关键SQL%;检查执行计划变化SELECT snap_id, plan_hash_value, executions_delta, elapsed_time_delta/1000000 as elapsed_sec FROM dba_hist_sqlstat WHERE sql_id sql_id ORDER BY snap_id DESC;发现执行计划改变后固定原计划-- 从AWR加载好的执行计划到SPM DECLARE l_plans PLS_INTEGER; BEGIN l_plans : DBMS_SPM.load_plans_from_awr( begin_snap good_snap_id, end_snap good_snap_id 1, basic_filter sql_id sql_id); END; /16.2 案例二数据库整体变慢现象数据库响应变慢AWR显示SQL ordered by Elapsed Time中有大量相似SQL。排查步骤识别问题SQL模式SELECT SUBSTR(sql_text,1,60), COUNT(*), SUM(elapsed_time)/1000000 FROM v$sqlstats GROUP BY SUBSTR(sql_text,1,60) ORDER BY 3 DESC;发现是缺少绑定变量导致硬解析过多SELECT force_matching_signature, COUNT(*) FROM v$sql GROUP BY force_matching_signature HAVING COUNT(*) 20 ORDER BY 2 DESC;解决方案修改应用使用绑定变量临时设置cursor_sharingFORCE16.3 案例三特定时段锁等待现象每天上午10点出现锁等待会话堆积。排查步骤创建ASH快照EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot(ALL);分析问题时段ASH数据SELECT sample_time, event, COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN TO_DATE(2023-01-01 09:50,YYYY-MM-DD HH24:MI) AND TO_DATE(2023-01-01 10:10,YYYY-MM-DD HH24:MI) GROUP BY sample_time, event ORDER BY sample_time, COUNT(*) DESC;发现是某个批处理作业导致调整作业调度时间优化批处理SQL17. 工具与脚本推荐17.1 SQLT (SQLT诊断工具)SQLT是Oracle提供的强大诊断工具可以深入分析SQL性能问题。使用步骤从Oracle Support下载sqlt.zip解压并运行安装脚本针对问题SQL运行sqlplus / as sysdba sqlt/utl/sqlhc.sql TARGET sql_idsql_id17.2 OraPlex工具集OraPlex提供了一系列实用的SQL脚本ashtop.sql - 查询ASH中的TOP事件sqlstat.sql - SQL执行统计信息planx.sql - 执行计划对比工具17.3 自定义监控脚本建议DBA团队建立自己的脚本库包含实时监控脚本历史分析脚本性能比对脚本自动化报告脚本18. 性能数据可视化18.1 使用Oracle SQL DeveloperOracle SQL Developer提供了可视化工具分析SQL性能连接数据库导航到Tools - Monitor SQL可以查看实时SQL执行情况生成执行计划和统计信息图表18.2 使用Grafana监控搭建Grafana监控平台展示Oracle性能数据使用Prometheus或Telegraf采集Oracle指标配置Grafana数据源创建仪表盘展示SQL执行时间趋势资源消耗TOP SQL执行计划变化监控18.3 自定义报表使用SQL*Plus生成HTML报表SET MARKUP HTML ON SPOOL sql_report.html SELECT sql_id, executions, ROUND(elapsed_time/1000000,2) as elapsed_sec, ROUND(buffer_gets/NULLIF(executions,0)) as avg_buffer_gets, SUBSTR(sql_text,1,100) as sql_text FROM v$sqlstats WHERE executions 0 ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY; SPOOL OFF19. 环境配置建议19.1 初始化参数优化-- 共享池大小 ALTER SYSTEM SET shared_pool_size2G SCOPEBOTH; -- 游标共享 ALTER SYSTEM SET cursor_sharingEXACT SCOPESPFILE; -- AWR设置 EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( interval 30, retention 43200);19.2 统计信息收集策略-- 自动统计信息收集 BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name auto optimizer stats collection, operation NULL, window_name NULL); END; / -- 对象级统计信息设置 EXEC DBMS_STATS.set_table_prefs(SCOTT,EMP,ESTIMATE_PERCENT,DBMS_STATS.AUTO_SAMPLE_SIZE);19.3 资源管理器配置对于混合负载环境使用Resource Manager限制报告类SQLBEGIN DBMS_RESOURCE_MANAGER.create_pending_area(); DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group REPORT_GROUP, comment Group for reporting queries); DBMS_RESOURCE_MANAGER.create_plan( plan DAYTIME_PLAN, comment Plan for daytime operations); DBMS_RESOURCE_MANAGER.create_plan_directive( plan DAYTIME_PLAN, group_or_subplan REPORT_GROUP, comment Limit reporting queries, mgmt_p1 20, parallel_degree_limit_p1 4); DBMS_RESOURCE_MANAGER.submit_pending_area(); END; /20. 总结与持续优化在实际工作中我发现建立一个完整的SQL性能监控体系非常重要。以下是我总结的几个关键点建立基线记录正常情况下的SQL性能指标便于对比分析自动化监控设置定期作业捕获性能数据避免问题发生后无从排查分层分析实时视图用于紧急问题排查AWR用于趋势分析SQL Tuning工具用于深度优化知识积累建立团队知识库记录常见问题和解法最后分享一个实用技巧对于复杂的性能问题可以同时收集以下数据做关联分析AWR报告ASH数据SQL Monitor报告10046 trace系统级别监控数据CPU、I/O等
阅读完成 · 觉得有帮助？
