当然这里讨论的是一般情况.

一般来说insert产生的UNDO最少,因为ORACLE只需记录要 “删除”一个ROWID

UPDATE一般排名第2,对于UPDATE操作,只需记录修改的字节.UNDO中会记录一小部分.和修改量有关

DELETE一般生成的最多,对于DELETE操作,ORACLE必须把整行前映象记录到UNDO段中,

回家继续…..

块清除即删除所修改数据库块上与锁定有关的信息.常见错误 ORA-01555:snapshot too old变它有关.

如果我们修改的块没有超过块缓冲区大小的10% ,而且块仍在缓冲中并且可用的,ORACLE就会在COMMIT时,清理这些块.

否则,它会忽略

Read More »

DML操作            AFTER触发器(10G前)     BEFORE触发器(10G以前)    AFTER触发器(10G)     BEFORE触发器(10G) 

DELETE             不影响                                 不影响                                       不影响                             不影响

INSERT             增加REDO                            增加REDO                                常量REDO                      常量REDO

UPDATE           增加REDO                             不影响                                    增加REDO                          增加REDO

在开发的时候可以考虑下

CREATE OR REPLACE PROCEDURE do_work (p_what IN VARCHAR2)
AS
   l_redo_size   NUMBER;
   l_cnt         NUMBER := 200;

   PROCEDURE report (l_op IN VARCHAR2)
   IS
   BEGIN
      SELECT v$mystat.VALUE - l_redo_size
        INTO l_redo_size
        FROM v$mystat, v$statname
       WHERE v$mystat.statistic# = v$statname.statistic#
         AND v$statname.NAME = ‘redo size’;

      DBMS_OUTPUT.put_line (   l_op
                            || ‘ redo size = ‘
                            || l_redo_size
                            || ‘ rows = ‘
                            || l_cnt
                            || ‘ ‘
                            || TO_CHAR (l_redo_size / l_cnt, ‘99,999.9′)
                            || ‘ bytes/row’
                           );

      INSERT INTO LOG
         SELECT p_what, l_op, data_length, l_redo_size, l_cnt
           FROM user_tab_columns
          WHERE table_name = ‘T’ AND column_name = ‘Y’;
   END;

   PROCEDURE set_redo_size
   AS
   BEGIN
      SELECT v$mystat.VALUE
        INTO l_redo_size
        FROM v$mystat, v$statname
       WHERE v$mystat.statistic# = v$statname.statistic#
         AND v$statname.NAME = ‘redo size’;
   END;
BEGIN
   set_redo_size;

   INSERT INTO t
      SELECT object_id, object_name, created
        FROM all_objects
       WHERE ROWNUM <= l_cnt;

   l_cnt := SQL%ROWCOUNT;
   COMMIT;
   report (’insert’);
   set_redo_size;

   UPDATE t
      SET y = LOWER (y);

   l_cnt := SQL%ROWCOUNT;
   COMMIT;
   report (’update’);
   set_redo_size;

   DELETE FROM t;

   l_cnt := SQL%ROWCOUNT;
   COMMIT;
   report (’delete’);
END;

Redo Byte Address (RBA)

Recent entries in the redo thread of an Oracle instance are addressed using a 3-part redo byte address, or RBA. An RBA is comprised of

• the log file sequence number (4 bytes)
• the log file block number (4 bytes)
• the byte offset into the block at which the redo record starts (2 bytes)
• Read More »