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

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 21 Nov 2002 21:49:52 GMT
Message-ID: <4ycD9.23206$%k2.7634219@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. 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 - 15:49:52 CST