Re: Undo

From: joel garry <joel-garry_at_home.com>
Date: Wed, 9 Jan 2008 14:20:04 -0800 (PST)
Message-ID: <715f5c70-9149-404d-84d7-ad2bcc5f52b0@i29g2000prf.googlegroups.com>


On Jan 9, 8:48 am, Steve Howard <stevedhow..._at_gmail.com> 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

Hazily wondering if you have partitioned indices ("The database also ensures that the [local] index is maintained automatically when maintenance operations are performed on the underlying table. " - DBA guide) or are using any parallel operations (so try a noparallel hint - what is the plan? - maybe long-ago bugs like poking at each partition when only one should be are reawakening under some obscure circumstances). If the touch everything in the table trick isn't working for delayed cleanout, maybe something else has the cleanout problem, like an internal table tracking the partitions? Or maybe it isn't the cleanout problem. Could your session be seeing the effects of the stuff happening on the write side of the link? Or maybe stacked consistency of just a few hot undo blocks?

Or maybe with billions of rows you have some hash collisions, which makes it have to follow some hash cluster chaining, and it gets counted as a consistent get for undo. Is that too weird?

I'm thinking with some of those you'd see the same thing with Dan's transportable ts suggestion as you move it to the ts. Not sure why he didn't suggest transporting the original ts.

Are you sure no one is curious and poking around in your table while you are copying it?

Googletease:
http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA609&lpg=PA609&dq=high+consistent+gets+partitioning+oracle&source=web&ots=RyYfat1A0C&sig=M_QF53munvGO8RCzTwg59yI2624#PPA609,M1

So, how are you copying it?

jg

--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/25.01.html#subj13
Received on Wed Jan 09 2008 - 16:20:04 CST

Original text of this message