oracle 通过活跃会话历史ASH排查系统运行卡顿的原因

mahan

一、 活跃会话历史（ASH）排查类
这类语句用于捕捉在案发瞬间或特定时间段内，数据库里各个会话（进程）到底在干什么。

1. 还原特定会话（如背锅侠 SID 2976）的真实行为轨迹

SQL
/* 用途：查看 SID 2976 在 10:00-10:15 期间每一秒的状态、等待事件和执行的 SQL_ID，确认它究竟是源头还是受害者。 */
SELECT TO_CHAR(sample_time, 'HH24:MI:SS') AS sample_time,
       session_id, sql_id, event, session_state, machine, program, module
FROM dba_hist_active_sess_history
WHERE session_id = 2976
  AND instance_number = 2
  AND sample_time BETWEEN TO_TIMESTAMP('2026-04-17 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
                      AND TO_TIMESTAMP('2026-04-17 10:15:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY sample_time;
2. 抓取特定精确时间点（如 09:14:49）在干活的程序

SQL
/* 用途：当我们知道统计信息是在 09:14 开始收集后，将监控探头拨到这个时间点，抓取在此期间耗费 CPU 或进行操作的机器名和程序名。 */
SELECT TO_CHAR(sample_time, 'HH24:MI:SS') AS sample_time,
       session_id, session_state, event, machine, program, module, sql_id
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2026-04-17 09:14:40', 'YYYY-MM-DD HH24:MI:SS')
                      AND TO_TIMESTAMP('2026-04-17 09:15:10', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY sample_time;
3. 直接通过 SQL 文本关键字在 ASH 中“大海捞针”

SQL
/* 用途：不靠时间点，直接去历史记录中捞取包含 "GATHER" 或 "DBMS_STATS" 关键字的 SQL 语句，并显示执行它的机器和程序（专门对付隐蔽程序）。 */
SELECT TO_CHAR(h.sample_time, 'HH24:MI:SS') AS sample_time,
       h.session_id, h.machine, h.program, h.module,
       dbms_lob.substr(s.sql_text, 100, 1) AS sql_text_snippet
FROM dba_hist_active_sess_history h
JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id
WHERE h.sample_time BETWEEN TO_TIMESTAMP('2026-04-17 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
                        AND TO_TIMESTAMP('2026-04-17 10:20:00', 'YYYY-MM-DD HH24:MI:SS')
  AND (UPPER(s.sql_text) LIKE '%DBMS_STATS%' OR UPPER(s.sql_text) LIKE '%GATHER_TABLE_STATS%')
ORDER BY h.sample_time;
4. 获取特定 SQL_ID 的完整 SQL 文本

SQL
/* 用途：已知 SQL_ID（如 75u6fnxvhxc1n），查询其背后的真实 SQL 代码。在内存 (v$sqlarea) 或历史 (dba_hist_sqltext) 中查询。 */
SELECT sql_text FROM v$sqlarea WHERE sql_id = '75u6fnxvhxc1n';
-- 或者
SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = '75u6fnxvhxc1n';
二、 数据库内部审计与操作记录类
这类语句用于直接查看 Oracle 底层的“硬性操作日记”。

5. 查底层的统计信息收集审计日志（极其重要）

SQL
/* 用途：无视应用层伪装，直接查出 Oracle 内部在哪个确切时间段，对哪些确切的表（Target）执行了统计信息收集操作。 */
SELECT operation, target,
       to_char(start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time,
       to_char(end_time, 'YYYY-MM-DD HH24:MI:SS') as end_time
FROM dba_optstat_operations
WHERE start_time BETWEEN TO_TIMESTAMP('2026-04-17 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
                     AND TO_TIMESTAMP('2026-04-17 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY start_time;
6. 查数据库登录审计日志

SQL
/* 用途：查出在指定时间内是谁（机器 IP/主机名）发起了数据库新登录。注意：如果是长连接池，这里查不到。 */
SELECT username, userhost, terminal,
       to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') as logon_time
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND timestamp BETWEEN TO_DATE('2026-04-17 09:14:00', 'YYYY-MM-DD HH24:MI:SS')
                    AND TO_DATE('2026-04-17 09:15:30', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY timestamp;
三、 数据库对象变更分析类
这类语句用于确认特定时间内，哪些物理对象的属性发生了变化（游标失效的直接证据）。

7. 精确查找被收集统计信息的表和索引

SQL
/* 用途：查出在 10:00-10:05 这致命的 5 分钟内，哪些表的 LAST_ANALYZED 时间被更新了（直接锁定引发血案的“病案主页”表）。 */
SELECT owner, table_name as object_name, 'TABLE' as type, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as time
FROM dba_tables
WHERE owner = 'ZLHIS'
  AND last_analyzed BETWEEN TO_DATE('2026-04-17 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2026-04-17 10:05:00', 'YYYY-MM-DD HH24:MI:SS')
UNION ALL
SELECT owner, index_name as object_name, 'INDEX' as type, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as time
FROM dba_indexes
WHERE owner = 'ZLHIS'
  AND last_analyzed BETWEEN TO_DATE('2026-04-17 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2026-04-17 10:05:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY time;
四、 代码追溯与定时任务排查类（擒王之剑）
这类语句用于排查系统内埋藏的炸弹（存储过程或定时任务）。

8. 在所有存储过程源码中搜索特定代码（本次破案关键）

SQL
/* 用途：全局扫描非系统用户的代码库，找出到底是哪个包或存储过程把 dbms_stats 偷偷写在了里面。直接揪出了 ZL100_USERJOB收集统计信息。 */
SELECT owner, name AS procedure_name, type, line, text
FROM dba_source
WHERE owner NOT IN ('SYS', 'SYSTEM', 'ORACLE_OCM', 'DBSNMP', 'SYSMAN')
  AND (UPPER(text) LIKE '%DBMS_STATS%' OR UPPER(text) LIKE '%GATHER_TABLE_STATS%')
ORDER BY owner, name, line;
9. 查新式 Scheduler 调度任务

SQL
/* 用途：排查 Oracle 11g 之后的 DBMS_SCHEDULER 任务列表，结合模糊匹配查找嫌疑任务。 */
SELECT owner, job_name, state,
       to_char(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_run,
       repeat_interval, job_action
FROM dba_scheduler_jobs
WHERE lower(job_action) LIKE '%stats%' OR lower(job_action) LIKE '%gather%';
10. 精准查询特定命名的老式 JOB 定时任务（最终铁证）

SQL
/* 用途：排查传统的 DBMS_JOB 队列，查询包含 ZL100_USERJOB 的定时任务。确认了其下次执行时间为 4月17日 04:00:00。 */
SELECT job, log_user,
       to_char(last_date, 'YYYY-MM-DD HH24:MI:SS') as last_run,
       to_char(next_date, 'YYYY-MM-DD HH24:MI:SS') as next_run,
       interval, what
FROM dba_jobs
WHERE upper(what) LIKE '%ZL100_USERJOB%';
五、 应急处置类
11. 停用违规的 Oracle JOB

SQL
/* 用途：直接让指定的后台任务处于 Broken（挂起/停用）状态，防止其下次继续作恶。 */
BEGIN
  DBMS_JOB.BROKEN(37541666, TRUE);
  COMMIT;
END;
/