1.试验前提
SQL> 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 SQL> select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI, 2 SUPPLEMENTAL_LOG_DATA_MIN from v$database; FOR SUP SUP SUPPLEME --- --- --- -------- NO NO NO NO SQL> conn / as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/archivelog/chf Oldest online log sequence 1879 Next log sequence to archive 1881 Current log sequence 1881 SQL> select count(*) from t_xifenfei_move; COUNT(*) ---------- 7432085 SQL> select bytes/1024/1024 from user_segments where segment_name='T_XIFENFEI_MOVE'; BYTES/1024/1024 --------------- 832
从上面信息可以看到数据库处于归档模式,强制日志和辅助日志为开启,试验测试表t_xifenfei_move有7432085条记录,占用硬盘空间832M
2.常规CAST
SQL> alter system flush buffer_cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> SET TIMING ON SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 100028 undo change vector size 16172 Elapsed: 00:00:00.06 SQL> create table chf.t_xifenfei_move_CAST tablespace users 2 as 3 select * from chf.t_xifenfei_move; Table created. Elapsed: 00:01:58.10 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 873150548 undo change vector size 131384 Elapsed: 00:00:00.01 SQL> select 873150548-100028 "redo size" from dual; redo size ---------- 873050520 SQL> select 131384-16172 "undo size" from dual; undo size ---------- 115212
通过这个可以得出结论,产生redo为873050520,undo为115212
3.普通INSERT
SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:00.06 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.01 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 732 undo change vector size 136 Elapsed: 00:00:00.18 SQL> CREATE TABLE t_xifenfei_move_INSERT 2 AS 3 SELECT * FROM T_XIFENFEI_MOVE WHERE 1=0; Table created. Elapsed: 00:00:00.32 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 22712 undo change vector size 6512 Elapsed: 00:00:00.02 SQL> INSERT INTO t_xifenfei_move_INSERT 2 SELECT * FROM T_XIFENFEI_MOVE; 7432085 rows created. Elapsed: 00:01:59.47 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 862261580 undo change vector size 27980508 --redo SQL> select 22712-732 "create redo size" from dual; create redo size ---------------- 21980 SQL> select 862261580-22712 "insert redo size" from dual; insert redo size ---------------- 862238868 --undo SQL> select 6512-136 "create undo size" from dual; create undo size ---------------- 6376 SQL> select 27980508-6512 "insert undo size" from dual; insert undo size ---------------- 27973996
通过这个可以得出CREATE TABLE 过程中产生redo:21980,undo:6376,而INSERT 过程中产生的redo:862238868,undo:27973996,整个过程总的产生redo:862260848(862238868+21980),undo:27980372(27973996+6376)
4.INSERT+APPEND
SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:25.19 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.04 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 732 undo change vector size 136 Elapsed: 00:00:00.05 SQL> create table chf.t_xifenfei_move_INSERT_A tablespace users 2 as 3 select * from chf.t_xifenfei_move where 1=0; Table created. Elapsed: 00:00:00.18 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 21892 undo change vector size 6308 Elapsed: 00:00:00.00 SQL> INSERT /*+ append */INTO t_xifenfei_move_INSERT_A 2 SELECT * FROM T_XIFENFEI_MOVE; 7432085 rows created. Elapsed: 00:02:26.37 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 872792032 undo change vector size 87764 --redo SQL> select 21892-732 "create redo size" from dual; create redo size ---------------- 21160 SQL> select 872792032-21892 "insert redo size" from dual; insert redo size ---------------- 872770140 --undo SQL> select 6308-136 "create undo size" from dual; create undo size ---------------- 6172 SQL> select 87764-6308 "insert undo size" from dual; insert undo size ---------------- 81456
这个过程可以得到结论,create 表的过程产生的redo:21160,undo:6172;insert 表的过程redo:872770140,undo:81456;整个过程产生的redo:872791300(21160+872770140),undo:87628(6172+81456)
5.INSERT+NOLOGGING
SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:02.21 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.06 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 780 undo change vector size 136 Elapsed: 00:00:00.06 SQL> create table chf.t_xifenfei_move_INSERT_N tablespace users 2 as 3 select * from chf.t_xifenfei_move where 1=0; Table created. Elapsed: 00:00:00.22 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 22056 undo change vector size 6308 Elapsed: 00:00:00.00 SQL> INSERT /*+ NOLOGGING */INTO t_xifenfei_move_INSERT_N 2 SELECT * FROM T_XIFENFEI_MOVE; 7432085 rows created. Elapsed: 00:02:30.33 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 862123984 undo change vector size 27982876 --redo SQL> select 22056-780 "create redo size" from dual; create redo size ---------------- 21276 SQL> select 862123984-22056 "insert redo size" from dual; insert redo size ---------------- 862101928 --undo SQL> select 6308-136 "create undo size" from dual; create undo size ---------------- 6172 SQL> select 27982876-6308 "insert undo size" from dual; insert undo size ---------------- 27976568
这个试验过程得出,create table得到redo:21276,undo:6172,insert table 得到redo:862101928,undo:27976568,整个过程redo:862123204,undo:27982740(27976568+6172)
6.INSERT+NOLOGGING(TABLE)
SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:23.68 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.04 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 800 undo change vector size 136 Elapsed: 00:00:00.18 SQL> create table chf.t_xifenfei_move_INSERT_N_new nologging tablespace users 2 as 3 select * from chf.t_xifenfei_move where 1=0; Table created. Elapsed: 00:00:00.71 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 23296 undo change vector size 6756 Elapsed: 00:00:00.00 SQL> INSERT INTO t_xifenfei_move_INSERT_N_new 2 SELECT * FROM T_XIFENFEI_MOVE; 7432085 rows created. Elapsed: 00:02:37.51 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 862031304 undo change vector size 27982776 SQL> select 23296-800 "create redo size" from dual; create redo size ---------------- 22496 SQL> select 862031304-23296 "insert redo size" from dual; insert redo size ---------------- 862008008 SQL> select 6756-136 "create undo size" from dual; create undo size ---------------- 6620 SQL> select 27982776-6756 "insert undo size" from dual; insert undo size ---------------- 27976020
通过该试验得出,create table 产生redo:22496,undo:6620;insert into 产生redo:862008008,undo:27976020;整个过程产生redo:862030504(22496+862008008),undo:27982640(6620+27976020)
7.APPEND+NOLOGGING(TABLE)
SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:23.59 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.05 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 780 undo change vector size 136 Elapsed: 00:00:00.04 SQL> create table chf.t_xifenfei_move_INSERT_NA nologging tablespace users 2 as 3 select * from chf.t_xifenfei_move where 1=0; Table created. Elapsed: 00:00:00.42 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 22000 undo change vector size 6308 Elapsed: 00:00:00.00 SQL> INSERT /*+APPEND */ INTO t_xifenfei_move_INSERT_NA 2 SELECT * FROM T_XIFENFEI_MOVE; 7432085 rows created. Elapsed: 00:01:08.92 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 602352 undo change vector size 82756 --redo SQL> select 22000-780 "create redo size" from dual; create redo size ---------------- 21220 SQL> select 602352-22000 "insert redo size" from dual; insert redo size ---------------- 580352 --undo SQL> select 6308-136 "create undo size" from dual; create undo size ---------------- 6172 SQL> select 82756-6308 "insert undo size" from dual; insert undo size ---------------- 76448
在这个试验中,create table产生redo:21220,undo:6172,insert into产生redo:580352,undo:76448;整个过程产生的redo:601572(
21220+580352),undo:82620(6172+76448)
8.CAST+NOLOGGING(TABLE)
SQL> alter system flush buffer_cache; System altered. Elapsed: 00:00:03.35 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.04 SQL> SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 732 undo change vector size 136 Elapsed: 00:00:00.05 SQL> create table chf.t_xifenfei_move_cast_N nologging tablespace users 2 as 3 select * from chf.t_xifenfei_move; Table created. Elapsed: 00:00:56.41 SQL> select a.name, b.value 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and lower(a.name) in 5 ('redo size','undo change vector size'); NAME VALUE ---------------------------------------------------------------- ---------- redo size 769848 undo change vector size 124944 SQL> select 769848-732 "redo size" from dual; redo size ---------- 769116 SQL> select 124944-136 "undo size" from dual; undo size ---------- 124808
这个试验产生的redo:769116,undo:124808
9.通过试验得出结论
1)sql hint中的nologgging无效
2)普通的cast(不含hint),其本质是append,无nologgging
3)nologgging(表级别)可以使得cast效率较高
4)nologgging(表级别)+append(hint)可以使得insert效率较高