Re: ora-1555 and delayed block cleanout

From: <fitzjarrell_at_cox.net>
Date: Fri, 1 Feb 2008 07:20:08 -0800 (PST)
Message-ID: <c1bf28bb-1345-46bb-a3a3-d529e87c7a75@j20g2000hsi.googlegroups.com>


On Feb 1, 8:28 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> 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 the failing query following a large update operation? Oracle will allocate commit lists in the buffer cache until it reaches 10% of the cache allocated, at which point it stops. So, if the number of modified blocks from an update statement exceeds 10% of the db_block_buffers allocated you'll get a delayed block cleanout with the next select against those blocks (this also means that the select statement will generate redo).

>
> 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

I would suspect the following statistic would be what you are looking for:

Statatistic number: 181
Name: cleanouts only - consistent read gets

David Fitzjarrell Received on Fri Feb 01 2008 - 09:20:08 CST

Original text of this message