批量修改数据导致回滚时间过长处理

oracle@xxxxx]$ sqlplus / as sysdba

出现大量enq: TX - row lock锁。

I EVENT SQL_ID HCOUNT

1 PX Deq: Execute Reply 6pm617gq9f08g 1
[CPU]:db file sequential read 1
gc cr request 7qzw2sb61fa8v 1
db file parallel read 6xawm14q5g0vp 1
[CPU]:PX Deq: Execution Msg 6pm617gq9f08g 1
SQLNet message from client 8ubjuvyg6c534 1
gc cr request bw1pwsnpmpbp7 1
SQLNet message from dblink bfa92v0yrt3vv 1
SQLNet message from dblink bmcf4n2jbm3hd 1
[CPU]:gc cr request 18tz5acv4jhg4 1
SQLNet message from dblink 5mm0gnwd5tcdw 1
2 [CPU]:SQLNet message from client c8jv7g7f1wyf8 1
enq: TX - row lock contention 2zfyru32rp5zw 1
enq: TX - allocate ITL entry fuagbwwhvs73r 1
library cache pin 1r34kjh48z6jh 1
library cache pin 448wn8r2sagr3 1
[CPU]:library cache pin 5t931a1tb3f8h 1
[CPU]:SQLNet message to client 5gt0uuscu271q 1
[CPU]:PX Deq Credit: send blkd 6pm617gq9f08g 1
[CPU]:library cache pin c3b2uy42cp6rh 1
[CPU]:SQLNet message from client 1
SQLNet message from dblink 1
library cache pin c9ww31attg5ar 1
enq: TX - row lock contention b84ubrm34zbrm 1
[CPU]:SQLNet message from client 609h92fkk680m 1
[CPU]:SQLNet message to client gast247sqsygd 1
[CPU]:SQLNet message from client 3ff19bbb2m6ts 1
db file sequential read avpkpmxcvg31b 1
log file sync 1
[CPU]:SQLNet message from client 5apag8prnq349 1
[CPU]:SQLNet message from client gpfqsu29xfkm0 1
enq: TX - row lock contention 51d785jbswc8z 2
SQLNet message from dblink 6vgum9wkmfu0j 2
enq: TX - row lock contention 4ug6303j3dy3j 6
enq: TX - row lock contention 3zrfjmj6t1ukq 7
enq: TX - row lock contention 3wtd9frcsm4xc 7
enq: TX - row lock contention 0057g52y48s1u 11
enq: TX - row lock contention b0234wtz9rz8m 15
enq: TX - row lock contention 6dcawj8hc1pau 62
enq: TX - row lock contention 13wzgxy2s1q89 83
enq: TX - row lock contention gz0utupz6q9m2 87
enq: TX - row lock contention cx2tvfzdt4bqj 177
enq: TX - row lock contention d7cwtyvw5dkkc 193
enq: TX - row lock contention 7aj84h6x4jvqm 1204

44 rows selected.

I EVENT HCOUNT

1 SQLNet message from dblink 5
[CPU]:SQLNet message from client 2
[CPU]:PX Deq: Execution Msg 1
[CPU]:db file scattered read 1
PX Deq: Execute Reply 1
[CPU]:gc cr request 1
[CPU]:library cache pin 1
[CPU]:db file sequential read 1
class slave wait 1
db file sequential read 1
2 enq: TX - row lock contention 1856
[CPU]:SQLNet message from client 4
SQLNet message from dblink 3
class slave wait 3
[CPU]:db file sequential read 1
enq: TX - allocate ITL entry 1
[CPU]:gc cr request 1
gc current request 1
[CPU]:SQL*Net message to client 1
[CPU]:library cache pin 1
[CPU]:PX Deq: Execution Msg 1

21 rows selected.

检查阻塞会话信息如下：

SQL> @sid
Enter value for sid_in: 13592
SADDR : 0000007EAC551030
SID : 13592
SERIAL# : 26283
AUDSID : 717908093
PADDR : 0000007EB2910F40
USER# : 454
USERNAME : xxxx
COMMAND : 0
OWNERID : 2147483644
TADDR : 0000007DF18743D8
LOCKWAIT :
STATUS : ACTIVE
SERVER : DEDICATED
SCHEMA# : 454
SCHEMANAME : xxxxxx
OSUSER : xxxx
PROCESS : 90724:117564
MACHINE : APP\PORTAL137
PORT : 54572
TERMINAL : PORTAL137
PROGRAM : plsqldev.exe
TYPE : USER
SQL_ADDRESS : 00
SQL_HASH_VALUE : 0
SQL_ID :
SQL_CHILD_NUMBER :
SQL_EXEC_START :
SQL_EXEC_ID :
PREV_SQL_ADDR : 000000759B3B7D68
PREV_HASH_VALUE : 2838122288
PREV_SQL_ID : 932rfs6nknmth
PREV_CHILD_NUMBER : 0
PREV_EXEC_START : 2018-02-06 10:57:00
PREV_EXEC_ID : 16777216
PLSQL_ENTRY_OBJECT_ID :
PLSQL_ENTRY_SUBPROGRAM_ID :
PLSQL_OBJECT_ID :
PLSQL_SUBPROGRAM_ID :
MODULE : PL/SQL Developer
MODULE_HASH : 1190136663
ACTION : SQL -

                                    ACTION_HASH                   : 2127054360

