Re: Undo

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 09 Jan 2008 11:19:34 -0800
Message-ID: <1199906356.191504@bubbleator.drizzle.com>


Steve Howard wrote:
> 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

Create a tablespace
Move it to the tablespace
Transport the tablespace

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Jan 09 2008 - 13:19:34 CST

Original text of this message