append与nologging的正确配合才能提高查询效率
使用append hint称为直接路径加载插入,数据不经过Buffer cache而是直接插到数据文件中。
具体的原理是：使用append hint则系统不查找freelist链表中的空闲块(freeblock),而是直接在segment高水位(HWM)以上插入数据，省略了freeblock的查找时间，因此速度快，但是由于总是在HWM以上插数据,导致了空间的浪费，对于频繁修改的数据不建议采用这种方式。
在开发以及运维过程中，常常要配合nologging提高查询速度，减少redo的产生，从而避免log file sync带来的性能下降问题。
以下我采用实验的方式验证append与nologgingde的作用：
服务器： Red Hat Enterprise Linux Server release 5.8 (Tikanga)
数据库： Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
实验一：append hint方式将分配新的extent造成空间浪费
SQL> create table AP01 as select * from dba_objects; //创建基础表AP01
Table created
SQL> select segment_name,extent_id,bytes from user_extents where segment_name='AP01'; //查看AP01分配的extent共有24个
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 0 65536
AP01 1 65536
AP01 2 65536
AP01 3 65536
AP01 4 65536
AP01 5 65536
AP01 6 65536
AP01 7 65536
AP01 8 65536
AP01 9 65536
AP01 10 65536
AP01 11 65536
AP01 12 65536
AP01 13 65536
AP01 14 65536
AP01 15 65536
AP01 16 1048576
AP01 17 1048576
AP01 18 1048576
AP01 19 1048576
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 20 1048576
AP01 21 1048576
AP01 22 1048576
AP01 23 1048576
24 rows selected
SQL> delete from AP01; //删除表中的数据
75224 rows deleted
SQL>
SQL> commit;
Commit complete
SQL> select count(*) from AP01;
COUNT(*)
----------
0
SQL> select segment_name,extent_id,bytes from user_extents where segment_name='AP01'; //
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 0 65536
AP01 1 65536
AP01 2 65536
AP01 3 65536
AP01 4 65536
AP01 5 65536
AP01 6 65536
AP01 7 65536
AP01 8 65536
AP01 9 65536
AP01 10 65536
AP01 11 65536
AP01 12 65536
AP01 13 65536
AP01 14 65536
AP01 15 65536
AP01 16 1048576
AP01 17 1048576
AP01 18 1048576
AP01 19 1048576
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 20 1048576
AP01 21 1048576
AP01 22 1048576
AP01 23 1048576
24 rows selected
--结果证明 delete并不会收缩已经分配的空间，extent数量没有减少。
SQL> insert into AP01 select * from dba_objects; --继续测试 采用传统方式插入数据
75224 rows inserted
SQL> commit;
Commit complete
SQL> select segment_name,extent_id,bytes from user_extents where segment_name='AP01';
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 0 65536
AP01 1 65536
AP01 2 65536
AP01 3 65536
AP01 4 65536
AP01 5 65536
AP01 6 65536
AP01 7 65536
AP01 8 65536
AP01 9 65536
AP01 10 65536
AP01 11 65536
AP01 12 65536
AP01 13 65536
AP01 14 65536
AP01 15 65536
AP01 16 1048576
AP01 17 1048576
AP01 18 1048576
AP01 19 1048576
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 20 1048576
AP01 21 1048576
AP01 22 1048576
AP01 23 1048576
24 rows selected
--extent数量还是24个，说明新插入的数据被放到空间block中。
SQL>
SQL> delete from AP01; --再次清掉所有数据
75224 rows deleted
SQL> commit;
Commit complete
SQL> select segment_name,extent_id,bytes from user_extents where segment_name='AP01';
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 0 65536
AP01 1 65536
AP01 2 65536
AP01 3 65536
AP01 4 65536
AP01 5 65536
AP01 6 65536
AP01 7 65536
AP01 8 65536
AP01 9 65536
AP01 10 65536
AP01 11 65536
AP01 12 65536
AP01 13 65536
AP01 14 65536
AP01 15 65536
AP01 16 1048576
AP01 17 1048576
AP01 18 1048576
AP01 19 1048576
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 20 1048576
AP01 21 1048576
AP01 22 1048576
AP01 23 1048576
24 rows selected
--此时为空表，但依然占用24个extent的空间。
--以下使用append直接路径append插入数据进行测试
SQL> insert/*+append*/into AP01 select * from dba_objects;
75224 rows inserted
SQL> commit;
Commit complete
SQL> select segment_name,extent_id,bytes from user_extents where segment_name='AP01';
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 0 65536
AP01 1 65536
AP01 2 65536
AP01 3 65536
AP01 4 65536
AP01 5 65536
AP01 6 65536
AP01 7 65536
AP01 8 65536
AP01 9 65536
AP01 10 65536
AP01 11 65536
AP01 12 65536
AP01 13 65536
AP01 14 65536
AP01 15 65536
AP01 16 1048576
AP01 17 1048576
AP01 18 1048576
AP01 19 1048576
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 20 1048576
AP01 21 1048576
AP01 22 1048576
AP01 23 1048576
AP01 24 1048576
AP01 25 1048576
AP01 26 1048576
AP01 27 1048576
AP01 28 1048576
AP01 29 1048576
AP01 30 1048576
AP01 31 1048576
AP01 32 1048576
33 rows selected
SQL>
SQL>
SQL> commit;
Commit complete
SQL> insert/*+append*/into AP01 select * from dba_objects;
75224 rows inserted
SQL> commit;
Commit complete
SQL> select segment_name,extent_id,bytes from user_extents where segment_name='AP01';
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 0 65536
AP01 1 65536
AP01 2 65536
AP01 3 65536
AP01 4 65536
AP01 5 65536
AP01 6 65536
AP01 7 65536
AP01 8 65536
AP01 9 65536
AP01 10 65536
AP01 11 65536
AP01 12 65536
AP01 13 65536
AP01 14 65536
AP01 15 65536
AP01 16 1048576
AP01 17 1048576
AP01 18 1048576
AP01 19 1048576
SEGMENT_NAME EXTENT_ID BYTES
-------------------------------------------------------------------------------- ---------- ----------
AP01 20 1048576
AP01 21 1048576
AP01 22 1048576
AP01 23 1048576
AP01 24 1048576
AP01 25 1048576
AP01 26 1048576
AP01 27 1048576
AP01 28 1048576
AP01 29 1048576
AP01 30 1048576
AP01 31 1048576
AP01 32 1048576
AP01 33 1048576
AP01 34 1048576
AP01 35 1048576
AP01 36 1048576
AP01 37 1048576
AP01 38 1048576
AP01 39 1048576
AP01 40 1048576
41 rows selected
---从上面我们发现使用append加载的数据会安放在HWM之上，系统增加新的extent来容纳他们，不管段上是否还有空闲区域。
实验二：非归档模式下 append加载情况
1.使用append+logging(默认不加此参数，就是logging)
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 redo size 368801804
SQL> set timing on
SQL>
SQL>
SQL> insert into AP01 select * from dba_objects logging;
75224 rows inserted
Executed in 2.542 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 redo size 377808968
Executed in 0.078 seconds
SQL> insert/*+append*/into AP01 select * from dba_objects logging;
75224 rows inserted
Executed in 0.452 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 redo size 377841416
Executed in 0.047 seconds
SQL> select (377841416-377808968) append_redo_inc_size, (377808968-368801804) noappend_redo_inc_size from dual;
APPEND_REDO_INC_SIZE NOAPPEND_REDO_INC_SIZE
-------------------- ----------------------
32448 9007164
Executed in 0.062 seconds
从上面的结果可以看到：在非归档模式下 使用append+logging(默认)方式相比不加append将会提高加载速度并降低redo量。
2.使用append+nologging
SQL> insert into AP01 select * from dba_objects nologging;
insert into AP01 select * from dba_objects nologging
ORA-12838: 无法在并行模式下修改之后读/修改对象
SQL> commit;
Commit complete
Executed in 0.016 seconds
SQL> insert into AP01 select * from dba_objects nologging;
75224 rows inserted
Executed in 3.354 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 redo size 388844044
Executed in 0.047 seconds
SQL> insert/*+append*/into AP01 select * from dba_objects nologging;
75224 rows inserted
Executed in 1.653 seconds
SQL> commit;
Commit complete
Executed in 0.031 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
48 redo size 388889148
Executed in 0.062 seconds
SQL> select (388889148-388844044) append_redo_inc_size, (388844044-377841416) noappend_redo_inc_size from dual;
APPEND_REDO_INC_SIZE NOAPPEND_REDO_INC_SIZE
-------------------- ----------------------
45104 11002628
Executed in 0.047 seconds
从上面的结果可以看到：在非归档模式下 使用append+nologging(默认)方式相比不加append只使用nologging将会提高加载速度并降低redo量。
实验三：归档模式下 append加载情况
1.归档模式下 使用logging
SQL> select name,LOG_MODE from v$database;
NAME LOG_MODE
--------- ------------
TTDB ARCHIVELOG
SQL>
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
30 redo size 350572
SQL> insert into AP01 select * from dba_objects;
75224 rows inserted
SQL> commit;
Commit complete
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
30 redo size 9129096
SQL> insert/*+append*/into AP01 select * from dba_objects;
75224 rows inserted
SQL> commit;
Commit complete
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
30 redo size 18014084
SQL> select (18014084-9129096) append_redo_inc_size, (9129096-350572) noappend_redo_inc_size from dual;
APPEND_REDO_INC_SIZE NOAPPEND_REDO_INC_SIZE
-------------------- ----------------------
8884988 8778524
从上可知：在归档模式下 使用append+logging(默认)方式没有提高速度降低redo,结果和不适用append效果一样。
1.归档模式下 使用nologging
SQL> create table AP01 nologging as select * from dba_objects;
Table created
Executed in 0.421 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
30 redo size 135414984
Executed in 0.062 seconds
SQL> insert into AP01 select * from dba_objects;
75224 rows inserted
Executed in 0.546 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
30 redo size 144260420
Executed in 0.047 seconds
SQL> insert /*+APPEND*/into AP01 select* from dba_objects;
75224 rows inserted
Executed in 0.468 seconds
SQL> commit;
Commit complete
Executed in 0.015 seconds
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b where a.statistic#=b.statistic# and a.name = 'redo size';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
30 redo size 144305144
Executed in 0.047 seconds
SQL> select (144305144-144260420) append_redo_inc_size, (144260420-135414984) noappend_redo_inc_size from dual;
APPEND_REDO_INC_SIZE NOAPPEND_REDO_INC_SIZE
-------------------- ----------------------
44724 8845436
Executed in 0.032 seconds
从上可知：在归档模式下 使用append+nologging方式可以大大提高加载速度并降低redo量。
总结：
非归档模式下,只要使用append hint便可提高效率,减少redo生成量，nologging可加也可不加；
归档模式下 ,append和nologging同时使用，才能够提升效率，减少redo生成量。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22878696/viewspace-1805127/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