CLIENT_INFO :
FIXED_TABLE_SEQUENCE : 2027500411
ROW_WAIT_OBJ# : -1
ROW_WAIT_FILE# : 191
ROW_WAIT_BLOCK# : 477880
ROW_WAIT_ROW# : 0
TOP_LEVEL_CALL# : 94
LOGON_TIME : 2018-02-06 10:08:34
LAST_CALL_ET : 2589
PDML_ENABLED : NO
FAILOVER_TYPE : NONE
FAILOVER_METHOD : NONE
FAILED_OVER : NO
RESOURCE_CONSUMER_GROUP :
PDML_STATUS : DISABLED
PDDL_STATUS : ENABLED
PQ_STATUS : ENABLED
CURRENT_QUEUE_DURATION : 0
CLIENT_IDENTIFIER :
BLOCKING_SESSION_STATUS : NOT IN WAIT
BLOCKING_INSTANCE :
BLOCKING_SESSION :
FINAL_BLOCKING_SESSION_STATUS : NOT IN WAIT
FINAL_BLOCKING_INSTANCE :
FINAL_BLOCKING_SESSION :
SEQ# : 1774
EVENT# : 153
EVENT : gc current request
P1TEXT : file#
P1 : 191
P1RAW : 00000000000000BF
P2TEXT : block#
P2 : 477880
P2RAW : 0000000000074AB8
P3TEXT : id#
P3 : 33554433
P3RAW : 0000000002000001
WAIT_CLASS_ID : 3871361733
WAIT_CLASS# : 11
WAIT_CLASS : Cluster
WAIT_TIME : -1
SECONDS_IN_WAIT : 30
STATE : WAITED SHORT TIME
WAIT_TIME_MICRO : 295
TIME_REMAINING_MICRO :
TIME_SINCE_LAST_WAIT_MICRO : 29684603
SERVICE_NAME : crmbdb
SQL_TRACE : DISABLED
SQL_TRACE_WAITS : FALSE
SQL_TRACE_BINDS : FALSE
SQL_TRACE_PLAN_STATS : FIRST EXEC
SESSION_EDITION_ID : 100
CREATOR_ADDR : 0000007EB2910F40
CREATOR_SERIAL# : 181
ECID :

PL/SQL procedure successfully completed.

12:55:51 SYS@crmbdb1>select sql_text from v$sqltext where sql_id=‘932rfs6nknmth’ order by piece
12:56:52 2 ;

SQL_TEXT

update INST.OFFER_PARAM e set e.value = (select d.现参数 from jh
2 d where e.offer_id =d.offer_id and e.offer_spec_param_id =‘109
010000307’ and e.status_cd =‘12’)

Elapsed: 00:00:00.00
12:56:53 SYS@crmbdb1>@querywait

/*
update INST.OFFER_PARAM e set e.value = (select d.现参数 from jh
2 d where e.offer_id =d.offer_id and e.offer_spec_param_id =‘109
010000307’ and e.status_cd =‘12’)

update INST.OFFER_PARAM e set e.value = (select d.rom jh2 d where
e.offer_id =d.offer_id and e.offer_spec_param_id =‘109010000307’ and
e.status_cd =‘12’)
where e.offer_id in (select offer_id from jh2 d ) and e.offer_spec_param_id =‘109
010000307’ and e.status_cd =‘12’
*/

处理步骤：
1、查询预计回滚结束时间
Alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
select usn, state, undoblockstotal “Total”, undoblocksdone “Done”, undoblockstotal-undoblocksdone “ToDo”,
decode(cputime,0,‘unknown’,sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Estimated time to complete”
from v$fast_start_transactions;

查询被kill了的会话使用的undo
select b.name “UNDO Segment Name”, b.inst# “Instance ID”, b.status$ STATUS, a.ktuxesiz “UNDO Blocks”, a.ktuxeusn as TRANS_XIDUSN, a.ktuxeslt TRANS_XIDSLOT, a.ktuxesqn TRANS_XIDSQN
from x$ktuxe a, undo$ b
where a.ktuxesta = ‘ACTIVE’ and a.ktuxecfl like ‘%DEAD%’ and a.ktuxeusn = b.us#;

2、重建表（不带索引），回插数据，建索引，加约束，同义词、授权

alter table INST.OFFER_PARAM rename to OFFER_PARAM_20180206

insert /+parallel(a 8)/ into INST.OFFER_PARAM a select /+parallel(b 8)/* from inst.OFFER_PARAM_20180206 b

select sum(bytes)/(102410241024) from dba_segments where segment_name=‘OFFER_PARAM’ and owner=‘INST’;

CREATE INDEX INST.IDX_OFFER_PARAM_AA_ID_0206 ON INST.OFFER_PARAM
(ATOM_ACTION_ID)
TABLESPACE TBS_IDX_INST parallel 16;

CREATE INDEX INST.IDX_OFFER_PARAM_OFFER_ID_0206 ON INST.OFFER_PARAM
(OFFER_ID)
TABLESPACE TBS_IDX_INST parallel 16;

CREATE INDEX INST.IDX_OFFER_PARAM_VAL_0206 ON INST.OFFER_PARAM
(VALUE)
TABLESPACE TBS_IDX_INST parallel 16;

CREATE UNIQUE INDEX INST.PK_OFFER_PARAM_0206 ON INST.OFFER_PARAM
(OFFER_PARAM_ID, ATOM_ACTION_ID)
TABLESPACE TBS_IDX_INST parallel 16;

alter INDEX INST.IDX_OFFER_PARAM_AA_ID_0206 noparallel;
alter INDEX INST.IDX_OFFER_PARAM_OFFER_ID_0206 noparallel;
alter INDEX INST.IDX_OFFER_PARAM_VAL_0206 noparallel;
alter INDEX INST.PK_OFFER_PARAM_0206 noparallel;