減少oracle日誌的產生

大家一定遇到過不想讓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還要小。

---------------------------------------------------------------------------------------

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

到此我們使用了三種方法，來降低日誌的產生