Oracle数据库慢查询排查及处理

Oracle视图要记牢：核心视图v$session（查会话）、v$sql（查慢查询）、v$sqlarea（查历史SQL）

1、紧急止损，恢复系统可用

查询慢查询会话（重点筛选耗时超10s、等待事件异常的会话）：

-- last_call_et单位：秒，sid、serial#为会话唯一标识，event为等待事件
select sid,serial#,username,sql_id,event,last_call_et from v$session where status='ACTIVE' and last_call_et>10 and username is not null; 

终止慢查询会话（单个会话）：

-- immediate表示强制终止，避免会话残留
alter system kill session 'sid,serial#' immediate;

批量终止慢查询会话（多个会话，避免手动逐个终止），先查询生成终止命令，再执行：

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='ACTIVE' and last_call_et>10 and username='业务用户名'; 

2、定位根因

止损后，重点定位导致卡顿的核心慢查询语句，Oracle需借助v$sql、v$sqlarea等专属视图

查询耗时久、执行频率高的慢查询语句：

--elapsed_time单位：微秒，换算为秒需除以1000000，筛选耗时超2s的查询
select sql_id,sql_text,elapsed_time/1000000 as elapsed_time_s,executions from v$sql where elapsed_time/1000000>2 order by elapsed_time desc;

若需查看慢查询历史记录，可查询v$sqlarea视图，命令：

select sql_text,elapsed_time/1000000 as elapsed_time_s from v$sqlarea where elapsed_time/1000000>2; 

3、分析慢查询原因（Oracle执行计划分析）

通过sql_id获取执行计划（更精准，推荐）：

-- 从共享池中获取
select * from table(dbms_xplan.display_cursor('sql_id')); 
-- 从awr中获取
select * from table(dbms_xplan.display_awr('sql_id'));

分析执行计划核心要点：

– Operation字段：出现“TABLE ACCESS FULL”，表示全表扫描，表数据量较大时，全表扫描耗时极长；

– Index字段：为“NULL”，表示未使用任何索引，查询时需遍历整个表，磁盘IO压力大（对应第一步的“db file sequential read”等待事件）；

– Rows字段：表示查询扫描了多少条数据；

4、最后

根据执行计划优化相应的SQL语句。