作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


产生多少Redo:

对于数据库的修改操作都会记录Redo,那么不同的操作会产生多少Redo呢?可以通过以下一些方式来查询。
⑴ SQL*Plus中使用AUTOTRACE的功能的。
当在SQL*Plus中启用Autotrace跟踪后,在执行了特定的DML语句时,Oracle会显示该语句的统计信息,其中,Redo Size一栏表示的就是该操作产生的Redo的数量,其单位为Bytes:

dbtan@NEI> set autotrace traceonly statistics
dbtan@NEI> insert into emp select * from emp;
32 rows created.
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          8  consistent gets
          0  physical reads
       4052  redo size

⑵ 通过v$mystat查询。
Oracle通过v$mystat视图记录当前Session的统计信息,我们也可以从该视图中查询得到Session的Redo生成情况:

dbtan@NEI> col name for a30
dbtan@NEI> select a.name,b.value from v$statname a,v$mystat b        
  2  where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                                 VALUE
------------------------------ -----------
redo size                             4052
dbtan@NEI> insert into emp select * from emp;
64 rows created.
dbtan@NEI> select a.name,b.value from v$statname a,v$mystat b
  2  where a.statistic# = b.statistic# and a.name = 'redo size';
NAME                                 VALUE
------------------------------ -----------
redo size                             7424
dbtan@NEI> select 7424-4052 from dual;
7424-4052
----------
      3372

⑶ 通过v$sysstat查询。
对于数据库全局Redo的生成量,可以通过v$sysstat视图来查询得到:

sys@NEI> col name for a40
sys@NEI> col value for 999999999999
sys@NEI> select name,value from v$sysstat where name = 'redo size';
NAME                                             VALUE
---------------------------------------- -------------
redo size                                    216277788

v$sysstat视图中得到的是自数据库实例启动以来的累积日志生成量,可以根据实例启动时间大致估算每天数据库的日志生成量:

sys@CCDB> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sys@CCDB> select startup_time from v$instance;
STARTUP_TIME
-------------------
2009-12-10 13:55:27
sys@CCDB> select
  2    (select value/1024/1024/1024 from v$sysstat where name='redo size'
  3    )/
  4    (select round(sysdate-
  5      (select startup_time from v$instance
  6      )) from dual
  7    ) redo_gb_per_day
  8  from dual;
REDO_GB_PER_DAY
---------------
     1.28679358

如果数据库运行在归档模式下,由于其他因素的影响,以上Redo生成量并不代表归档日志的大小,但是可以通过一定的加权提供参考。

至于归档日志的生成量,可以通过v$archived_log视图,根据一段时间的归档日志量进行估算得到。该视图中记录了归档日志的主要信息:

sys@CCDB> select name,completion_time,blocks*block_size/1024/1024 MB
  2  from v$archived_log where status = 'A' and rownum <11;
NAME                                                         COMPLETION_TIME             MB
------------------------------------------------------------ ------------------- ----------
/databak/ARCHIVELOG/ARC0000003083_0689837452.0001            2009-12-14 08:02:40 44.4682617
/databak/ARCHIVELOG/ARC0000003084_0689837452.0001            2009-12-14 08:30:03 44.4682617
/databak/ARCHIVELOG/ARC0000003085_0689837452.0001            2009-12-14 08:35:42 44.4682617
/databak/ARCHIVELOG/ARC0000003086_0689837452.0001            2009-12-14 09:32:28 44.4682617
/databak/ARCHIVELOG/ARC0000003087_0689837452.0001            2009-12-14 10:26:14 44.4682617
/databak/ARCHIVELOG/ARC0000003088_0689837452.0001            2009-12-14 11:09:13 44.4682617
/databak/ARCHIVELOG/ARC0000003089_0689837452.0001            2009-12-14 11:54:38 44.4682617
/databak/ARCHIVELOG/ARC0000003090_0689837452.0001            2009-12-14 12:38:43 44.4682617
/databak/ARCHIVELOG/ARC0000003091_0689837452.0001            2009-12-14 13:13:16 44.4619141
/databak/ARCHIVELOG/ARC0000003092_0689837452.0001            2009-12-14 14:03:23 44.4702148
10 rows selected.

某日全天的日志生成可以通过如下查询计算:

sys@CCDB> select trunc(completion_time),
  2    sum(Mb)/1024 DAY_GB
  3  from
  4    (select name,
  5      completion_time,
  6      blocks*block_size/1024/1024 Mb
  7    from v$archived_log
  8    where completion_time between trunc(sysdate)-2 and trunc(sysdate)-1
  9    )
10  group by trunc(completion_time);
TRUNC(COMPLETION_TI     DAY_GB
------------------- ----------
2009-12-12 00:00:00 1.26909781

最近日期的日志生成统计:

sys@CCDB> select trunc(completion_time),
  2    sum(mb)/1024 day_gb
  3  from
  4    (select name,
  5      completion_time,
  6      blocks*block_size/1024/1024 mb
  7    from v$archived_log
  8    )
  9  group by trunc(completion_time);
TRUNC(COMPLETION_TI     DAY_GB
------------------- ----------
2009-12-11 00:00:00 1.33388376
2009-12-13 00:00:00 1.22696066
2009-12-14 00:00:00 .904955387
2009-12-10 00:00:00 .779622078
2009-12-12 00:00:00 1.26909781

根据每日归档的生成量,我们也可以反过来估计每日的数据库活动性及周期性,并决定空间分配等问题。

- The End -