Tag Archives: undo

ORA-01555和延迟块清除

Posted on by
Reply 
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也会发生.
Continue reading →

Transaction Tables Consistent Reads

Posted on by
Reply

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. Continue reading →

reading of this two week

Posted on by
Reply

Oracle automatic segment space management: ASSM internal structures
http://www.dba-oracle.com/art_builder_assm.htm

Not KEEPing
http://jonathanlewis.wordpress.com/2010/03/20/not-keeping/

Row Directory
http://jonathanlewis.wordpress.com/2009/05/21/row-directory/

Index too big
http://jonathanlewis.wordpress.com/2010/03/25/index-too-big/

heap block compress
http://jonathanlewis.wordpress.com/2010/03/30/heap-block-compress/

Failed Login
http://jonathanlewis.wordpress.com/2010/04/05/failed-login/

Daylight Saving Time Causes Performance Issues!
http://prodlife.wordpress.com/2010/04/02/daylight-saving-time-causes-performance-issues/

Block cleanout – fast or delayed.
http://www.jlcomp.demon.co.uk/cleanout.html

Snapshot too old and delayed block cleanout
http://www.ixora.com.au/q+a/0103/24224451.htm

Parse Calls
http://jonathanlewis.wordpress.com/2007/07/03/parse-calls/

Nutshell – 1
http://jonathanlewis.wordpress.com/2009/10/14/nutshell-1/

使用append hint 减少undo的产生

Posted on by
Reply 
sid@CS10G> @mystat "undo change vector size"
sid@CS10G> set echo off

NAME                                VALUE
------------------------------ ----------
undo change vector size         154481820

sid@CS10G> insert into t select * from all_objects

49654 rows created.

sid@CS10G> commit;

Commit complete.

sid@CS10G> @mystat2
sid@CS10G> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
undo change vector size         154713576          231,756

sid@CS10G> @mystat "undo change vector size"
sid@CS10G> set echo off

NAME                                VALUE
------------------------------ ----------
undo change vector size         154713576

sid@CS10G> insert /*+ append */ into t select * from all_objects;

49654 rows created.

sid@CS10G> commit;

Commit complete.

sid@CS10G> @mystat2
sid@CS10G> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
undo change vector size         154719336            5,760

p.s: undo减少了，redo log也减少了