nologging選項的學習與測試

 

參考了eygle和網上的文章

1 Ovewview
1) 如果物件所在的表空間或Database在Force Logging Mode中，物件的Nologging屬性會被忽略掉。
2) In Nologging mode, data is modified with minimal logging(Some minimal logging is done to mark new extents invalid,
and data dictionary changes are always logged).
(Nologging並不是說一點日誌都不記錄,DML 肯定記，DML產生UNDO，UNDO產生REDO)

2 Only the following operations support the NOLOGGING mode.

DML:
1) Direct-path INSERT(serial or parallel) /*+append*/
2) Direct Loader(SQL*Loader)

DDL:
CREATE TABLE ... AS SELECT
CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE |CACHE READS
ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE |CACHE READS (to specify logging of newly created LOB columns)
ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters ... NOCACHE | CACHE READS (to change logging of existing LOB columns)
ALTER TABLE ... MOVE
ALTER TABLE ... (all partition operations that involve data movement)
– ALTER TABLE ... ADD PARTITION (hash partition only)
– ALTER TABLE ... MERGE PARTITIONS
– ALTER TABLE ... SPLIT PARTITION
– ALTER TABLE ... MOVE PARTITION
– ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION
– ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER INDEX ...




3 測試

create view redo_size as
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = 'redo size';

在非歸檔模式下：
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/product/9ir2/dbs/arch
Oldest online log sequence     1328
Current log sequence           1330

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            110036

SQL>  insert into test select * from dba_objects;

31424 rows created.

SQL> select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           3637292

SQL> insert /*+ append */ into test select * from dba_objects;

31424 rows created.

SQL>  select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           3647424

SQL> select (3647424-3637292) redo_append,  (3637292-110036) redo from dual;

REDO_APPEND       REDO
----------- ----------
      10132    3527256

結論：在非歸檔模式下,hint /*+ append */只產生少量的redo.

在歸檔模式下
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/archive/
Oldest online log sequence     1328
Next log sequence to archive   1330
Current log sequence           1330


SQL> @redo_size

View created.

SQL>  create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            173524

SQL> insert into test select * from dba_objects;

31425 rows created.

SQL>  select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           3691796

SQL> insert /*+ append */ into test select * from dba_objects;

31425 rows created.

SQL>  select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           7250224

SQL> select (7250224-3691796) append_redo, (3691796-173524) redo from dual;

APPEND_REDO       REDO
----------- ----------
    3558428    3518272

結論：我們看到在歸檔模式下,對於常規表的insert append產生和insert同樣的redo
此時的insert append實際上並不會有效能提高.
但是此時的append是生效了的


SQL> select operation, count(*) from v$logmnr_contents  where seg_name='TEST' group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
DDL                                       6
DIRECT INSERT                         31425
INSERT                                31425

我們注意到: INSERT 和 DIRECT INSERT 都是31425條記錄,也就是每條記錄都記錄了redo.     

4.對於Nologging的table的處理
a 在歸檔模式下:
SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            200276

SQL> insert into test select * from dba_objects;

31425 rows created.

SQL> select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           3718448

SQL>  insert /*+ append */ into test select * from dba_objects;

31425 rows created.

SQL> select * from redo_size;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           3724588

SQL> select (3724588-3718448) append_redo, (3718448-200276) redo from dual;

APPEND_REDO       REDO
----------- ----------
       6140    3518172

注意到/*+append */可以減少redo.

在非歸檔模式下也同樣可以減少redo.




Oracle資料庫中NOLOGGING和FORCE LOGGING的理解

其實nologging與表模式，插入模式，資料庫執行模式(archived/unarchived)都有很大的關係：

總結如下：

注意append是一種hint;

the +append hint
Additionally, a direct load with SQL*Loader and a direct load insert can also make use of nologging.
The direct load insert is a special form. of the insert statement that uses the /*+ append */ hint.

一般我們可以這樣來使用

insert /*+append+/ into mytable values(1,'alan');

資料庫在歸檔模式下

當表模式為logging狀態時，無論是append模式還是no append模式，redo都會生成。

當表模式為nologging狀態時，只有append模式，不會生成redo.

資料庫在非歸檔模式下

無論是在logging還是nologing的模式下，append的模式都不會生成redo,而no append模式下都會生成redo。

如果我想看一張表是否是logging狀態,可以這樣

select table_name,logging from dba_tables where table_name='tablename';

那麼在Oracle內部還存在一個內部引數：_disable_logging 預設是false

透過更改為true可以讓Oracle在修改表中的記錄的時候完全不記錄redo,這個引數要甚用。平時，我們只作為效能測試用。

force logging(強制日誌)模式：

透過命令：

alter database force logging來使得Oracle無論什麼操作都進行redo的寫入。

透過select force_logging from v$database可以看到當前資料庫強制日誌模式的狀態。



Note: Even though direct path load reduces the generation of redo, it is not totally eliminated.
That's because those inserts still generate undo which in turn generates redo.
That means that ordinary inserts, updates and deletes always generate redo, no matter if the underlying table or index is specifed with nologging or not.
If there is an index on the table, and an +append insert is made on the table, the indexes will produce redo.
This can be circumvented by setting the index to unusable and altering the session's skip_unusable_indexes to true. Nologging can be overriden at tablespace level using alter tablespace ... force logging. Nologging has no effect if the database is in force logging mode (which can be controlled with alter database force [no] logging mode).