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

"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

/


>

> --------------------------------------------------------------------------

---


>