Date: Wed, 9 Jan 2008 08:48:25 -0800 (PST)
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%');
consistent gets from cache
consistent gets - examination
direct 0consistent changes
transaction tables consistent reads - undo records applied 638911
transaction tables consistent read
rollbacks 70data blocks consistent reads - undo records applied 51998
no work - consistent read gets
cleanouts only - consistent read gets
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
13 rows selected.
SQL> Any clues on what we can do?
Steve Received on Wed Jan 09 2008 - 10:48:25 CST