Re: ora-1555 and delayed block cleanout
Date: Sat, 2 Feb 2008 11:29:10 -0000
Message-ID: <iZKdnT1TEuwdyTnanZ2dneKdnZydnZ2d@bt.com>
"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.htmlReceived on Sat Feb 02 2008 - 05:29:10 CST