enq: TX - row lock c 2u1bpvx9s2ygp ACTIVE
ontention
SQL> select sid,serial#,machine,program,EVENT,SQL_ID,STATUS,saddr from v$session where SADDR in(select SES_ADDR from v$transaction);
no rows selected
SQL> select START_TIME,STATUS,SES_ADDR from v$transaction;
no rows selected
注意事项： 大事务回滚时可能会产生大量REDO信息；
同时并行回滚参数设置不当(如过高)也可能导致回滚事务时HANG住，建议使用默认值LOW。
SQL> show parameter fast_start_p
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
查看一个参数是否默认值及允许的值
set linesize 200
col NAME for a30
col value for a30
col isdefault for a10
select * from V$PARAMETER_VALID_VALUES where name='fast_start_parallel_rollback';
NUM NAME ORDINAL VALUE ISDEFAULT
---------- ------------------------------ ---------- ------------------------------ ----------
782 fast_start_parallel_rollback 1 FALSE FALSE
782 fast_start_parallel_rollback 2 LOW TRUE
782 fast_start_parallel_rollback 3 HIGH FALSE
SQL> select * from V$PARAMETER_VALID_VALUES where name like '%statistics_level%';
NUM NAME ORDINAL VALUE ISDEFAULT
---------- ------------------------------ ---------- ------------------------------ ----------
1182 statistics_level 1 BASIC FALSE
1182 statistics_level 2 TYPICAL TRUE
1182 statistics_level 3 ALL FALSE
---------
Mon Aug 24 14:00:52 2015
Thread 1 advanced to log sequence 81771 (LGWR switch)
Current log# 3 seq# 81771 mem# 0: /oralog/orcl/redo03.log
Mon Aug 24 14:47:49 2015
SMON: Restarting fast_start parallel rollback
Mon Aug 24 14:47:57 2015
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/orcl/udump/orcl_ora_27174.trc.
Mon Aug 24 14:47:57 2015
Thread 1 advanced to log sequence 81772 (LGWR switch)
Mon Aug 24 14:47:57 2015
Thread 1 advanced to log sequence 81772 (LGWR switch)
Current log# 1 seq# 81772 mem# 0: /oralog/orcl/redo01.log
Mon Aug 24 14:48:09 2015
Thread 1 advanced to log sequence 81773 (LGWR switch)
Current log# 2 seq# 81773 mem# 0: /oralog/orcl/redo02.log
Thread 1 cannot allocate new log, sequence 81774
Checkpoint not complete
Current log# 2 seq# 81773 mem# 0: /oralog/orcl/redo02.log
Mon Aug 24 14:48:44 2015
Thread 1 advanced to log sequence 81774 (LGWR switch)
Current log# 3 seq# 81774 mem# 0: /oralog/orcl/redo03.log
Mon Aug 24 14:48:55 2015
Thread 1 cannot allocate new log, sequence 81775
Checkpoint not complete
Current log# 3 seq# 81774 mem# 0: /oralog/orcl/redo03.log
|