redo

参考视频：bbk5604

redo的原理

1、理解checkpoint

检查点是一个数据库事件，它存在的意义是减少恢复事件

2、redo特性

重做日志文件是循环使用，至少两组日志，日志切换过程会触发检查点。在检查点完成之前，日志文件不能被重用

3、事务恢复

roll forward、roll back

redo量计算

The more redo you generate,the longer your operations will take,and the slower the entire system will be.

对数据库的修改都会记录redo，那么怎么计算产生的redo呢?

1、通过v$mystat和v$statname视图

SQL> create table t2 as select * from dba_tables;

Table created.

SQL> select a.name,b.value from v$statname a ,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            931588

SQL> insert into t2 select * from t2;

2922 rows created.

SQL> select a.name,b.value from v$statname a ,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           1743520

SQL> select 1743520-931588 from dual;

1743520-931588
--------------
        811932

SQL> rollback;

Rollback complete.

SQL> select count(*) from t2;

  COUNT(*)
----------
      2922

SQL>

View Code

2、使用autotrace

SQL> set autotrace trace stat;
SQL> insert into t2 select * from t2;

2922 rows created.


Statistics
----------------------------------------------------------
          0  recursive calls
        396  db block gets
        801  consistent gets
          0  physical reads
     795112  redo size
        836  bytes sent via SQL*Net to client
        784  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2922  rows processed

SQL>

View Code

3、通过v$archived_log视图计算每天产生的归档日志量

SQL> l
  1  select trunc(completion_time),sum(M) from
  2  ( select name,completion_time,blocks*block_size/1024/1024 M from v$archived_log )
  3* group by trunc(completion_time) order by trunc(completion_time)
SQL> /

TRUNC(COMPLETION_TI     SUM(M)
------------------- ----------
2013/07/09 00:00:00 39.8706055
2013/07/12 00:00:00  63.628418
2013/07/13 00:00:00 202.876465
2013/07/14 00:00:00 113.645508

SQL>

View Code