Oracle Nologging And Append

转载地址

http://gaobo424.iteye.com/blog/1330010

自己测试 如下

环境：10.2.0.4(单机，没开归档)

SQL> alter table t_dz_test nologging;

Table altered.

SQL> set timing on
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;  

11677713 rows created.

Elapsed: 00:00:10.01
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> set table t_dz_test logging;
SP2-0158: unknown SET option "table"
SQL> alter table t_dz_test logging;

Table altered.

Elapsed: 00:00:00.02
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;  

11677713 rows created.

Elapsed: 00:00:12.23
SQL>

环境：10.2.0.5（rac  开了归档）

开始t_dz_test是logging，第一次append耗时45秒，第二次nogging时耗时19秒，第三次改成logging时耗时17秒。。。最后一次nologging耗时45秒

我想看一下redolog大小，开了执行计划，结果报错ora-12838,关了之后不报错，，不知道这个报错之后的准不准，nologging的redolog是小了，但是最后一次耗时45秒。

下面是同事给的图片，，，貌似我测试的没有这么理想

SQL> 
SQL> 
SQL> create table t_dz_test as select * from t_user_mobile_dz where rownum<=1;

Table created.

SQL> set timing on
SQL> set autot trac
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot  trace
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;

12706742 rows created.

Elapsed: 00:00:51.16

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
    1148864  recursive calls
     662012  db block gets
    1380625  consistent gets
          0  physical reads
  937864108  redo size
        345  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
   12706742  rows processed

SQL> 
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> 
SQL> 
SQL> insert into t_dz_test  select  * from t_user_mobile_dz;


12706882 rows created.

Elapsed: 00:00:51.56

Execution Plan
----------------------------------------------------------
Plan hash value: 2802676981

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

| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time
     |

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

|   0 | INSERT STATEMENT  |                  |    12M|   674M| 26304   (2)| 00:0
5:16 |

|   1 |  TABLE ACCESS FULL| T_USER_MOBILE_DZ |    12M|   674M| 26304   (2)| 00:0
5:16 |

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



Statistics
----------------------------------------------------------
        371  recursive calls
    1080626  db block gets
     341476  consistent gets
        304  physical reads
  918012192  redo size
        373  bytes sent via SQL*Net to client
        527  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   12706882  rows processed

SQL> SQL> 
SQL> rollback;

Rollback complete.

Elapsed: 00:00:06.83
SQL> set autot off 
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;

12706936 rows created.

Elapsed: 00:00:45.87
SQL> 
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> alter table t_dz_test nologging;

Table altered.

Elapsed: 00:00:00.01
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;

12706968 rows created.

Elapsed: 00:00:19.75
SQL> rollabck;
SP2-0042: unknown command "rollabck" - rest of line ignored.
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> alter table t_dz_test logging;

Table altered.

Elapsed: 00:00:00.01
SQL> 
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;

12707115 rows created.

Elapsed: 00:00:17.16
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> set autot  trace
SQL> 
SQL> 
SQL> 
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;

12707172 rows created.

Elapsed: 00:00:18.80

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          0  recursive calls
    1277659  db block gets
     120968  consistent gets
          0  physical reads
  903051200  redo size
        352  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   12707172  rows processed

SQL> alter table t_dz_test logging;

Table altered.

Elapsed: 00:00:00.04
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;

12707206 rows created.

Elapsed: 00:00:44.34

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
    1148211  recursive calls
    1413593  db block gets
    1379882  consistent gets
          0  physical reads
  937850524  redo size
        353  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
   12707206  rows processed

SQL> 
SQL> truncate table t_dz_test;



Table truncated.

Elapsed: 00:00:46.18
SQL> SQL> SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> alter table t_dz_test nologging;

Table altered.

Elapsed: 00:00:00.01
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;


12708501 rows created.

Elapsed: 00:00:47.32

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
    1149041  recursive calls
     662152  db block gets
    1380961  consistent gets
          2  physical reads
   35775004  redo size
        354  bytes sent via SQL*Net to client
        542  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
   12708501  rows processed

SQL> SQL> 
SQL> 
SQL> 
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> 

对于logging的理解总是以为表的日志设置为NO它就不会去产生日志了，其实不是的下面是对于logging的一些解释和试验。

Logging介绍

可以采用nologging模式执行以下操作：

1．索引的创建和ALTER（重建）。
2．表的批量INSERT（通过/*+append */提示使用“直接路径插入“。或采用SQL*Loader直接路径加载）。表数据生成少量redo，但是所有索引修改会生成大量redo（尽管表不生成日志，但这个表上的索引却会生成redo！）。


3．Lob操作（对大对象的更新不必生成日志）。
4．通过create table as select创建表。
5．各种alter table操作，如move和split。
在一个archivelog模式的数据库上，如果nologging使用得当，可以加快许多操作的速度，因为它能显著减少生成的重做日志量。假设你有一个表，需要从一个表空间移到另一个表空间，原先需要N小时才能完成的操作可能只需要N/2小时。要想适当地使用这个特性，需要DBA的参与，或者必须与负责数据库备份和恢复（或任何备用数据库）的人沟通。如果这个人不知道使用了这个特性，一旦出现介质失败，就可能丢失数据，或者备用数据库的完整性可能遭到破坏，对此一定要三思。

对象Logging状态查询

通过此查询SQL语句查询表的logging状态

SELECT T.TABLE_NAME, T.LOGGING
  FROM USER_TABLES T
