oracle 中慢sql定位

一、实时监控利器

1. 动态性能视图查询

SELECT sql_id, 
       sql_text,
       executions,
       elapsed_time/1000000 as elapsed_sec,
       cpu_time/1000000 as cpu_sec,
       disk_reads,
       buffer_gets
FROM v$sql
WHERE elapsed_time/1000000 > 5  -- 筛选执行超过5秒的SQL
ORDER BY elapsed_time DESC;
Copy

1. 实时会话监控（每秒刷新）

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       q.sql_text,
       q.optimizer_cost
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'
AND s.type <> 'BACKGROUND';
Copy

二、深度分析工具

1. AWR报告生成步骤：

# 生成快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

# 生成报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Copy

关键分析指标：

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets

1. ASH实时分析：

SELECT sample_time,
       session_id,
       sql_id,
       event,
       blocking_session
FROM v$active_session_history
WHERE sql_id IS NOT NULL
ORDER BY sample_time DESC;
Copy

三、SQL Trace高级技巧

1. 开启10046跟踪：

ALTER SESSION SET tracefile_identifier = 'slow_sql_trace';
ALTER SESSION SET events '10046 trace name context forever, level 12';
-- 执行待跟踪SQL
EXIT;
Copy

1. TKPROF解析：

tkprof ora_12345.trc output.txt sys=no aggregate=yes sort=prsela,exeela,fchela
Copy

四、执行计划分析

EXPLAIN PLAN FOR
SELECT /*+ YOUR_HINT */ ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Copy

五、自动诊断工具

1. SQL调优顾问：

DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'g4yfu8q89kj7d',
    scope => 'COMPREHENSIVE',
    time_limit => 3600);
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;
/
Copy

六、性能视图黄金组合

SELECT * FROM (
  SELECT sql_id,
         ROUND(elapsed_time/1e6) elapsed_sec,
         executions,
         ROUND(elapsed_time/decode(executions,0,1,executions)/1e6,4) per_exec_sec,
         sql_text
  FROM v$sqlstats
  ORDER BY elapsed_time DESC)
WHERE ROWNUM <= 10;
Copy

注意事项：

1. 诊断包授权：AWR/ASH需要Diagnostics and Tuning Pack许可
2. 执行计划稳定性：注意SQL Profile和Baseline的影响
3. 统计信息时效性：确保统计信息最新
4. 绑定变量窥视：可能影响执行计划选择

通过以上方法的组合使用，可以快速定位TOP SQL，结合执行计划分析和SQL调优技术，能有效解决90%以上的数据库性能问题。建议建立定期性能分析机制，对历史SQL进行趋势分析，实现预防性优化。