Chinaunix首页 | 论坛 | 博客

wilson_oracleoracleeason.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

oracleeason

  • 博客访问： 550337
  • 博文数量： 65
  • 博客积分： 1158
  • 博客等级： 少尉
  • 技术积分： 1261
  • 用 户 组： 普通用户
  • 注册时间： 2012-07-18 22:07
文章分类

全部博文（65）

文章存档

2016年（1）

2014年（2）

2013年（9）

2012年（53）

我的朋友
最近访客
推荐博文
相关博文
Oracle非归档模式下插入数据到nologging表和logging表产生的undo信息比较

分类： Oracle

2012-11-18 19:34:08

一首先看我自己的实验环境是处于哪种归档模式：

SQL> select t.LOG_MODE from v$database t;
 
LOG_MODE
------------
NOARCHIVELOG

二.确定是在非归档模式下之后，我首先创建一个常规的表t_test_nologging

1.建表，所谓常规就是在创建过程中不指定这个表是nologging

SQL> create table t_test_nologging as select * from t_down_log where 1=0;
 
Table created
 
SQL> select count(*) from t_down_log;
 
  COUNT(*)
----------
    660618

2.常规地插入t_test_nologging所有的t_down_log记录
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                         CUR_SIZE_KB
---------------------------------------------------------------- ------------- ------
redo size                    38884.543KB

--插入t_down_log所有的记录
SQL> insert into t_test_nologging select * from t_down_log;
 
660618 rows inserted
 
SQL> commit;
 
Commit complete
 
--再次计算undo大小
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                       CUR_SIZE_KB
--------------------------------------------------------------- ------
redo size                  77660.121KB
 
SQL> select 77660.121 - 38884.543 from dual；  
77660.121-38884.543
-------------------
          38775.578

上面插入的方式产生了38775.578KB的undo


--按照insert append方式插入的时候
SQL> insert /*+ append */ into t_test_nologging select * from t_down_log;
 
660618 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                CUR_SIZE_KB
-----------------------------------------------------
redo size           77771.629KB
 
SQL> select 77771.629 - 77660.121 from dual;
 
77771.629-77660.121
-------------------
            111.508
 
用insert /*+ append */ into 却只产生了111.508KB的undo表空间

可以得出结论，在非归档模式下如果采用insert /*+ append */ into的方式给非nologging表插入数据的时候产生的undo数据是要少于直接用insert into方式插入的。



三.在非归档模式下之后，我们将上面创建的那张表设置为nologging表

SQL> alter table t_test_nologging nologging;
 
Table altered
 
SQL>
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                当前undo大小(单位KB)
-----------------------------------------
redo size            77773.738KB
 
SQL> insert into t_test_nologging select * from t_down_log;
 
660618 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                        当前undo大小(单位KB)
-----------------------------------------------------------------
redo size                     116393.234KB
 
SQL> select 116393.234 -  77773.738 || 'KB' from dual;
 
116393.234-77773.738||'KB'
--------------------------
38619.496KB
 
SQL> insert /*+ append */ into t_test_nologging select * from t_down_log;
 
660618 rows inserted
 
SQL>
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                                                             当前undo大小(单位KB)
---------------------------------------------------------------- ------------------------------------------
redo size                                                        116417.566KB
 
 
SQL> commit;
 
Commit complete
 
SQL> select 116417.566 - 116393.234 || 'KB' from dual;

116417.566-116393.234||'KB'
---------------------------
24.332KB

上面实验可知尽管我把这张表改为了nologging模式，但还是只能通过insert /*+ append */ into的方式使得产生的undo减少。

四.直接将t_test_nologging干掉，然后建立一张新的名字也为t_test_nologging的nologging表

SQL> drop table t_test_nologging purge;
 
Table dropped
 
SQL> create table t_test_nologging nologging as select * from t_down_log where 1 = 0;
 
Table created
 
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                            当前undo大小(单位KB)
---------------------------------------------------------------- 
redo size                        155256.492KB
 
SQL> insert into t_test_nologging select * from t_down_log;
 
660618 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME              当前undo大小(单位KB)
------------------------------- ------------------------------------------
redo size           194108.301KB
 
SQL> select 194108.301 - 155364.371 || 'KB' from dual;
 
194108.301-155364.371||'KB'
---------------------------
38743.93KB

--尽管已经是nologging状态的表了，但是在非归档模式下的直接insert时还是会产生大量的redo信息
 
SQL> truncate table t_test_nologging;
 
Table truncated
 
SQL> insert /*+ append */ into t_test_nologging select * from t_down_log;
 
660618 rows inserted
 
SQL> commit;
 
Commit complete

SQL>
SQL> SELECT a.NAME,round(b.VALUE / 1024, 3)||'KB' "当前undo大小(单位KB)"
  2  FROM v$statname a, v$mystat b
  3  WHERE a.statistic# = b.statistic#
  4  AND lower(a.NAME) LIKE '%' || lower('redo size') || '%';
 
NAME                 当前undo大小(单位KB)
---------------------------------------------------------
redo size             194397.07KB
 
SQL> select 194397.07 - 194108.301 || 'KB' from dual;
 
194397.07-194108.301||'KB'
--------------------------
288.769KB

--处于非归档模式下的unlogging表也只能在insert /*+ append */ into的情况下才能减少undo的产生。


结论：综上实验，对于非归档模式，不管表的状态是nologging还是logging都只能在insert /*+ append */ into的情况下才能减少undo信息的产生。


--附上查看表是不是logging的sql，以及改变logging方式的sql
SQL> select logging from user_tables where table_name = 'T_TEST_NOLOGGING';
 
LOGGING
-------
NO
 
SQL> ALTER TABLE T_TEST_NOLOGGING LOGGING;
 
Table altered
 
SQL> select logging from user_tables where table_name = 'T_TEST_NOLOGGING';
 
LOGGING
-------
YES

但是上面能够成立前提一定得是数据库和表所在的表空间一定是处于no force logging状态
如果是处于force logging的话，那么别说insert /*+ append */ into了，就连建表时指定的nologging都会失效，但是sql*loader这样的工作如果采用直接路径方式会由于跳过了sql引擎，所以不会产生重做数据。

查看数据库是不是force logging状态

SQL> select force_logging from v$database;

FORCE_LOGGING
-------------
NO

修改为force_logging

alter database force logging;

取消force logging

alter database no force logging;







阅读(5127) | 评论(0) | 转发(0) |
0

上一篇：假如女人是一种编程语言

下一篇：Oracle将system表空间的数据文件变小

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6