sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库redo wastage案例详解

时间:2016-12-01 22:36   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:看oracle core 第6章时,里面提到了redo wastage的概念,针对该概念,以前也有所了解,不过并不是太清楚,今天正好简单记录一下。

SQL>   SELECT name,VALUE FROM v$sysstat WHERE name LIKE '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                       264
redo synch TIME                                                         344
redo blocks READ FOR recovery                                         25479
redo entries                                                        2158328
redo SIZE                                                         605152164
redo buffer allocation retries                                           46
redo wastage                                                        1357540
redo writer latching TIME                                                16
redo writes                                                            5050
redo blocks written                                                 1225607
redo WRITE TIME                                                        5262

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log SPACE requests                                                  35
redo log SPACE wait TIME                                                177
redo log switch interrupts                                                0
redo ordering marks                                                       4
redo subscn MAX counts                                                    0
redo WRITE broadcast ack TIME                                             0

17 ROWS selected.

SQL>   CREATE TABLE t AS SELECT * FROM dba_objects;
TABLE created.

SQL>   SELECT name,VALUE FROM v$sysstat WHERE name LIKE '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                       265
redo synch TIME                                                         344
redo blocks READ FOR recovery                                         25479
redo entries                                                        2159471
redo SIZE                                                         611007860
redo buffer allocation retries                                           46
redo wastage                                                        1358616
redo writer latching TIME                                                16
redo writes                                                            5059
redo blocks written                                                 1237416
redo WRITE TIME                                                        5266

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log SPACE requests                                                  35
redo log SPACE wait TIME                                                177
redo log switch interrupts                                                0
redo ordering marks                                                       4
redo subscn MAX counts                                                    0
redo WRITE broadcast ack TIME                                             0

17 ROWS selected.

SQL>   SELECT a.name,b.VALUE
  2  FROM v$statname a ,v$mystat b
  3  WHERE a.statistic#=b.statistic# AND a.name LIKE '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         5
redo synch TIME                                                           0
redo blocks READ FOR recovery                                             0
redo entries                                                          12561
redo SIZE                                                          14319932
redo buffer allocation retries                                            0
redo wastage                                                              0
redo writer latching TIME                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo WRITE TIME                                                           0

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log SPACE requests                                                   0
redo log SPACE wait TIME                                                  0
redo log switch interrupts                                                0
redo ordering marks                                                       0
redo subscn MAX counts                                                    0
redo WRITE broadcast ack TIME                                             0

17 ROWS selected.

SQL>   BEGIN
  2    FOR i IN 1 .. 100 loop
  3      DELETE FROM t WHERE rownum  SELECT name,VALUE FROM v$sysstat WHERE name LIKE '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                       266
redo synch TIME                                                         344
redo blocks READ FOR recovery                                         25479
redo entries                                                        2159888
redo SIZE                                                         611345536
redo buffer allocation retries                                           46
redo wastage                                                        1366480
redo writer latching TIME                                                16
redo writes                                                            5097
redo blocks written                                                 1238113
redo WRITE TIME                                                        5270

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log SPACE requests                                                  35
redo log SPACE wait TIME                                                177
redo log switch interrupts                                                0
redo ordering marks                                                       4
redo subscn MAX counts                                                    0
redo WRITE broadcast ack TIME                                             0

17 ROWS selected.

SQL>   SELECT a.name, b.VALUE
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4     AND a.name LIKE '%redo%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch writes                                                         6
redo synch TIME                                                           0
redo blocks READ FOR recovery                                             0
redo entries                                                          12925
redo SIZE                                                          14652304
redo buffer allocation retries                                            0
redo wastage                                                              0
redo writer latching TIME                                                 0
redo writes                                                               0
redo blocks written                                                       0
redo WRITE TIME                                                           0

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo log SPACE requests                                                   0
redo log SPACE wait TIME                                                  0
redo log switch interrupts                                                0
redo ordering marks                                                       0
redo subscn MAX counts                                                    0
redo WRITE broadcast ack TIME                                             0

