Re: ORA 1555 and select count(*)

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.

            Regards Hans-Peter

Jonathan Lewis wrote in message
<929036043.2129.1.nnrp-03.9e984b29_at_news.demon.co.uk>...
>It didn't.
>
>Assume you are at SCN 10,000 right now, and the
>original statment needed to see some data as at
>SCN 5,000, but the rollback segment to get just
>one block back from SCN 10,000 to 5,000 has
>been overwritten. - ORA-01555 Snapshot too old
>
>Now you run your count(*) - it wants to see
>data as at SCN 10,000 because that's
>when you started it. No problem.
>
>Similarly, re-running the SAP query, it now
>wants to see data as at SCN 10,000. again
>no problem
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>JohannesBavendiek_at_gmx.de wrote in message <7jof95$i5q$1_at_nnrp1.deja.com>...
>>I made the experience that the problem with "delayed block cleanout"
>>(ORA 1555) was resolved by executing select count(*) ... before
>>repeating the step.
>>
>>Here the description of the situation:
>>
>>1. a step runs on ora 1555 error in table QCMCOEP (a SAP table for
>>conversion use only)
>>2. execute select count(*) from QCMCOEP (result: about 19 Mio rows)
>>3. repeat of the step runs and ends ok. No other changes made!
>>
>>This happened in Oracle 8 under AIX.
>>The process was a step in a SAP Upgrade, that executed a table
>>conversion on table COEP.
>>
>>How does the simple select count(*) ... change data in rollbacks?
>>Can anyone explain this to me?
>
>
>
Received on Fri Jun 11 1999 - 01:23:41 CDT