ITPub博客

首页 > 数据库 > Oracle > append与nologging的正确配合才能提高查询效率

append与nologging的正确配合才能提高查询效率

原创 Oracle 作者:libingquan008 时间:2015-09-21 13:43:04 0 删除 编辑


使用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博客 ” ,链接:http://blog.itpub.net/22878696/viewspace-1805127/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-02-10

  • 博文量
    46
  • 访问量
    119546