Oracle Nologging and Append 经典总结

转载

mb630ec035bcfe8 2022-10-19 22:34:43 博主文章分类：SQL language

对于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;

下面的例子是源数据在1万条左右，Create table …as select …测试发现相差2秒钟左右,特别是在大数据量带有nologging的Create速度上确实会快很多。

下面是INSERT语句的测试数据量在2百万左右，TEST_FUTUFARES2的logging不管是在YES还是NO的状态下其实插入都是一样的速度

通过以上测试其实表在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 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>INSERT /*+append*/ INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 24.297 seconds

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 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
	LOGGING	NO APPEND	有REDO
	NOLOGGING	APPEND	无REDO
	NOLOGGING	NO APPEND	有REDO
NOARCHIVELOG	LOGGING	APPEND	无REDO
	LOGGING	NO APPEND	有REDO
	NOLOGGING	APPEND	无REDO
	NOLOGGING	NO APPEND	有REDO