Excessive transaction tables consistent reads - undo records applied in 11.2.0.3
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 3 Oct 2012 09:43:34 +0300
Hi
Did anyone came accross excessive "tables consistent reads - undo records"
in 11.2.0.3 ?
In our case it looks like this:
- select * from table, full scan
- table segment is about 400MB, one would not expect more than a few GB of
physical IO for a full scan.
- physical IO reaches terabytes (of UNDO segments), "transaction tables
consistent reads - undo records applied", "consistent gets" roughly
corresponds to IO (terabytes), the other undo statistics (like "data blocks
consistent reads - undo records applied" ) have moderate values.
- Developers confirmed the execessive reads happen when two batch jobs
modifying the same table cross cross each other. But - the funny point is
that one of the jobs finishes in tens of minutes. The other job is left
compromised: it crawls slowly for 10 or more hours just to full scan 400MB
table doing the excessive undo IO. Nothing else is accomplished in between:
just bulk fetch by 1000 records into PL/SQL (var)array.
As far as we saw the process is not spinning: select * from table does
fetch records but at extremely slow pace.
Oracle support lists a few bugs as fas as excessive "transaction tables
consistent reads - undo records applied" are concerned but I have not find
one for 11.2.0.3
On the other hand I am wandering if this is exactly a bug: can it be that
"transaction tables consistent reads - undo records applied" goes over the
roof if one session does a select while the other sessions manage to modify
the same table segment and expire transactional tables in undo segments ?
Brgds, Laimis N
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
