transaction tables consistent reads - undo records applied

10.2.0.4 three node cluster EE on SLES 10

Can someone give me a good definition of *exactly* what this means and what causes it (mainly the latter). The documentation is not very descriptive, with "Number of undo records applied to transaction tables that have been rolled back for consistent read purposes".

It sounds like undo on undo, but we don't have any larger number for rollbacks (or commits) when this happens than we do at any other time.

We have been plagued by this for over a year, and after multilpe SR's where the support analyst just reads us the documentation, I am at my wits end.

We have a fairly large table (almost 1TB with about 300 million rows) with a large XMLTYPE column. Once a day, a job scans this table for records added that day for propagation to an external system. The longer the query runs, the more we see the session doing single block reads against the undo tablespace, with the stat in the subject climbing into the millions. Eventually, after several hours, an ORA-01555 is thrown.

I even grabbed one of the P1/P2 parameters for the session querying and dumped the undo block in the P2 value. While it was a second or two after the event was posted, the block itself didn't even contain any references to the table being queried!

Can anyone shed some light?

Thanks,

Steve Received on Thu Jun 24 2010 - 21:20:20 CDT