以前很多资料都有说明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
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 总结上述测试
这个方法和第一种方法的不同之处在于使用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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12457158/viewspace-753936/
259
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