17 ROWS selected.
------Redo wastage size (bytes)

SQL>   select (1366480-1358616) from dual;

(1366480-1358616)
-----------------
             7864

---Redo log size (bytes)

SQL>   select 14652304-14319932 from dual;  ---session级别

14652304-14319932
-----------------
           332372

SQL>   select 611345536-611007860 from dual; ---系统级别

611345536-611007860
-------------------
             337676

SQL>       

对于多出的redo log bytes,其实就是递归操作产生的。      

SQL>   select 7864/337676 from dual;

7864/337676
-----------
.023288596

我们可以发现,此时redo log 浪费的比例约为2.3%。此时的redo log block_size为512 bytes。
SQL>   conn /AS sysdba
Connected.
SQL>   SELECT MAX(lebsz) FROM x$kccle;

MAX(LEBSZ)
----------
       512

SQL>   SELECT * Oracleoracleplus.netFROM v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        286   52428800          1 NO  CURRENT                3411998 17-JUN-12
         2          1        284   52428800          1 YES INACTIVE               3407009 17-JUN-12
         3          1        285   52428800          1 YES INACTIVE               3410228 17-JUN-12

SQL>   SELECT * FROM v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /home/ora10g/oradata/roger/redo03.log                        NO
         2         ONLINE  /home/ora10g/oradata/roger/redo02.log                        NO
         1         ONLINE  /home/ora10g/oradata/roger/redo01.log                        NO

SQL>   ALTER SYSTEM DUMP LOGFILE '/home/ora10g/oradata/roger/redo01.log';

System altered.

-----trace如下:
DUMP OF REDO FROM FILE '/home/ora10g/oradata/roger/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
        Compatibility Vsn = 169870592=0xa200500
        Db ID=2466925865=0x930a4d29, Db Name='ROGER'
        Activation ID=2470475068=0x9340753c
        Control Seq=4397=0x112d, File SIZE=102400=0x19000
        File NUMBER=1, Blksiz=512, File TYPE=2 LOG

这里部分内容其实是redo log 的头部,一共16个bytes,所以我这里50m的redo log,其实可以用的空间大概如下:

SQL>    SELECT 50*1024*1024 - (50*1024*1024*0.023 + 102400*16) FROM dual;

50*1024*1024-(50*1024*1024*0.023+102400*16)
-------------------------------------------
                                 49584537.6

SQL>   SELECT  49584537.6/52428800 FROM dual;

49584537.6/52428800
-------------------
             .94575

从上来看来,其实对于50m的redo log,实际上可用的空间的比例约为94.5左右,同理,对于archivelog 也是一样的道理,对于产生的50m的archive log而言,里面有5.5m是空洞。

为什么会产生redo wastage的情况下,可用这样简单的理解:

oracle 写log buffer内容到disk的过程,仍然是以block为单位的,redo log block size为512 bytes,也就是说,oracle每次写都是block size的整数倍,如果说某个时刻,lgwr进程在写某个block,仅仅写了400个bytes,此时事务commit或者发现redo log 切换,该block将被写入到redo log file中,而此时该block中还有112 个bytes是空的。

针对这类的redo wastage 浪费比例计算,我这样计算其实并不准确,严格来讲,应该计算多天的平均值。

在oracle core第6章中,L大师提到,对于block size更大的情况,redo wastage可能也就更为严重,但是redo logblock大小不是我们能够决定的,是由操作系统本身决定的,针对不同的平台,redo log block size不同,如下表:

oracle 为什么要这样设计,L大师是这样简单总结了一句话:
So Oracle’s strategy is simple: move the pointer to the end of the block, write the block, never read,
and never look back. The aim is to keep the code as simple as possible and keep streaming the log to disk
without interruptions.

总的来说,只有redo wastage 比较严重的情况下,我们才会加以关注。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库redo wastage案例详解

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1354.html

Oracle研究中心

关键词:

深入研究redo wastage笔记

Oracle redo wastage