ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11g append 和常规insert在logging FORCE_LOGGING产生redo量差异

11g append 和常规insert在logging FORCE_LOGGING产生redo量差异

原创 Linux操作系统 作者:cnaning 时间:2013-02-05 14:27:49 0 删除 编辑

以前很多资料都有说明append+nologging会减少redo产生,对于这点是毋庸置疑的,但是append+logging模式特别是在归档模式下,大家都不建议这么使用,因为作用不大,都说产生的redo不会减少,这么做只是速度会加快,因为直接路径写不经过数据缓存直接写入数据文件。

通过下面的测试,相信大家会对上述观点有所改观:

1.1.  查看数据库版本信息

 

select * from v$version

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0  Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

注:

1.2.  查看当前redo

SQL>  @d:\mystat "redo size"

Cannot SET AUTOTRACE

NAME                                                                  VALUE

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

redo size                                                                 0

redo size for lost write detection                                   0

redo size for direct writes                                           0

 

Executed in 0.047 seconds

1.3.  测试append插入数据

insert  /*+ append */  into test.APPEND_TEST_HIS select * from test.APPEND_TES

302186 rows inserted

Executed in 25.491 seconds

 

commit

Commit complete

Executed in 0.062 seconds

 

1.4.  查看append产生redo

SQL> @d:\mystat2

set echo off

NAME                                           V           DIFF

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

redo size                                   235682056      235,682,056

redo size for lost write detection             0                0

redo size for direct writes             113653100      113,653,100

 

Executed in 0.047 seconds

 

1.5不查看产生redoDIFF部分

我们这里不看diff部分,因为11g多了redo size for direct writes这部分导致diff部分不准确,我们只查看value部分即可

SQL> set echo off

set echo off

SQL> set verify off

SQL> select a.name,b.value V

  2  from v$statname a,v$mystat b

  3  where a.statistic#=b.statistic#

  4  and lower(a.name) =lower('&S')

  5  /

 

NAME                                           VALUE

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

redo size                                     235682056

redo size for lost write detection              0

redo size for direct writes                113653100

 

Executed in 0.062 seconds

 

1.6 常规insert插入数据

insert into test.APPEND_TEST_TMP select * from test.APPEND_TEST

302186 rows inserted

Executed in 23.291 seconds

 

commit

Commit complete

Executed in 0.016 seconds

 

1.7 查看常规insert产生redo

SQL> @d:\mystat2

set echo off

 

NAME                                              V               DIFF

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

redo size                                       514169024      400,515,924

redo size for lost write detection                0       -113,653,100

redo size for direct writes                 113653100                0

 

Executed in 0.063 seconds

 

1.8 总结上述测试

常规insert产生的redo量:

514169024 - 235682056=278486968

常规insertappend 30w记录时多42804912redo

278486968 - 235682056=4280491240M

 

2 测试方法二

上述方法测试过程及结果不是很明显,下面我们换个方法测试

2.1 检查测试环境

2.1.1 查看数据库force_logging

select FORCE_LOGGING from v$database

FORCE_LOGGING

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

YES

 

2.1.2 查看表的logging属性

这三张表的表结构完全一样,为了方便测试

select table_name,logging from dba_tables where table_name=upper('&tname')

TABLE_NAME            LOGGING

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

APPEND_TEST             YES

Executed in 0.094 seconds

 

select table_name,logging from dba_tables where table_name=upper('&tname')

TABLE_NAME         LOGGING

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

APPEND_TEST_HIS      YES

Executed in 0.109 seconds

 

select table_name,logging from dba_tables where table_name=upper('&tname')

 TABLE_NAME        LOGGING

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

 APPEND_TEST_TMP    YES

 APPEND_TEST_TMP    YES

 APPEND_TEST_TMP    YES

Executed in 0.078 seconds

 

 

2.2 下面测试redo输出

select '下面重新测试,只 输出redo size' from dual

'下面重新测试,只输出REDOSIZE'

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

下面重新测试,只 输出redo size

Executed in 0.047 seconds

 

 

2.2.1 查询当前redo

SQL> set echo off

SQL> set verify off

SQL> column value new_val V

SQL> define S="&1"

SQL> set autotrace off

Cannot SET AUTOTRACE

SQL> select a.name,b.value from v$statname a,v$mystat b

  2  where a.statistic#=b.statistic#

  3  and lower(a.name) =lower('&S')

  4  /

 

NAME                         VALUE

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

redo size                        0

 

2.2.2 测试append产生redo

SQL> set echo on;

SQL> set timing on;

SQL>

SQL>

SQL> insert  /*+ append */  into test.APPEND_TEST_HIS select * from test.APPEND_TEST;

302186 rows inserted

Executed in 12.542 seconds

 

 

2.2.3 查看append产生redo

