Excessive transaction tables consistent reads - undo records applied in 11.2.0.3

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

--
http://www.freelists.org/webpage/oracle-l