oracle新人关于redo和undo的疑惑

liqy103

Thanks rigorous attitude from Yong Huang. The database version is 10.2.0.4 SE.

My understand stand is UNDO protected by REDO, all changes need to be written to REDO.  While 3GB is more than 3 times of 900Mb (total redo size) , even someone was selecting the deleted table , can this make the difference so significant?

Could you elaborate  "if while your job is running, users are selecting from the table you're changing, more undo is used. If no existing undo blocks can satisfy his query, new undo blocks will be created for him, which generates redo" ? Thanks!

Yong Huang

> While 3GB is more than 3 times of 900Mb (total redo size) , even someone was selecting the deleted table ,
> can this make the difference so significant?

Why not test? Keep monitoring your alert.log. As soon as you see the error (or see used undo blocks starting to go down in v$transaction), check the session's 'redo size' statistic from v$sesstat. See if the increase in redo so far is about the same as in your second case.

>  Could you elaborate "if while your job is running, users are selecting from the table you're changing...

I could be wrong. Let me think more about it. Sorry. (I removed it from my last message.)

Yong Huang

speeder85

唉，满篇的英文看得真累，都还给老师了

liqy103

I know the answer now, when undo tablespace is too small causes more REDO log generated.
In short, the total redo log generated = redo log during delete + redo log due to roll back.

Will post my experiment over the weekend.  

Yong Huang

> when undo tablespace is too small causes more REDO log generated.

That statement is misleading. It's not directly attributed to the small size of the undo tablespace. It's because your transaction is rolled back after running out of space. The rollback could happen under other conditions too, e.g. session being killed. (But of course in that case, you can only find the remaining redo from v$sysstat, not v$sesstat since the session was already killed.)

Yong Huang

liqy103

I think my description describes the symptom, and Yong Huang elaborates the details (root cause).

Below are my tests.

SQL> select * from v$statname where name like '%redo%';

STATISTIC# NAME                                                                  CLASS          STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
        90 redo synch writes                                                             8 1439995281
        91 redo synch time                                                             8 4215815172
       132 redo blocks read for recovery                                             2 2679943069
       133 redo entries                                                              2 3488821837
      134 redo size                                                                     2 1236385760
       135 redo buffer allocation retries                                             2 1446958922
       136 redo wastage                                                              2 3462806146
       137 redo writer latching time                                                     2 2166056472
       138 redo writes                                                                     2 1948353376
       139 redo blocks written                                                             2 2391431605
       140 redo write time                                                             2 3094453259
       141 redo log space requests                                                     2 1985754937
       142 redo log space wait time                                                     2        252430928
       143 redo log switch interrupts                                                     2        674283274
       144 redo ordering marks                                                             2 2104561012
       145 redo subscn max counts                                                     2        449106517

16 rows selected.

SQL> select * from v$statname where name like '%undo%';

STATISTIC# NAME                                                                  CLASS          STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
        75 DBWR undo block writes                                                     8        111270822
       174 undo change vector size                                                   128 1465971540
       175 transaction tables consistent reads - undo records applied                   128 1054055970
       177 data blocks consistent reads - undo records applied                           128 2915445793
       186 rollback changes - undo records applied                                   128 3616249137
       198 auto extends on undo tablespace                                           128 3140365462
       200 total number of undo segments dropped                                   128 3623640507
       218 global undo segment hints helped                                           128 1791494885
       219 global undo segment hints were stale                                    128 3309048233
       220 local undo segment hints helped                                           128 1047863356
       221 local undo segment hints were stale                                           128 2070200837
       222 undo segment header was pinned                                           128        248401831
       224 SMON posted for undo segment recovery                                   128 2117898593
       227 SMON posted for undo segment shrink                                           128        579492169
       234 IMU undo retention flush                                                   128 2087226422
       240 IMU undo allocation size                                                   128        244193920

16 rows selected.


REM    statistic# 134 "redo size"                                       
REM    statistic# 174 "undo change vector size"         (correct me if this is not the correct statname to monitor)
REM In the following tests, I'll monitor these two statnames

REM test 1: make UNDO TABLESPACE small enough (40MB) to simulate the failure.
SQL> select * from dba_data_files where tablespace_name='UNDO';

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                       BYTES         BLOCKS STATUS          RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/usr/lib/oracle/xe/oradata/XE/undo.dbf
         2 UNDO                             41943040           5120 AVAILABLE             2 NO            0               0            0        418775045112 ONLINE


Elapsed: 00:00:00.01


REM redo, undo value before deletion
SQL> select * from v$mystat where statistic# in (134,174);

       SID STATISTIC#           VALUE
---------- ---------- ----------
        38          134  199051128
        38          174        53161332

Elapsed: 00:00:00.00
SQL> delete from t;
delete from t
            *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'


Elapsed: 00:00:24.05
SQL> select * from v$mystat where statistic# in (134,174);

       SID STATISTIC#           VALUE
---------- ---------- ----------
        38          134  331696900
        38          174        92619292

Elapsed: 00:00:00.00

134(REDO) 331696900-199051128=126.50MB
174(UNDO) 92619292−53161332=39457960=37.63MB


REM test 2: In crease UNDO tablespace to make it sufficient for big deletion


SQL> alter database datafile '/usr/lib/oracle/xe/oradata/XE/undo.dbf' resize 300M;

Database altered.

Elapsed: 00:00:03.42

REM note that the table size is about 80MB
SQL> select bytes , tablespace_name from user_segments where segment_name='T';

     BYTES TABLESPACE_NAME
---------- ------------------------------
  83886080 SYSTEM

Elapsed: 00:00:00.01
SQL> select * from v$mystat where statistic# in (134,174);

       SID STATISTIC#           VALUE
---------- ---------- ----------
        38          134  331697708
        38          174        92619368

Elapsed: 00:00:00.00
SQL> delete from t;

767744 rows deleted.

Elapsed: 00:01:19.70
SQL> select * from v$mystat where statistic# in (134,174);

       SID STATISTIC#           VALUE
---------- ---------- ----------
        38          134  767858344
        38          174  324824736

Elapsed: 00:00:00.00

134# (REDO): 767858344-331697708=436160636=415MB
174# (UNDO): 324824736-92619368=232205368=221.45MB

REM: Note that REDO size is about 5 times of the total size of rows deleted , UNDO is about 3 times.
REM Now rollback and monitor further

SQL> rollback;

Rollback complete.

Elapsed: 00:01:08.37
SQL> select * from v$mystat where statistic# in (134,174);

       SID STATISTIC#           VALUE
---------- ---------- ----------
        38          134 1111799660
        38          174  324824736

Elapsed: 00:00:00.00
SQL> select (1111799660-767858344)/1048576 from dual;

(1111799660-767858344)/1048576
------------------------------
                    328.007999

Elapsed: 00:00:00.00

REM observation 1: No more undo generated during roll back
REM observation 2: during the rollback, redo size is about another 4 times of the total size of rows deleted. So total size would be less than 9 times should there be rollback.  

Hence, as good practice, I always assist application teams to monitor the redo size for the very first sample batch housekeep (say 100k rows), and based on the existing archive log detistinatin to advise how many rows to housekeep at one go. Of course,  commit for each tens of thousands rows (to avoid ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'), instead of a big commit in this experiment.

Yong Huang

If you have enough space, one big commit uses less overall resource than many smaller commits. To prevent running out of space in a big transaction, always enable resumable ("alter session enable resumable"). Create a logon trigger if you can inject that command (remember to grant resumable to the user). If it's traditional import, remember to add "resumable=y". Data pump import automatically does it.

Yong Huang