SQL> set echo off

set echo off

SQL> set verify off

SQL> select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff

  2  from v$statname a,v$mystat b

  3  where a.statistic#=b.statistic#

  4  and lower(a.name) =lower('&S')

  5  /

 

NAME                               V              DIFF

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

redo size                     235862932      235,862,932

 

Executed in 0.031 seconds

SQL> set echo on

SQL> commit;

Commit complete

Executed in 0 seconds                                         

 

2.2.4 测试常规insert产生redo

SQL> insert into test.APPEND_TEST_TMP select * from test.APPEND_TEST;

302186 rows inserted

Executed in 9.298 seconds

 

2.2.5 查看常规insert产生redo

SQL> set echo off

set echo off

SQL> set verify off

SQL> select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff

  2  from v$statname a,v$mystat b

  3  where a.statistic#=b.statistic#

  4  and lower(a.name) =lower('&S')

  5  /

 

NAME                                    V           DIFF

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

redo size                           513956464      513,956,464

 

Executed in 0.031 seconds

SQL> set echo on

SQL> commit;

Commit complete

Executed in 0.015 seconds

 

2.3 在另外一个session进行相反测试

select '下面重新测试,在另外一个session 输出redo size' from dual

'下面重新测试,在另外一个SESSI

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

下面重新测试,在另外一个session 输出redo size'

Executed in 0.046 seconds

 

2.3.1 查询当前redo

SQL> set echo off

SQL> set verify off

SQL> column value new_val V

SQL> define S="&1"

SQL> set autotrace off

Cannot SET AUTOTRACE

SQL> select a.name,b.value from v$statname a,v$mystat b

  2  where a.statistic#=b.statistic#

  3  and lower(a.name) like '%'||lower('&S')||'%'

  4  /

 

NAME                                           VALUE

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

redo size                                       0

redo size for lost write detection             0

redo size for direct writes                     0

SQL> set echo on;

 

2.3.2 测试常规insert产生redo

SQL> set timing on;

SQL> insert into test.APPEND_TEST_TMP select * from test.APPEND_TEST;

302186 rows inserted

Executed in 20.248 seconds

 

2.3.3 查询常规insert产生redo

SQL> set echo off

set echo off

SQL> set verify off

SQL> select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff

  2  from v$statname a,v$mystat b

  3  where a.statistic#=b.statistic#

  4  and lower(a.name) =lower('&S')

  5  /

 

NAME                                   V               DIFF

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

redo size                          278483988      278,483,988

 

Executed in 0.031 seconds

SQL> set echo on

SQL> commit;

Commit complete

Executed in 0.016 seconds

 

2.3.4 查看commit产生的redo

SQL> set echo off

set echo off

SQL> set verify off

SQL> select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff

  2  from v$statname a,v$mystat b

  3  where a.statistic#=b.statistic#

  4  and lower(a.name) like '%'||lower('&S')||'%'

  5  /

 

NAME                                          V            DIFF

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

redo size                                   278484224      278,484,224

redo size for lost write detection           0               0

redo size for direct writes                   0                0

 

Executed in 0.047 seconds

 

2.4 总结上述测试

这个方法和第一种方法的不同之处在于使用2session,分别按照先append后常规insert和先常规inertappend的顺序进行测试。

append产生redo 235862932   2.2.3

常规insert产生redo278483988 2.3.3

278483988 - 235862932=42621056  40M

 

3 测试方法三

通过sqlplus 执行计划测试

3.1 设置执行计划只显示统计部分

SQL> set autotrace traceonly statistics

 

3.2 测试常规insert产生redo

SQL> insert into test.APPEND_TEST_TMP select * from test.APPEND_TEST;

302186 rows created.

 

Statistics

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

       1567  recursive calls

     491798  db block gets

      63503  consistent gets

       1805  physical reads

  278164024  redo size

        828  bytes sent via SQL*Net to client

        846  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

     302186  rows processed

 

3.3 回滚常规insert操作

SQL> SQL> rollback;

 

Rollback complete.

 

3.4 测试append产生redo

SQL> insert  /*+ append */  into test.APPEND_TEST_HIS select * from test.APPEND_TEST;

302186 rows created.

 

Statistics

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

       1468  recursive calls

     105783  db block gets

      23176  consistent gets

          5  physical reads

  235648412  redo size

        824  bytes sent via SQL*Net to client

        862  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

     302186  rows processed

 

SQL> SQL> commit;

Commit complete.

 

4 测试常规insertappend 产生的undo

通过上述测试可以确定append产生redo会比常规insert产生redo少,那undo会不会少呢?

让我们一起来测试吧

4.1 查看当前undo

SQL>  @d:\mystat "undo"

Cannot SET AUTOTRACE

 

NAME                                                                        VALUE

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

