11g append 和常規insert在logging FORCE_LOGGING產生redo量差異

以前很多資料都有說明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不檢視產生redo的DIFF部分

我們這裡不看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

常規insert比append 在30w記錄時多42804912的redo

278486968 - 235682056=42804912約40M

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

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> 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 總結上述測試

這個方法和第一種方法的不同之處在於使用2個session，分別按照先append後常規insert和先常規inert後append的順序進行測試。

append產生redo： 235862932 見2.2.3

常規insert產生redo：278483988 見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 測試常規insert和append 產生的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產生undo：81857572

append 產生undo：57565792

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/，如需轉載，請註明出處，否則將追究法律責任。

11g append 和常規insert在logging FORCE_LOGGING產生redo量差異

1.2. 檢視當前redo

1.3. 測試append插入資料

1.4. 檢視append產生redo

1.5不檢視產生redo的DIFF部分

1.6 常規insert插入資料

1.7 檢視常規insert產生redo

1.8 總結上述測試

2 測試方法二

2.1.1 檢視資料庫force_logging

2.1.2 檢視錶的logging屬性

2.2.1 查詢當前redo

2.2.2 測試append產生redo

2.2.3 檢視append產生redo

2.2.4 測試常規insert產生redo

2.2.5 檢視常規insert產生redo

2.3 在另外一個session進行相反測試

2.3.1 查詢當前redo

2.3.2 測試常規insert產生redo

2.3.3 查詢常規insert產生redo

2.3.4 檢視commit產生的redo

2.4 總結上述測試

3 測試方法三

3.1 設定執行計劃只顯示統計部分

3.2 測試常規insert產生redo

3.3 回滾常規insert操作

3.4 測試append產生redo

4 測試常規insert和append 產生的undo

4.2 測試常規insert產生undo

4.4 開啟另外一個session測試append，檢視當前undo

5 tom大師指令碼

相關文章