oracle redo产生过快,[分享]Oracle产生多少Redo Size

[分享]Oracle产生多少Redo Size

但凡对数据库修改操作都会记录Redo，那么不同操作会产生多少Redo呢？

查询方式：

方式一：

1.在SQL*Plus 使用 autotrace功能

2.在执行特定DML语句时，Oracle会显示该语句统计信息，其中Redo size 一览表示该操作产生Redo数量

Statistics

----------------------------------------------------------

4  recursive calls

1  db block gets

7  consistent gets

0  physical reads

340  redo size

675  bytes sent via SQL*Net to client

590  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

方式二：

通过v$mystat查询 当前 session 的统计信息，同时也可以查得session 的Redo 生成情况：

col name format a30

_selecta.name,b.value

from v$statname a,v$mystat b

where a.statistic#=b.statistic# and a.name='redo size';

NAME                                VALUE

------------------------------ ----------

redo size                           20112

通过v$sysstat 查得全局Redo 的生成量

col value for 9999999999999999

_selectname,value

from v$sysstat

where name='redo size';

NAME                                VALUE

------------------------------ ----------

redo size                      3139476724

从v$sysstat 查得自数据库实例启动以来累积日志生成量，可以根据实例启动时间来

大致估算每天数据库日志生成量：

alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss';

_selectstartup_time from v$instance;

_select(_selectvalue/1024/1024/1024) from v$sysstat where name='redo size')/

(_selectround(sysdate - (_selectstartup_time from v$instance)) from dual) REDO_GB_PER_DAY

from dual;

REDO_GD_PER_DAY

---------------

.100823532

归档日志生成量，v$archived_log 根据一段时间的归档日志量进行估算：

_selectname,completion_time,blocks*block_size/1024/1024 Mb

from v$archived_log where rownum < 11

and completion_time between trunc(sysdate) - 2 and trunc(sysdate) - 1;

NAME                                          COMPLETION_TIME             MB

--------------------------------------------- ------------------- ----------

/oracle/oradata/archive/1_108_759450376.dbf   2011-09-14 13:00:18 36.9335938

/oracle/oradata/archive/1_109_759450376.dbf   2011-09-14 22:00:23 40.0454102

某日全天日志生成查询计算：

_selecttrunc(completion_time),sum(Mb)/1024 Day_GB

from (_selectname,completion_time,blocks*block_size/1024/1024 Mb

from v$archived_log

where completion_time between trunc(sysdate) - 2 and trunc(sysdate) - 1)

group by trunc(completion_time);

TRUNC(COMPLETION_TI     DAY_GB

------------------- ----------

2011-09-14 00:00:00 .075174809

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

_selecttrunc(completion_time),sum(mb)/1024 day_gb

from (_selectname,completion_time,blocks*block_size/1024/1024 Mb

from v$archived_log)

group by trunc(completion_time);

TRUNC(COMPLETION_TI     DAY_GB

------------------- ----------

2011-08-29 00:00:00 .078902245

2011-09-07 00:00:00 .085594654

2011-09-15 00:00:00  .11264801

2011-08-27 00:00:00 .152146816

2011-09-06 00:00:00  .08170557

2011-08-19 00:00:00 .073926926

2011-09-13 00:00:00 .122589588

2011-09-14 00:00:00 .075174809

综述：根据每日归档的生成量，也可以反过来估计每日的数据库活动性及周期性，并决定空间分配问题(网络摘录2011/9/16)

Oracle Redo Size