Re: why aren't ORA-1555 errors MORE frequent?

Actually, the hypothesis was that if the blocks before cleanout must be written to rollback/undo then "ORA-1650 Unable to extend rollback segment" or similar could result from a SELECT. Indeed, the cleanout scenario for ORA-1555 is even more difficult to produce.

Richard

"Howard J. Rogers" wrote:
>
> "Richard Kuhler" <noone_at_nowhere.com> wrote in message
> news:4ycD9.23206$%k2.7634219_at_twister.socal.rr.com...
> > Wait a second. I'm beginning to doubt what was said now. I don't see
> > why Oracle should have to write the old version of the block to undo
> > before doing the cleanout. The old version of the block was already
> > written to undo by the process that made the change that must now be
> > cleaned out. The current block data includes the rollback header
> > segment information but it seems like that must still be retained after
> > the cleanout for read consistency. How else would it know where to find
> > the previous version of this block?
> >
> > I did a test similar to what was described (code below) without any
> > errors.
>
> Which is why I didn't respond to it. Demonstrating delayed block
> cleanout-induced 1555s *is* fiendishly difficult.
>
> HJR
>
> >The statistics from the select show that block cleanout was
> > done on almost all the data. There certainly was not enough undo space
> > for all those blocks. Redo was definitely produced but I can't see
> > anything that indicates undo was produced.
> >
> > Am I missing something?
> >
> >
> > create table t (value varchar2(80))
> > /
> >
> > begin
> > for i in 1 .. 1000000 loop
> > insert into t values (rpad('*', 80, '*'));
> > end loop;
> > end;
> > /
> >
> >
> > alter system set db_cache_size = 1
> > /
> >
> > update t
> > set value = rpad('!', 80, '!')
> > /
> >
> > create undo tablespace tmp_undo_ts datafile
> > '/dat2/oradata/dev9ir2/tmpundo.dbf' size 256k
> > /
> >
> > alter system set undo_tablespace = tmp_undo_ts
> > /
> >
> > -- disconnect and reconnect to be sure undo changed
> >
> > select count(*)
> > from t
> > /
> >
> > select name, value
> > from v$mystat, v$statname
> > where v$mystat.statistic# = v$statname.statistic#
> > /
> >
> > NAME
> > VALUE
> > ----------------------------------------------------------------
> > ----------
> > ...
> > consistent gets
> > 11914
> > physical reads
> > 11912
> > db block changes
> > 11905
> > ...
> > DBWR undo block
> > writes 0
> > ...
> > redo entries
> > 11905
> > redo size
> > 714300
> > ...
> > no work - consistent read
> > gets 4
> > cleanouts only - consistent read gets
> > 11905
> > rollbacks only - consistent read
> > gets 0
> > cleanouts and rollbacks - consistent read
> > gets 0
> > ...
> > immediate (CURRENT) block cleanout
> > applications 0
> > immediate (CR) block cleanout applications
> > 11905
> > deferred (CURRENT) block cleanout
> > applications 0
> > commit txn count during cleanout
> > 11905
> > active txn count during
> > cleanout 0
> > cleanout - number of ktugct calls
> > 11905
> > ...
> > table scan blocks gotten
> > 11909
> > ...
> >
> >
> > Richard Kuhler
> >
> > "Charles J. Fisher" wrote:
> > >
> > > On Wed, 20 Nov 2002, Howard J. Rogers wrote:
> > >
> > > > You can certainly get 1555s when no-one is doing anything other than
> pure
> > > > selects, true enough. They are incredibly hard to demonstrate,
> however,
> > > > being as rare as hen's teeth.
> > >
> > > This shouldn't be that difficult to demonstrate.
> > >
> > > 1. Restart your instance with less than 100 block buffers.
> > > 2. Update a big table (much, much bigger than 100 blocks), adding
> > > 1 to sum num column, and commit it.
> > > 3. Throw the tablespace in backup mode.
> > > 4. select * from the table.
> > >
> > > Shouldn't this do it?
> > >
> >
> --------------------------------------------------------------------------
> ---
> > > | The Moving Finger writes; and, having writ,
> |
> > > | Moves on: nor all your Piety nor Wit
> |
> > > | Shall lure it back to cancel half a Line,
> |
> > > | Nor all your Tears wash out a Word of it.
> |
> > > |
> |
> > > | -Rubaiyat of Omar Khayyam
> |
> >
> --------------------------------------------------------------------------
> ---
> > > / Charles J. Fisher | cfisher@rhadmin.org | http://rhadmin.org
> /
> >
> > --------------------------------------------------------------------------
> ---
> >
Received on Thu Nov 21 2002 - 16:41:37 CST