oracle：一次sql阻塞的排查经历

--堵塞排查方法

--1. explain plan for + sql：根据SQL语句，解析时，找出它的 sal_PLAN_HASH_VALUE
explain plan for UPDATE T_PM_User SET FCONTROLUNITID='00000000-0000-0000-0000-000000000000CCE7AED4'  where FID='Y7BKuFR2QFC0vfUT+GPFeRO33n8='
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--2.根据 PLAN_HASH_VALUE，找出可能的SQL_ID

select sql_id from dba_hist_active_sess_history where sql_PLAN_HASH_VALUE  = '1397262751'  group by sql_id

--3.根据SQL_ID，找出对应的SQL，对比后确定，被堵塞的SQL语句的SQL_ID='一个截图，根据SQL语句，解析时，找出它的 PLAN_HASH_VALUE，

select SQL_TEXT,SQL_FULLTEXT,SQL_ID,LOADS,FIRST_LOAD_TIME,PLSQL_EXEC_TIME,JAVA_EXEC_TIME,ROWS_PROCESSED,COMMAND_TYPE,PARSING_USER_ID,PARSING_SCHEMA_ID,PARSING_SCHEMA_NAME,KEPT_VERSIONS,ADDRESS,SERVICE,MODULE,MODULE_HASH,ACTION,CPU_TIME,ELAPSED_TIME,OUTLINE_SID,CHILD_ADDRESS,SQLTYPE,REMOTE,OBJECT_STATUS,LITERAL_HASH_VALUE,LAST_LOAD_TIME,PROGRAM_ID,PROGRAM_LINE#,LAST_ACTIVE_TIME
    from  v$sql where sql_id='1u63rjrnhn2vn'

--4.根据 SQL_ID，找出堵塞该语句的进程，是节点1实例，SID=216的进程，以及节点2实例，SID=387的进程

select
       instance_number, BLOCKING_SESSION
from dba_hist_active_sess_history
where sql_id='bdn99h44jtpvy' and BLOCKING_SESSION
is not null group by instance_number, BLOCKING_SESSION;

--5.找出语句被堵塞的时间段

select MAX(SAMPLE_TIME), MIN(SAMPLE_TIME), EVENT, BLOCKING_SESSION,INSTANCE_NUMBER
from dba_hist_active_sess_history where sql_id='bdn99h44jtpvy' and BLOCKING_SESSION is not null
GROUP BY EVENT, BLOCKING_SESSION,INSTANCE_NUMBER

--分别查询这个时间段内，进程387及进程216执行过的SQL语句，但查询结果为空

SELECT * FROM dba_hist_active_sess_history where SESSION_ID=387 AND INSTANCE_NUMBER=2
AND SAMPLE_TIME BETWEEN TO_DATE('2020-11-26 15:13','yyyy-mm-dd hh24:mi')
AND TO_DATE('2020-11-26 16:13','yyyy-mm-dd hh24:mi')
--
SELECT * FROM dba_hist_active_sess_history where SESSION_ID=216 AND INSTANCE_NUMBER=1
AND SAMPLE_TIME BETWEEN TO_DATE('2020-11-26 16:04','yyyy-mm-dd hh24:mi')
AND TO_DATE('2020-11-26 16:12','yyyy-mm-dd hh24:mi')

--
select MAX(SAMPLE_TIME), MIN(SAMPLE_TIME),SESSION_SERIAL#, PROGRAM, MODULE
from dba_hist_active_sess_history where SESSION_ID=387 AND INSTANCE_NUMBER=2
GROUP BY PROGRAM, MODULE,SESSION_SERIAL#;
--
select sql_text from
(select sql_id from dba_hist_active_sess_history where session_id=387 and instance_number=2
group by sql_id) a , dba_hist_sqltext b where a.sql_id = b.sql_id
and UPPER(sql_text) not like 'SELECT%';

select sql_text from
(select sql_id from dba_hist_active_sess_history where session_id=216 and instance_number=1
group by sql_id) a , dba_hist_sqltext b where a.sql_id = b.sql_id
and UPPER(sql_text) not like 'SELECT%';

select * from dba_hist_active_sess_history where session_id=216 and instance_number=1

select INSTANCE_NUMBER, SESSION_ID,SESSION_SERIAL# ,EVENT, PROGRAM, MODULE,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#
from dba_hist_active_sess_history where sql_id='bdn99h44jtpvy' and BLOCKING_SESSION
is not null GROUP BY INSTANCE_NUMBER, SESSION_ID,SESSION_SERIAL#, EVENT, PROGRAM, MODULE,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL# ORDER BY 1,2

select sql_id from dba_hist_active_sess_history where SESSION_ID ='387' and  SESSION_SERIAL# = '54861'

set line 999;
col name for a30;
col session_id for 9999;
select * from dba_dml_locks;
select * from dba_lock;
select * from dba_lock_internal where  SESSION_ID ='387' ;