ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 减少oracle日志的产生

减少oracle日志的产生

原创 Linux操作系统 作者:dengxm 时间:2011-05-05 23:36:06 0 删除 编辑
大家一定遇到过不想让oracle产生日志的情况。那么什么时候oracle能够不产生日志呢?
有吗?这里我总结一下oracle产生少量日志的情况。
在scott下,构造了一个9万多的表
create table t1 as select * from emp;
SQL> insert into t1 select * from t1;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
重复的不断插入,直到
917504 rows created.
SQL> commit;
-----------------------------------------------------------------------
下面开始我们的实验
SQL> conn /as sysdba
Connected.
SQL> desc v$sysstat
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 STATISTIC#                                                     NUMBER
 NAME                                                           VARCHAR2(64)
 CLASS                                                          NUMBER
 VALUE                                                          NUMBER
 STAT_ID                                                        NUMBER
SQL> select * from v$sysstat where class=2;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       132 redo blocks read for recovery                               2          0 2679943069
       133 redo entries                                                2     104183 3488821837
       134 redo size                                                   2  107480316 1236385760
       135 redo buffer allocation retries                              2          4 1446958922
       136 redo wastage                                                2     556420 3462806146
       137 redo writer latching time                                   2          2 2166056472
       138 redo writes                                                 2       2149 1948353376
       139 redo blocks written                                         2     217925 2391431605
       140 redo write time                                             2       1307 3094453259
       141 redo log space requests                                     2          5 1985754937
       142 redo log space wait time                                    2         27  252430928
       143 redo log switch interrupts                                  2          0  674283274
       144 redo ordering marks                                         2       2294 2104561012
       145 redo subscn max counts                                      2          0  449106517
       168 flashback log writes                                        2          0 3123176560
15 rows selected.
查看当前的redo size
SQL> select * from v$sysstat where STATISTIC#=134;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  107576456 1236385760
SQL> show user
USER is "SYS"
SQL> conn scott/tigger
Connected.
SQL> create table test as select * from t1;
Table created.
SQL> conn /as sysdba
Connected.
SQL> select * from v$sysstat where STATISTIC#=134;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  108493988 1236385760
SQL> select ( 108493988-107576456)/1024/1024 from dual;
(108493988-107576456)/1024/1024
-------------------------------
                     .875026703
                    
我们日常中的备份表产生了.875026703M大小的日志               
SQL> conn scott/tigger
Connected.
SQL> drop table test purge;
Table dropped.
SQL> create table test as select * from t1 nologging;
Table created.
SQL> conn /as sysdba
Connected.
SQL> select * from v$sysstat where STATISTIC#=134;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  108905676 1236385760
SQL> select (108905676- 108493988)/1024/1024 from dual;
(108905676-108493988)/1024/1024
-------------------------------
                     .392616272
加上nologging后,产生的日志比原来的1/2还要小。
---------------------------------------------------------------------------------------
二、接下来,我们来看一下insert
SQL> conn scott/tigger
Connected.
SQL> select count(*) from test;
  COUNT(*)
----------
         0
SQL> insert into test select * from t1;
1835008 rows created.
SQL> commit;
Commit complete.
SQL> conn /as sysdba
Connected.
SQL> select * from v$sysstat where STATISTIC#=134;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  202231620 1236385760
SQL> select (202231620- 109424508)/1024/1024 from dual;
(202231620-109424508)/1024/1024
-------------------------------
                     88.5077591
产生88.5077591M的日志
SQL> truncate table test;
Table truncated.
SQL> insert /*+append*/ into test select * from t1 nologging;
1835008 rows created.
SQL> commit;
Commit complete.
SQL> select * from v$sysstat where STATISTIC#=134;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  489164360 1236385760
SQL> select * from v$sysstat where STATISTIC#=134;
STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  489208144 1236385760
SQL> select (489208144-488407836)/1024/1024 from dual;
(489208144-488407836)/1024/1024
-------------------------------
                     .763233185
                    
  产生日志大小为.763233185M
 
----------------------------------------------------------------------------
三、创建索引
为9十多万数据的test创建索引
SQL> create index idx_1 on test(sal);

SQL> select (520157608-489472044)/1024/1024 from dual;
(520157608-489472044)/1024/1024
-------------------------------
                     29.2640343
                    
产生日志29.2640343M
SQL> drop index idx_1;
Index dropped.
SQL> create index idx_1 on test(sal) nologging;
Index created.
SQL> select (520661968-520507264)/1024/1024 from dual;
(520661968-520507264)/1024/1024
-------------------------------
                     .147537231
                    
产生日志.147537231M
到此我们使用了三种方法,来降低日志的产生
1、create table test as ......... nologging;
2、并行加载数据配合nologging
3、创建索引的时候,加nologging

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

上一篇: 奇怪了,哈
请登录后发表评论 登录
全部评论

注册时间:2008-10-08

  • 博文量
    54
  • 访问量
    140352