转载地址
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.16Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------1148864 recursive calls662012 db block gets1380625 consistent gets0 physical reads937864108 redo size345 bytes sent via SQL*Net to client542 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client2 sorts (memory)0 sorts (disk)12706742 rows processedSQL>
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.56Execution 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 calls1080626 db block gets341476 consistent gets304 physical reads918012192 redo size373 bytes sent via SQL*Net to client527 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)12706882 rows processedSQL> 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.80Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------0 recursive calls1277659 db block gets120968 consistent gets0 physical reads903051200 redo size352 bytes sent via SQL*Net to client542 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)12707172 rows processedSQL> 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.34Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------1148211 recursive calls1413593 db block gets1379882 consistent gets0 physical reads937850524 redo size353 bytes sent via SQL*Net to client542 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client7 sorts (memory)0 sorts (disk)12707206 rows processedSQL>
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.32Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------1149041 recursive calls662152 db block gets1380961 consistent gets2 physical reads35775004 redo size354 bytes sent via SQL*Net to client542 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client7 sorts (memory)0 sorts (disk)12708501 rows processedSQL> 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
----------------------------------------------------------------------------------