Re: ora-1555 and delayed block cleanout

"Chuck" <skilover_nospam_at_bluebottle.com> wrote in message news:b0Goj.522$K%.403_at_trnddc04...
> Is there a way to verify that an ora-1555 is being caused by delayed
> block cleanout? I have a query that failed after 13000 seconds. The
> undo_retention is set to 28800 and v$undostat does not show any anything
> in UNXPSTEALCNT during the query. I suspect it was caused by delayed
> block cleanout but would like a way to categorically prove it.
>
> Is there a v$sysstat parameter that counts delayed block cleanouts?
> There's a bunch the include cleanout in the name but I haven't found
> clear descriptions of them. Is "deferred (CURRENT) block cleanout
> applications" the same as delayed block cleanouts?
>
> DB version is 10.2.0.3

You don't mention UNXPBLKRELCNT and (particularly) UNXPBLKREUCNT, but if these are also zero then in theory what you are describing should not be possible.

The thing about delayed block cleanout and queries is that Oracle need only do
it if the query NEEDS to check whether something that appears to be an uncommitted change has really been committed, and then has to find out if it committed before or after the query started. At worst, it only has to
get a "least upper bound" commit time - which in your case would be about 13,000 seconds ago, hence within the retention time.

For queries that need to take this 'when did it commit' action a long way back
into the past, the most revealing statistics are:

    transaction tables consistent reads - undo records applied     transaction tables consistent read rollbacks     cleanout - number of ktugct calls

The last one is the number of times you did a 'get commit time' call, the second is the number of consistent read copies of undo segment header blocks you created, and the first is the number of undo records you had to use to create those CR copies.

But I don't think anything can tell you that the 1555 was because of a failure to create a transaction table CR.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html