nologging选项的学习与测试

 

参考了eygle和网上的文章

http://www.eygle.com/faq/Nologging&append.htm

http://database.ctocio.com.cn/tips/282/8200782.shtml

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). 

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

转载于:http://blog.itpub.net/10248702/viewspace-668003/