没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
回滚段引起的相关故障
故障现象:enq: US -contention
故障原因:事务量突然增加
解决方法:ALTER SYSTEM SET "_rollback_segment_count"=<n>;
具体分析:
mos相关解释:
You have many offline undo segments and the workload starts to online many undo segments over a short period of time.
This can lead to high 'latch: row cache objects' contention may be seen on DC_ROLLBACK_SEGMENTS together with high 'enq: US - contention' waits
when using system managed undo with an auto tuned undo retention period。
The wait event "enq: US Contention" is associated with contention on the latch in the row cache (dc_rollback_seg).
Enqueue US - Contention can become a bottle-neck for performance if workload dictates that a lot of offlined undo segments must be onlined over a short period of time.
The latch on the row cache can be unable to keep up with the workload.
当系统activity增加或者降低的时候,oracle SMON进程会自动ONLINE或者OFFLINE rollback segments。这样导致某些与undo segments相关的latch或者enqueue被hold住太长时间,
导致系统很多活跃session都开始等待enq: US - contention。
一般情况下 稳定系统中突然出现的Enq:US 是因为并发事务数突然变多导致的,而实际引发并发事务数变多的可能是某些DML语句遇到了性能问题,导致事务处理速度变慢,或者是commit变慢所致。
针对上述问题,enq:US本身只是性能问题所导致的“果”,而不是原因,一般解决了上述问题,enq:US的这个果也就消失了。
方法1:查看长事物和并发事务的方法 基表视图 v$sysmetric,v$sysmetric_history,dba_hist_sysmetric_history ,v$sysmetric_summary
--查看一段时间内的commit,rollback的事务数 (dba_hist_sysmetric_summary视图对应于awr的Load Profile模块),如果取其他指标可以替换掉参数metric_name
select 'Commits rollback' metric_name, a.maxval, a.minval, a.average, b.begin_interval_time
from dba_hist_sysmetric_summary a, dba_hist_snapshot b
where a.metric_name in('User Commits Per Sec','User Rollbacks Per Sec')
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.instance_number in (select instance_number from v$instance)
故障现象:enq: US -contention
故障原因:事务量突然增加
解决方法:ALTER SYSTEM SET "_rollback_segment_count"=<n>;
具体分析:
mos相关解释:
You have many offline undo segments and the workload starts to online many undo segments over a short period of time.
This can lead to high 'latch: row cache objects' contention may be seen on DC_ROLLBACK_SEGMENTS together with high 'enq: US - contention' waits
when using system managed undo with an auto tuned undo retention period。
The wait event "enq: US Contention" is associated with contention on the latch in the row cache (dc_rollback_seg).
Enqueue US - Contention can become a bottle-neck for performance if workload dictates that a lot of offlined undo segments must be onlined over a short period of time.
The latch on the row cache can be unable to keep up with the workload.
当系统activity增加或者降低的时候,oracle SMON进程会自动ONLINE或者OFFLINE rollback segments。这样导致某些与undo segments相关的latch或者enqueue被hold住太长时间,
导致系统很多活跃session都开始等待enq: US - contention。
一般情况下 稳定系统中突然出现的Enq:US 是因为并发事务数突然变多导致的,而实际引发并发事务数变多的可能是某些DML语句遇到了性能问题,导致事务处理速度变慢,或者是commit变慢所致。
针对上述问题,enq:US本身只是性能问题所导致的“果”,而不是原因,一般解决了上述问题,enq:US的这个果也就消失了。
方法1:查看长事物和并发事务的方法 基表视图 v$sysmetric,v$sysmetric_history,dba_hist_sysmetric_history ,v$sysmetric_summary
--查看一段时间内的commit,rollback的事务数 (dba_hist_sysmetric_summary视图对应于awr的Load Profile模块),如果取其他指标可以替换掉参数metric_name
select 'Commits rollback' metric_name, a.maxval, a.minval, a.average, b.begin_interval_time
from dba_hist_sysmetric_summary a, dba_hist_snapshot b
where a.metric_name in('User Commits Per Sec','User Rollbacks Per Sec')
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.instance_number in (select instance_number from v$instance)
--and a.begin_time...
-- and a.end_time=...
order by a.snap_id desc;
方法2:查看 v$undostat dba_hist_undostat 中TXNCOUNT字段,
通过以上两步可以发现当前事务量突然增加。导致的产生此故障
解决方法:
1.Bounce the instance.
2.Setting _ROLLBACK_SEGMENT_COUNT to a high number to keep undo segments online:
ALTER SYSTEM SET "_rollback_segment_count"=<n>;这个操作需要重启数据库才能生效
_rollback_segment_count参数是控制非SYSTEM回滚段处于online状态的个数;
_rollback_segment_count参数越大,保持online状态的回滚段就越多。9i默认情况下oracle会自己去进行判断,
有自己的算法,随着不停的inactive和active操作,smon去offline和online回滚段,会加剧系统资源的消耗;
副作用是可能的数据库startup变慢
3.Set _UNDO_AUTOTUNE to FALSE:
ALTER SYSTEM SET "_undo_autotune" = false;
4.A fix to Bug:7291739 is to set a new hidden parameter, _HIGHTHRESHOLD_UNDORETENTION to set a high threshold for undo retention completely distinct from maxquerylen:
ALTER SYSTEM SET "_highthreshold_undoretention"=<n>;
相关资料
1:隐藏参数查看
set linesize 200;
col ksppinm format a40;
col ksppdesc format a50;
col ksppstvl format a20;
-- and a.end_time=...
order by a.snap_id desc;
方法2:查看 v$undostat dba_hist_undostat 中TXNCOUNT字段,
通过以上两步可以发现当前事务量突然增加。导致的产生此故障
解决方法:
1.Bounce the instance.
2.Setting _ROLLBACK_SEGMENT_COUNT to a high number to keep undo segments online:
ALTER SYSTEM SET "_rollback_segment_count"=<n>;这个操作需要重启数据库才能生效
_rollback_segment_count参数是控制非SYSTEM回滚段处于online状态的个数;
_rollback_segment_count参数越大,保持online状态的回滚段就越多。9i默认情况下oracle会自己去进行判断,
有自己的算法,随着不停的inactive和active操作,smon去offline和online回滚段,会加剧系统资源的消耗;
副作用是可能的数据库startup变慢
3.Set _UNDO_AUTOTUNE to FALSE:
ALTER SYSTEM SET "_undo_autotune" = false;
4.A fix to Bug:7291739 is to set a new hidden parameter, _HIGHTHRESHOLD_UNDORETENTION to set a high threshold for undo retention completely distinct from maxquerylen:
ALTER SYSTEM SET "_highthreshold_undoretention"=<n>;
相关资料
1:隐藏参数查看
set linesize 200;
col ksppinm format a40;
col ksppdesc format a50;
col ksppstvl format a20;
剩余5页未读,继续阅读
资源评论
三朝看客
- 粉丝: 177
- 资源: 109
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功