Oracle生产库故障排查小技巧

近期客户应用经常在高峰期无征兆卡顿很久，只能是重启应用服务才能恢复。

经过用以下语句查看问题：

select v.sql_text,v.first_load_time,v.LAST_LOAD_TIME,v.LAST_ACTIVE_TIME,v.MODULE,v.PARSING_SCHEMA_NAME from v$sql v where first_load_time >'2026-06-02/09:20:12'

----详细情况分析语句

select snap_id, dbid, instance_number,

to_char(begin_interval_time,'YYYY-MM-DD HH24:MI:SS') as 开始时间,

to_char(end_interval_time,'YYYY-MM-DD HH24:MI:SS') as 结束时间

from dba_hist_snapshot

where begin_interval_time >= to_date('2026-05-24 09:00:00','yyyy-mm-dd hh24:mi:ss')

and end_interval_time <= to_date('2026-05-24 12:00:00','yyyy-mm-dd hh24:mi:ss')

order by snap_id;*/

---@?/rdbms/admin/awrrpt.sql

/*

SELECT b.sid oracleID,

b.username 登录用户名,

b.serial#,

spid 操作系统ID,

sql_text 正在执行的SQL,

b.machine 计算机名

FROM v$process a, v$session b, v$sqlarea c

WHERE a.addr = b.paddr

AND b.sql_hash_value = c.hash_value;

*/

select * from v$sqlarea where first_load_time >= '2025-11-24/09:45:00'

and first_load_time<= '2025-11-24/11:30:00' and executions>0

order by elapsed_time desc

最终通过时长比较，模拟环境执行等排查出相关问题，进行了优化。