OGG入门--产生lag超时，查询历史超长事务的方法

最新推荐文章于 2025-02-07 09:13:41 发布

转载最新推荐文章于 2025-02-07 09:13:41 发布 · 508 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dayu-liu/p/9562198.html

文章标签：

#数据库

1.通过查询gv$session查询当前OGG在数据库中的sid及serial#。

SQL> select inst_id,sid,serial#,sql_id,program,module  from gv$session where module like '%OGG%';

   INST_ID        SID    SERIAL# SQL_ID        PROGRAM                        MODULE
---------- ---------- ---------- ------------- ------------------------------ ------------------------------
         1       2818      10197               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD2-OPEN_DATA_SOURCE
         1       2857      13093               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD2-OCI_META_THREAD
         1       2878          3               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD-OCI_META_THREAD
         1       2899         69               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD-OCI_DBLOGREADER
         1       2920      26055               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD-OCI_META_THREAD
         1       2942       3231               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD-OCI_DBLOGREADER
         2        465      42317               replicat@pxpaddb1 (TNS V1-V3)  OGG-REPRPT-OPEN_DATA_SOURCE    --replicate对应的为事务应用进程
         2        786      29465               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD-OPEN_DATA_SOURCE
         2        856      47529 0kugqg48477gf extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD2-OCI_DBLOGREADER
         2        914      48783               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD2-OCI_META_THREAD
         2       1113      43309               extract@pxpaddb1 (TNS V1-V3)   OGG-EXTXPAD2-OCI_DBLOGREADER

2.通过上述查询出来的sid，serial#查询此会话执行执行的历史事务。

select xid,count(*)                     --XID为事务ID
from gv$active_session_history
where session_id=465
and sample_time > to_date('20180828 04:00','yyyymmdd hh24:mi')
and sample_time < to_date('20180828 09:00','yyyymmdd hh24:mi')
and session_serial#=42317
group by xid
order by count(*);


002000120026D456         36
008C00040019C462         36
00180019003AF275         37
004B00130029E9C2         38
001F0007002A2346         38
007700040035CF96         41
002600010047DB1B         42
004500210035705B         43
008A000A001730EA         49
004A000B0023329A         51
0019000F004BFB75         57

XID                COUNT(*)
---------------- ----------
0076000F00260115         57
002E00110043BB26         75
0067000D002BD6A2         98
005500080072B1A2        104
008E000900162242        122
007E00150037C1CC        239
007600040025FFF4        849
0055001C0072A286       1183
001C0002002E48C8       1591
0051001A002F6B7D      15563

3.通过gv$active_session_history查询执行时间最长的事务sql_id。

select sql_id,count(*)
from gv$active_session_history
where session_id=465
and session_serial#=42317
and xid='0051001A002F6B7D';

***
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9
bkxp4hkg9zmt9

4.通过以上sql_id即可定位到目标端产生lag是由哪条sql引起的。此外需要说明的是，在目标端的sql并不一定和源端的sql为同一条sql。

UPDATE "XPAD"."RPT_XPAD_MONETARYFUNDPROFIT" x SET x."ISSUEID" =
:a10,x."ACCOUNT" = :a11,x."CUSTACCOUNT" = :a12,x."BILLUNIT" =
:a13,x."CASHUNIT" = :a14,x."CALDATE" = :a15,x."TENSINCOME" =
:a16,x."INCOME" = :a17,x."ACCINCOME" = :a18,x."DAYS" = :a19 WHERE
x."ISSUEID" = :b0 AND x."ACCOUNT" = :b1 AND x."CUSTACCOUNT" = :b2 AND
x."BILLUNIT" = :b3 AND x."CASHUNIT" = :b4 AND x."CALDATE" = :b5 AND
x."TENSINCOME" is NULL AND x."INCOME" is NULL AND x."ACCINCOME" = :b8
AND x."DAYS" = :b9 AND ROWNUM = 1

Plan hash value: 228950999

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                             |       |       |     4 (100)|          |
|   1 |  UPDATE                       | RPT_XPAD_MONETARYFUNDPROFIT |       |       |            |          |
|   2 |   COUNT STOPKEY               |                             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| RPT_XPAD_MONETARYFUNDPROFIT |     1 |    68 |     4   (0)| 00:00:01 |
|   4 |     INDEX RANGE SCAN          | MFPROFIT_CALDATE            |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

转载于:https://www.cnblogs.com/dayu-liu/p/9562198.html