oracle定位造成卡顿的SQL语句
先查询阻塞的会话号
select event,machine,sql_id,program,blocking_session from dba_hist_active_sess_history where SAMPLE_TIME between TO_TIMESTAMP ('2021-08-25 15:25:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2021-08-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS') and wait_class<>'Idle' and BLOCKING_SESSION is not null;
然后根据这个SESSION_ID查询sql_ID或者下一层阻塞的回话
select event,machine,sql_id,program,blocking_session from dba_hist_active_sess_history where SAMPLE_TIME between TO_TIMESTAMP ('2021-08-25 15:25:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2021-08-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS') and SESSION_ID='3278';
例如上图，如果有sql_id的话，查询一下sql_text;如果还有一层BLOCKING_SESSION的话，继续往下查询
然后通过sql_id查询SQL 的文本
select sql_fulltext,sql_id from v$sql where sql_id='6ky0jxgugphax';
