oracle commit hang 模拟

会话1正常插入
SQL> SQL> SQL> SQL> SQL>  INSERT INTO test_hang VALUES (1, 'test_hang');
1 row created.

会话2 suspend LGWR 进程，模拟异常
SQL> oradebug setospid 14068
Oracle pid: 28, Unix process pid: 14068, image: oracle@redhat76 (LGWR)
SQL> oradebug suspend;
Statement processed.

会话1提交异常
SQL> commit;




查询会话1状态
set linesize 300
col event for a30;
SELECT 
    s.sid, 
    s.serial#, s.event,status,
    p.spid,  -- 关键：操作系统进程 ID
    s.username, 
    s.status,
    s.program  -- 可通过程序名/用户名筛选目标会话
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE p.spid='18437';


SQL> set linesize 300
SQL> col event for a30;
SQL> SELECT 
  2      s.sid, 
  3      s.serial#, s.event,status,
  4      p.spid,  -- 关键：操作系统进程 ID
  5      s.username, 
  6      s.status,
  7      s.program  -- 可通过程序名/用户名筛选目标会话
  8  FROM v$session s
  9  JOIN v$process p ON s.paddr = p.addr
 10  WHERE p.spid='18437';

       SID    SERIAL# EVENT                          STATUS   SPID                     USERNAME     STATUS   PROGRAM
---------- ---------- ------------------------------ -------- ------------------------ ------------ -------- ------------------------------------------------
        83      10814 log file sync                  ACTIVE   18437                    SYS          ACTIVE   sqlplus@redhat76 (TNS V1-V3)

SQL> 


查询会话1ASH信息，有更新
SQL> select * from (
  2  select event,sql_id,count(*),max(sample_time) from v$active_session_history where session_id=83 
  3  group by event,sql_id
  4  order by 3 desc)
  5  where rownum<10;

EVENT                          SQL_ID          COUNT(*) MAX(SAMPLE_TIME)
------------------------------ ------------- ---------- ---------------------------------------------------------------------------
log file sync                                       151 24-FEB-26 02.40.06.734 PM
enq: TM - contention           10ch9s5ufat7y         76 24-FEB-26 02.13.39.019 PM
db file sequential read        03guhbfpak0w7          4 24-FEB-26 02.08.03.860 PM
db file sequential read        03jajjvddcqf9          2 24-FEB-26 02.09.51.289 PM
db file sequential read        59vjj34vugaav          1 24-FEB-26 02.09.54.313 PM
db file sequential read        2sxqgx5hx76qr          1 24-FEB-26 02.08.00.856 PM
db file sequential read        72frm9t5k2909          1 24-FEB-26 02.10.48.612 PM
db file sequential read        dqucusk8avvuh          1 24-FEB-26 02.09.43.274 PM
9 rows selected.
SQL> /
EVENT                          SQL_ID          COUNT(*) MAX(SAMPLE_TIME)
------------------------------ ------------- ---------- ---------------------------------------------------------------------------
log file sync                                       157 24-FEB-26 02.40.12.739 PM
enq: TM - contention           10ch9s5ufat7y         76 24-FEB-26 02.13.39.019 PM
db file sequential read        03guhbfpak0w7          4 24-FEB-26 02.08.03.860 PM
                               1jb0rx0tq963n          3 24-FEB-26 02.09.57.328 PM
db file sequential read        03jajjvddcqf9          2 24-FEB-26 02.09.51.289 PM
db file sequential read        59vjj34vugaav          1 24-FEB-26 02.09.54.313 PM
db file sequential read        2sxqgx5hx76qr          1 24-FEB-26 02.08.00.856 PM
db file sequential read        72frm9t5k2909          1 24-FEB-26 02.10.48.612 PM
db file sequential read        dqucusk8avvuh          1 24-FEB-26 02.09.43.274 PM
9 rows selected.
SQL>