Re: ORA 1555 and select count(*)

Delayed block cleanout relates to fact commits, which do not generate a complete block update, and leave the ITL entry in a special state.

Cleanout is then performed by the next transaction that UPDATES the block, so (in general) a count(*) will not cause block cleanout.

However - a fast commit is not applied at commit time to the blocks which the transaction has changed but which have been flushed back to disc before the commit occurred (i.e. Oracle will not re-read blocks simply to 'fast-comit' them). In this circumstance the normal commit cleanout will take place, i.e. the next reader will cause a full commit change to the ITL and generate the redo.

It is possible that SAP has generated a large number of long-running transaction before this one, and therefore failed to perform a fast commit on lots of blocks, leaving them to the count(*).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Hans-Peter Sloot wrote in message <7jq9ut$pma$1_at_hdxl22.telecom.ptt.nl>...
>Hello Jonathan,
>
>I am afraid your answer is not sufficient.
>
>It has to do with delayed block cleanout.
>Simply running again the SAP query will probably help not at all.
>What the select count(*) does is the block cleanout (which had been
>delayed before).
>
>The select count(*) does not change anything in rollback but
>in the table data. If JohannesBavendiek_at_gmx.de would repeat
>the select count(*) he would see that the second is a lot faster.
Received on Fri Jun 11 1999 - 01:47:06 CDT