Undo

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 9 Jan 2008 08:48:25 -0800 (PST)
Message-ID: <2ff98722-0c34-4bd8-8f23-0cf5cf2d4de8@e25g2000prg.googlegroups.com>


Hi All,

Oracle 10.2.0.3 non-RAC on RHAT 4 32 bit

We are trying to copy a fairly large table (about 8 billion rows with 16 hash partitions) to another database. We are periodically running into performance issues during the copy via database lihk where the source database will begin reading undo blocks. We see in v$sesstat for the session the following...

SQL> select name,value
  2 from v$sesstat s,
  3 v$statname sn
  4 where sid = 221

  5      and s.statistic# = sn.statistic#
  6      and (name like '%consi%');

NAME
VALUE




consistent gets
3557713
consistent gets from cache
3557713
consistent gets - examination
715695
consistent gets
direct                                                    0
consistent changes
690915
transaction tables consistent reads - undo records applied 638911
transaction tables consistent read
rollbacks                             70
data blocks consistent reads - undo records applied 51998
no work - consistent read gets
2814270
cleanouts only - consistent read gets
26838
rollbacks only - consistent read
gets                                   436
cleanouts and rollbacks - consistent read
gets                            7

12 rows selected.

SQL> My question is why the "transaction tables consistent reads - undo records applied" is so high with automatic undo in place?

Below is the doc from oracle in the performance tuning guide on tahiti...

/



If there are few very, large rollback segments, then your system could be spending a lot of time rolling back the transaction table during delayed block cleanout in order to find out exactly which SCN a transaction was committed. When Oracle commits a transaction, all modified blocks are not necessarily updated with the commit SCN immediately. In this case, it is done later on demand when the block is read or updated. This is called delayed block cleanout.

The ratio of the following V$SYSSTAT statistics should be close to 1:

ratio = transaction tables consistent reads - undo records applied /

        transaction tables consistent read rollbacks

The recommended solution is to use automatic undo management.
/


We even full scanned the partitions before copying the data (hoping to pay the delayed block cleanout "price" locally), but we still see this. There are very few changes occurring on the table right now...

SQL> select begin_interval_time,physical_writes_delta   2 from DBA_HIST_SEG_STAT dhss
  3 natural join dba_hist_snapshot dhs   4 where ts#=89
  5 and begin_interval_time > sysdate - 1   6 order by 1
  7 /

BEGIN_INTERVAL_TIME
PHYSICAL_WRITES_DELTA




08-JAN-08 02.00.37.198
PM
349
08-JAN-08 05.00.57.132
PM
1354
08-JAN-08 05.31.01.785
PM
40817
08-JAN-08 06.00.05.224
PM
5217
08-JAN-08 06.30.08.121
PM
3716
08-JAN-08 07.00.12.864
PM
3263
08-JAN-08 07.30.19.094
PM
1832
08-JAN-08 08.00.24.529
PM
767
08-JAN-08 08.30.29.818
PM
64
08-JAN-08 11.30.54.736
PM
1353
09-JAN-08 12.00.59.535
AM
2857
09-JAN-08 12.30.05.408
AM
754
09-JAN-08 11.00.06.713
AM
9166

13 rows selected.

SQL> Any clues on what we can do?

Thanks!

Steve Received on Wed Jan 09 2008 - 10:48:25 CST

Original text of this message