WHERE T.TABLE_NAME LIKE '%TEST_FUTUFARES%';

Create和Insert的Logging测试
Create table …. as select ….及 insert into …..select ….测试

改变logging状态值的方法：

ALTER TABLE table_name NOLOGGING/logging;

通过以上测试其实表在Nologging与Logging状态时插入2百万的数据耗时差不多的，也就是说DML不是说不记日志而只是在特定的情况下是不记日志的，比如用SQL*Loader直接装载及INSERT /*+Append*/选项直接路径装载，也就是说不管是否是NOLOGGING状态DML操作正常情况下肯定会产生日志。

Nologging模式下数据库操作只有如下几种情况下不产成redo记录：

１、用sql*load的direct load方式时，不采用redo记录
已测试
２、用insert的direct方式，即在append方式insert
已测试

３、create table ….as select….
已测试

４、create index
create index  TEST_FUTUFARES2_log  on TEST_FUTUFARES2 (FARE_KIND,FUTUFARE_TYPE) nologging;

创建索引要想产生极少的REDO必须要按上面的那种方式创建索引，按照上面的那种方法去创建索引不管表的日志是处在nologging还是logging状态下都是一样都会产生很少的REDO日志，否则还是会产生大量的REDO日志。
５、alter table ... move partition
６、alter table ... split partition
７、alter index ... split partition
８、alter index ... rebuild
９、alter index ... rebuild partition
１０、INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line





Append介绍


非归档模式情况下：

1．查看当前会话所有产生的REDO总量

表处于nologging状态:

SQL> set timing on;
SQL>INSERT  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 36.25 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                   VALUE
--------------------------------------------------------------------------
Redo size                                             113495212

SQL>INSERT /*+append*/ INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 9.062 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
Redo size                                                113560764

SQL>select 113560764-113495212 from dual;
113560764-113495212
-------------------
              65552


表处于logging状态:
   对于此测试得出的结果其实跟上面的nologging得出的测试结果几乎是一模一样的，就不贴出来了。



归档模式情况下：

表处于logging状态:

SQL> INSERT  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 44.031 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                     VALUE
--------------------------------------------------------------------------
Redo size                                              113460280

SQL>INSERT /*+append*/  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 24.297 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
Redo size                                               223253980

SQL> select 223253980-113460280 from dual;
223253980-113460280
-------------------
          109793700

表处于nologging状态:

SQL> INSERT /*+append*/  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 6.391 seconds



SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
redo size                                               223576712

SQL> select 223576712-223253980 from dual;
223576712-223253980
-------------------
             322732



2．查看全局数据库redo生成量，可以通过v$sysstat视图看到

SQL> select name,value  from v$sysstat where name='redo size';
NAME                                                    VALUE
--------------------------------------------------------------------------
Redo size                                               122314360


总结：
关于Nologging与append测试的一些总结，通过上面的SQL语句查看可以得出在大量数据插入过程的语句中加入/*+append*/的这个SQL语句产生的REDO日志明显示是会少同时时间节约了很多，当然这样可能会影响备份因此nologging加载数据后要做一个数据库的全备。

insert append并不是在任何时候都可以节省时间的以下是测试的一些总结：



第一种情况：

database为archivelog状态，这种情况下，就算你用insert append也是不一定提高插入效率的。但是如果你将目标表设置为nologging，然后再使用insert append就会很快。
第二种情况：

database为noarchivelog状态，如果在这种情况下直接采用insert方法而不加入append向表中插入数据，占用的redo空间的大小与archivelog状态下占用的大小是相当的，不论表是否为nologging。但是如果采用insert append方法的话，通过redo的占用值大家可以发现，不论表是否为nologging，所占用的redo的大小都是很小的。也就说明：在数据库为noarchivelog的状态下，采用insert append方法，如果表不是nologging，系统也会自动将表转换为nologging（即在执行insert append之前，先执行一个alter table arch1 nologging）。
第三种情况：

如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo数量可能比表的redo数量还要大。用insert append可以实现直接路径加载速度是快很多，但有一点需要注意: insert append时在表上加”6”类型的锁，会阻塞表上的所有DML语句，因此在有业务运行的情况下要慎重使用。若同时执行多个insert append对同一个表并行加载数据，并不一定会提高速度，因为每一时刻只能有一个进程在加载(排它锁造成)à此观点是在网上查到的对于此疑问对于锁的问题此兄弟可能不是太理解，对于ORACLE数据库来说只要DML特别是insert操作他肯定会将表给锁住而且是独占锁除非进行commit,rollback，及其它的DDL操作来释放否则锁会一直独占导致其它的DML操作无法进行正常的操作，而跟所谓的APPEND无关。

以下是关于表模式(LOGGING/NOLOGGING)，插入模式(APPEND/NOAPPEND)，数据库运行模式(归档/非归档)，REDO日志产生的关系
数据库模式            表模式              插入模式            REDO生成
----------------------------------------------------------------------------------
ARCHIVELOG          LOGGING          APPEND                有REDO
                                                         NO APPEND          有REDO

                               NOLOGGING     APPEND                 无REDO
                                                         NO APPEND           有REDO
----------------------------------------------------------------------------------
NOARCHIVELOG    LOGGING          APPEND                 无REDO
                                                        NO APPEND           有REDO
                              NOLOGGING     APPEND                  无REDO
                                                        NO APPEND           有REDO
----------------------------------------------------------------------------------