定位 Oracle GoldenGate 进程中的长事务

1. 查看进程的长事务,得到XID

GGSCI (rhelv1.localdomain) 72> send extract EG1_G21A,showtrans
Sending SHOWTRANS request to EXTRACT EG1_G21A ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 14, RBA 4447248
------------------------------------------------------------
XID:                  5.0.780
Items:                1
Extract:              EG1_G21A
Redo Thread:          1
Start Time:           2017-07-02:10:09:13
SCN:                  0.965762 (965762)
Redo Seq:             14
Redo RBA:             4447248
Status:               Running

此处得到的 XID 为 5.0.780 -> 5

2. 根据 XID 查找正在进行的事务,定位SQLID

select * from gv$transaction where xidusn=5;
select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='50E8F800';

此处得到的 sql_id 为:fbwuc1mnwch12

3. 根据 SQLID 定位具体的 SQL 内容

select hash_value, address,executions,buffer_gets, disk_reads,round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
last_load_time,module,sql_fulltext from v$sqlarea where sql_id='&sql_id';

共有 0 条评论

Leave a Reply

Your email address will not be published. Required fields are marked *