GGSCI (ewmdb1) 37> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_ICM 24:56:17 00:00:08
GGSCI (ewmdb1) 49> !
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_ICM 25:11:33 00:00:02
1，查看ggserr.log
2，查看dirrpt下面该进程的dsc文件
3，info 进程名多次，看看RBA是否有变化
经过以上几步rps1进程状态是正常的，延迟高问题出在哪里？view params REP_ICM 看到该进程只有一张表。那我们看看这个进程到底卡在哪里了
[oracle@ewmdb1 ogg]$ ps -ef |grep REP_ICM |grep -v grep
oracle 147832 115289 0 15:03 ? 00:00:01 /home/oracle/ogg/replicat PARAMFILE /home/oracle/ogg/dirprm/rep_icm.prm REPORTFILE /home/oracle/ogg/dirrpt/REP_ICM.rpt PROCESSID REP_ICM USESUBDIRS
[oracle@ewmdb1 ogg]$ ps -ef |grep 147832 |grep -v grep
oracle 147832 115289 0 15:03 ? 00:00:01 /home/oracle/ogg/replicat PARAMFILE /home/oracle/ogg/dirprm/rep_icm.prm REPORTFILE /home/oracle/ogg/dirrpt/REP_ICM.rpt PROCESSID REP_ICM USESUBDIRS
oracle 147838 147832 99 15:03 ? 00:25:26 oracleewmdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
select s.sid,s.SERIAL#,sql_id from v$process p,v$session s where p.addr=s.paddr and p.spid=147838;
-----分析统计信息
select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T_ICM_ORCODE_TRADE_LOG';
select table_name,num_rows from dba_tables where owner='ICM' and table_name='T_ICM_ORCODE_TRADE_LOG';
--发现统计信息过久
exec dbms_stats.gather_table_stats(ownname => 'ICM',tabname => 'T_ICM_ORCODE_TRADE_LOG',estimate_percent => 30,method_opt=> 'for all indexed columns',degree=>8,cascade=>TRUE);
15:33:55 SYS@ewmdb1(ewmdb1)> exec dbms_stats.gather_table_stats(ownname => 'ICM',tabname => 'T_ICM_ORCODE_TRADE_LOG',estimate_percent => 30,method_opt=> 'for all indexed columns',degree=>8,cascade=>TRUE);
PL/SQL procedure successfully completed.
15:39:46 SYS@ewmdb1(ewmdb1)>
接下来我们看看该sql执行计划是不是走错了？
Plan hash value: 2729232882
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| | |
| 1 | UPDATE | T_ICM_ORCODE_TRADE_LOG | | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 684 | 0 (0)| KEY | KEY |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| T_ICM_ORCODE_TRADE_LOG | 1 | 684 | 0 (0)| KEY | KEY |
|* 4 | INDEX RANGE SCAN | UN_ICM_ORCODE_TRADE_LOG_MNO | 1 | | 0 (0)| KEY | KEY |
------------------------------------------------------------------------------------------------------------------------
---生成 sql 对应的 HTML 报告
17:23:47 SYS@ewmdb1(ewmdb1)> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'UPDATE "ICM"."T_ICM_ORCODE_TRADE_LOG" x SET x."AMT"%';
ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000001DAFAE0DC0 3216245465 1 1
0000001DDEF43750 3128034321 35 1
0000001D7B6AF3C8 3368027201 1 1
0000001D7B700688 768214354 5 1
0000001DDEEB30A0 3923510751 9 1
0000001D9B11EE60 2213416555 3 1
0000001DDF032890 1636831117 1 1
0000001DAFADDFC8 999165987 6351 1
0000001DCF65ECB0 2989757191 61 1
0000001D9B04EEE8 653136874 138 1
0000001D9B08A040 3817871791 2 1
0000001D7B6CE0D8 3059358276 45 1
0000001D9B068AB0 350887397 1075 1
0000001DDEEC9D48 1344545751 4 1
0000001DCF7410E8 1097737204 4815 1
0000001D7B6CFBB8 2201756819 36 1
0000001DAFB1B850 2584225353 8 1
0000001DAFB04E18 346304475 4886 1
0000001DCF604CF8 2353804982 70 1
0000001DAFB34C88 585644747 21 1
0000001D8B6E4A88 550127758 367 1
0000001DDEF82E38 1899121162 14 1
0000001DBF4C1F50 2919124308 2 1
0000001DDEEA3888 2063224832 2 1
0000001DDEEB1C20 1303269929 2 1
0000001D8B243610 532572153 19814 1
0000001DBF717DA0 1655597963 9 1
0000001DBF4C0E48 1819832215 2 1
0000001DBFA72578 3589306602 22 1
0000001D7B6CD448 1997044230 375 1
0000001DDEF24690 1942780661 1 1
0000001D7B6AC7B0 1353874794 5 1
0000001D8B6F3538 4125523431 53 1
0000001D7B6DD198 1838317746 2689 1
0000001D9B0FE920 4287663344 105 1
0000001D9B056068 2480051898 1 1
0000001D9B0F8D70 3935607984 83 1
0000001DCF5EA230 4163805110 2969 1
0000001DCF5EACF8 2370610478 7 1
0000001DDED6F4C0 424454352 1 1
0000001DCF65B020 2942741613 5 1
0000001DDEF20500 877703358 406 1
0000001DCF5BBC90 4059870012 1 1
0000001D9B05A810 2488317001 31 1
0000001D7B6AE778 773372943 2 1
0000001DBF7325A8 1483652226 149 1
alter session set events '5614566 trace name context forever';
exec dbms_shared_pool.purge('0000001DBF7325A8,1483652226','C');
exec dbms_shared_pool.purge('0000001D7B6AE778,773372943','C');
----查询 延迟
GGSCI (ewmdb1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_ICM 00:00:00 00:00:02
---新的执行计划
Plan Hash Value : 1629545242
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 296 | 3 | 00:00:01 |
| 1 | UPDATE | T_ICM_ORCODE_TRADE_LOG | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 296 | 2 | 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | T_ICM_ORCODE_TRADE_LOG_NEW_PK | 1 | 296 | 2 | 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("X"."UUID"=:B0 AND "X"."CRE_DT"=:B1)
日积月累
