Tag Archives: undo

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也减少了