标签归档:delayed_block_cleanout

ORA-01555和延迟块清除

01555, 00000, "snapshot too old: rollback segment number %s
with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read
//         are overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase
//	undo_retention setting. Otherwise, use larger rollback segments

出现ORA-01555直接的原因是一致读所需的undo records被覆盖, 一致读失败有两种情况:
1. 数据块中ITL结构对应的undo block被覆盖, 无法构造一致读.
2. transaction table in undo segment header: 延迟块清除发生时, 如果Oracle需要回滚对应的transaction table, 找到事务确切提交的时间. 而且所需的undo record被覆盖, ORA-01555也会发生.
继续阅读

Transaction Tables Consistent Reads

There is an interesting case in this week. A query run over hours, it take millions of buffer gets. The query is to verify if there is any data in the past 3 month for a customer, which usually completes in a minute. The case can’t be reproduced in production clone DB. The same execution path return around 10 seconds, doing 3829 buffer gets. The execution plan is as expected as below, the index skip scan will return hundreds of rows, zero or several rows return after nested loop join. 继续阅读