Re: ORA 1555 and select count(*)

Sorry, I'm a new guy... ITL? What's that?

Jonathan Lewis wrote:

> 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 Sat Jun 12 1999 - 20:46:54 CDT