Re: ora-1555 and delayed block cleanout

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Fri, 1 Feb 2008 06:37:52 -0800 (PST)
Message-ID: <2710a2c1-05be-4424-bb3c-9833aadc6408@j78g2000hsd.googlegroups.com>

On Feb 1, 9: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 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

Hi Chuck,

There are tons of them, for which the doc is pretty comprehensive...I have found not always correct, but at least it's there :)

SQL> select name from v$statname where name like '%clean%';

NAME

---

commit cleanout failures: write disabled
commit cleanout failures: block lost
commit cleanout failures: cannot pin
commit cleanout failures: hot backup in progress
commit cleanout failures: buffer being written
commit cleanout failures: callback failure

commit cleanouts
commit cleanouts successfully completed
cleanouts only - consistent read gets
cleanouts and rollbacks - consistent read gets immediate (CURRENT) block cleanout applications immediate (CR) block cleanout applications deferred (CURRENT) block cleanout applications commit txn count during cleanout
active txn count during cleanout
cleanout - number of ktugct calls
immediate CR cleanouts (index blocks)
deferred CUR cleanouts (index blocks)
SMON posted for supplemental logging cleanup

19 rows selected.

SQL> HTH, Steve Received on Fri Feb 01 2008 - 08:37:52 CST