Re: ORA 1555 and select count(*)

What you are describing does not relate to the 'fast commit' mechanism, but to the traditional commit mechanism used in Version 6 (and some early versions of 7 probably).

There are two different actions that can happen when a commit occurs:
a) A changed block is still in the SGA b) A changed block is no longer in the SGA

In the former case, the block is partially updated (and redo generated) to put it into a 'fast commit' state ( the ITL is marked to show the commit SCN, but the ITL flag is set to U, not C, The big difference difference though is that the 'lock flag' on the row is NOT changed to 0, and still points to the ITL.) From this point onwards, the block will not change to a 'fully cleaned block' and the redo describing those final steps of the cleanup will not occur until the next update to the block.

Delayed_logging block cleanout refers to this phenomenon, and (I believe) exists largely to allow fast commits and Oracle Parallel Server to co-exist without an extreme overhead in pinging.

In the second case (the change block is no longer in the SGA), the fast commit cannot take place (a 'fast commit' that had rto read a block would not be all that fast). In this case reading a block causes all clean-out actions to run to completion - which is the phenomenon you are describing.

Hence my point (expanded in the second post) that 'select count(*)' will not get rid of the problem unless SAP has previously been running a lot of very large transactions that forced a large number of blocks to disc so that they could not be processed through a fast commit.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Mon Jun 14 1999 - 05:01:39 CDT