DBWR undo block writes                                                    0

undo change vector size                                                   0

transaction tables consistent reads - undo records applied         0

data blocks consistent reads - undo records applied                 0

rollback changes - undo records applied                                0

auto extends on undo tablespace                                          0

total number of undo segments dropped                                   0

global undo segment hints helped                                         0

global undo segment hints were stale                                    0

local undo segment hints helped                                           0

local undo segment hints were stale                                     0

undo segment header was pinned                                            0

IMU undo retention flush                                                  0

IMU undo allocation size                                                  0

SMON posted for undo segment recovery                                   0

SMON posted for undo segment shrink                                     0

 

16 rows selected

 

4.2 测试常规insert产生undo

SQL> insert into test.APPEND_TEST_TMP select * from test.APPEND_TEST;

 

302186 rows inserted

 

4.3 查看常规insert产生undo

SQL> @d:\mystat2

set echo off

 

NAME                                                                         V

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

DBWR undo block writes                                                    0

undo change vector size                                                  81857572

transaction tables consistent reads - undo records applied          0

data blocks consistent reads - undo records applied                   0

rollback changes - undo records applied                                 6

auto extends on undo tablespace                                           0

total number of undo segments dropped                                    0

global undo segment hints helped                                          0

global undo segment hints were stale                                      0

local undo segment hints helped                                           0

local undo segment hints were stale                                       0

undo segment header was pinned                                             0

IMU undo retention flush                                                    0

IMU undo allocation size                                                    0

SMON posted for undo segment recovery                                    0

SMON posted for undo segment shrink                                      0

                                                            

16 rows selected                                           

                                                           

SQL> commit;

Commit complete

 

4.4 开启另外一个session测试append,查看当前undo

SQL> @d:\mystat "undo"

Cannot SET AUTOTRACE

 

NAME                                                                         VALUE

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

DBWR undo block writes                                                    0

undo change vector size                                                   0

transaction tables consistent reads - undo records applied          0

data blocks consistent reads - undo records applied                   0

rollback changes - undo records applied                                   0

auto extends on undo tablespace                                           0

total number of undo segments dropped                                     0

global undo segment hints helped                                          0

global undo segment hints were stale                                      0

local undo segment hints helped                                           0

local undo segment hints were stale                                       0

undo segment header was pinned                                            0

IMU undo retention flush                                                  0

IMU undo allocation size                                                  0

SMON posted for undo segment recovery                                     0

SMON posted for undo segment shrink                                       0

 

16 rows selected

 

4.5 测试append产生undo

SQL> insert  /*+ append */  into test.APPEND_TEST_HIS select * from test.APPEND_TEST;

 

302186 rows inserted

 

 

4.6 查看append产生undo

SQL> @d:\mystat2

set echo off

 

NAME                                                                     V

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

DBWR undo block writes                                               0

undo change vector size                                              57565792

transaction tables consistent reads - undo records applied     0

data blocks consistent reads - undo records applied              0

rollback changes - undo records applied                            12

auto extends on undo tablespace                                      0

total number of undo segments dropped                               0

global undo segment hints helped                                    0

global undo segment hints were stale                                0

local undo segment hints helped                                     0

local undo segment hints were stale                                 0

undo segment header was pinned                                      0

IMU undo retention flush                                              0

IMU undo allocation size                                              0

SMON posted for undo segment recovery                               0

SMON posted for undo segment shrink                                 0

 

16 rows selected

 

SQL> commit;

 

Commit complete

 

4.7 测试结果总结

常规insert产生undo81857572

append 产生undo57565792

81857572 - 57565792=24291780  23M

 

5 tom大师脚本

5.1 mystat.sql

set echo off

set verify off

column value new_val V

define S="&1"

 

 

set autotrace off

select a.name,b.value from v$statname a,v$mystat b

where a.statistic#=b.statistic#

and lower(a.name) like '%'||lower('&S')||'%'

/

set echo on;

 

5.2 mystat2.sql

set echo off

set verify off

 

select a.name,b.value V,to_char(b.value-&V,'999,999,999,999') diff

from v$statname a,v$mystat b

where a.statistic#=b.statistic#

and lower(a.name) like '%'||lower('&S')||'%'

/

 

set echo on

 

 

5.3 使用方法

例如查看delete 产生redo

 

SQL>  @d:\mystat "redo size"

Cannot SET AUTOTRACE

NAME                                                                  VALUE

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

redo size                                                                 0

Executed in 0.047 seconds

 

sys>delete from dual;

1 row deleted.

 

SQL> @d:\mystat2

set echo off

NAME                                           V           DIFF

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

redo size                                   587          587

Executed in 0.047 seconds

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12457158/viewspace-753936/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-12

  • 博文量
    36
  • 访问量
    217383