Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下,REDO、UNDO和执行速度的比较

0    83    2

Tags:

👉 本文共约3583个字,系统预计阅读时间或需14分钟。

【知识点整理】Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下,REDO、UNDO和执行速度的比较

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,\~O(∩_∩)O\~:

① 系统和会话级别的REDO和UNDO量的查询

② NOLOGGING、APPEND、ARCHIVE和PARALLEL下,REDO、UNDO和执行速度的比较(重点)

REDO和UNDO生成量的查询

说明:反映UNDO、REDO占用量的统计指标是:

UNDO:undo change vector size

REDO:redo size

1、查看全局数据库REDO生成量,可以通过V\$SYSSTAT视图查询

SELECT NAME,

VALUE

FROM V\$SYSSTAT

WHERE NAME = 'redo size';

Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下,REDO、UNDO和执行速度的比较

  1. 查看当前会话的REDO生成量,可以通过V\$MYSTAT或V\$SESSTAT视图查询

create or replace view redo_size as

SELECT VALUE

FROM v\$mystat my,

v\$statname st

WHERE my.statistic# =st.STATISTIC#

AND st.name = 'redo size';

----下边的实验将用到这个视图

CREATE OR REPLACE VIEW VW_REDO_UNDO_LHR AS

SELECT (SELECT NB.VALUE

FROM V\$MYSTAT NB, V\$STATNAME ST

WHERE NB.STATISTIC# = ST.STATISTIC#

AND ST.NAME = 'redo size') REDO,

(SELECT NB.VALUE

FROM V\$MYSTAT NB, V\$STATNAME ST

WHERE NB.STATISTIC# = ST.STATISTIC#

AND ST.NAME = 'undo change vector size') UNDO

FROM DUAL;

或:

CREATE OR REPLACE VIEW VW_REDO_UNDO_LHR AS

SELECT (SELECT NB.VALUE

FROM v\$sesstat NB, V\$STATNAME ST

WHERE NB.STATISTIC# = ST.STATISTIC#

AND ST.NAME = 'redo size'

AND NB.SID=USERENV('SID')) REDO,

(SELECT NB.VALUE

FROM v\$sesstat NB, V\$STATNAME ST

WHERE NB.STATISTIC# = ST.STATISTIC#

AND ST.NAME = 'undo change vector size'

AND NB.SID=USERENV('SID')) UNDO

FROM DUAL;

实验过程

实验环境准备

插入完成后查询结果:

Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下,REDO、UNDO和执行速度的比较

下边的存过可以测试REDO和UNDO的量,至于该存过的算法大家自己看吧。

开始实验

归档模式

增加日志组的个数,避免因为日志切换导致的等待。

在PL/SQL DEVELOPER中查询结果:

SELECT D.*

FROM T_RU_160929_LHR D

ORDER BY D.ID;

非归档模式

在PL/SQL DEVELOPER中查询结果:

SELECT D.*

FROM T_RU_160929_LHR D

ORDER BY D.ID;

以上测试过程,可以多做几次,然后取其平均值,多次测试前将结果表清空:

UPDATE T_RU_160929_LHR T

SET T.ARCH_REDO = '',

T.ARCH_UNDO = '',

T.ARCH_USE_TIME = '',

T.NOARCH_REDO = '',

T.NOARCH_UNDO = '',

T.NOARCH_USE_TIME = '',

T.COMMENTS = '';

COMMIT;

实验结果

根据以上的实验可以得到一些结论:关于表日志模式(LOGGING/NOLOGGING)、插入模式(APPEND/NOAPPEND)、数据库运行模式(归档/非归档)和并行模式下,REDO、UNDO和执行速度的情况大约如下表所示:

Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下,REDO、UNDO和执行速度的比较

结论

关于效率的结论:

  1. INSERT INTO: 在APPEND提示的情况下,NOLOGGING或NOARCHIVELOG满足一个即产生少量的REDO和UNDO;另外PARALLEL默认是以DIRECT的方式进行加载数据的,一般在并行情况下SQL执行速度提高。
  2. CTAS:CTAS本身就是一种DIRECT的操作,归档模式+NOLOGGING模式产生少量REDO;并行模式下时间大幅度减少,但生成的REDO和UNDO成倍增长。
  3. ALTER TABLE ... MOVE:ARCHIVELOG+NOLOGGING模式产生少量REDO;并行模式下时间大幅度减少,但生成的REDO和UNDO成倍增长 。
  4. CREATE INDEX:ARCHIVELOG+NOLOGGING模式产生少量REDO;并行模式下时间大幅度减少,但生成的REDO和UNDO成倍增长。

5、UPDATE:任何组合都会生成大量UNDO、大量REDO;有关并行的性能需要查询执行计划再做定夺。

6、DELETE:任何组合都会生成大量UNDO、大量REDO;加上并行可以大幅度提高SQL的执行速度。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

7、MERGE:在关联更新的情况下,MERGE语句的非关联形式的性能比UPDATE要高,若加上并行性能更好。

8、总体而言,非归档比归档模式下性能高

关于属性NOLOGGING和并行度的结论:

1、对于形如:CREATE TABLE TT NOLOGGING PARALLEL 4 AS SELECT * FROM DBA_OBJECTS; 或 CREATE INDEX IDNX11 ON TT(OBJECT_ID) NOLOGGING PARALLEL 4;的SQL语句而言,创建的表或索引的并行度是4,日志模式是NOLOGGING,所以,生产库上对于重要的表和索引需要修改为LOGGING,并行度可以根据需要来修改,ALTER TABLE TT LOGGING NOPARALLEL;或ALTER INDEX IDNX11 LOGGING NOPARALLEL;

2、对于形如:ALTER TABLE TT MOVE NOLOGGING PARALLEL 4;或 ALTER INDEX IDNX11 REBUILD NOLOGGING PARALLEL 4;的SQL语句而言,修改后的表的并行度依然为原来的并行度,但是索引的并行度是4,而日志模式都是NOLOGGING,所以,生产库上对于重要的表和索引需要修改为LOGGING,并行度可以根据需要来修改,ALTER TABLE TT LOGGING NOPARALLEL;或ALTER INDEX IDNX11 LOGGING NOPARALLEL;

总之一句话,若执行了上边形式的SQL语句后,最好都修改一下表或索引的并行度及其日志模式。

APPEND使用注意事项:

  1. 建议不要经常使用APPEND,这样表空间会一直在高水位上,除非你这个表只插不删。

  2. 以APPEND方式插入记录后,要执行COMMIT,才能对表进行查询。否则会出现错误:ORA-12838: 无法在并行模式下修改之后读/修改对象。

  3. APPEND对INSERT INTO ... VALUES语句不起作用,需要使用11gR2的APPEND_VALUES来提示才可以直接路径加载,注意:APPEND_VALUES对INSERT INTO ... SELECT也起作用。

  4. APPEND使用HWM之上的块,减少了搜索FREELIST上块的时间。

  5. 在归档模式下:NOLOGGING+APPEND才会显著减少REDO数量;在非归档模式下:单独APPEND即可减少REDO数量。

  6. APPEND不会减少相关表的索引上产生的REDO数量。

  7. APPEND的插入操作是给表加上6级排它锁,会阻塞表上的所有DML语句。

  8. 每提交一次,就会取一个新的BLOCK存放,高水位就上推一个BLOCK,若在LOOP循环中,外部循环100W次,但是每循环一次只有一行符合条件的数据插入,这样,大量单条/*+APPEND*/插入,就会使得表急剧增大,除对INSERT本身造成性能影响之外,对以后的SELECT、UPDATE、DELETE更是带来更巨大的性能影响。

NOLOGGING使用注意事项:

  1. NOLOGGING插完后最好对表做个备份。生产上重要的表不建议设置NOLOGGING属性。

  2. 如果库处在FORCE LOGGING模式下,此时的NOLOGGING方式是无效的。

PDML使用注意事项:

  1. 必须使用ALTER SESSION ENABLE PARALLEL DML;才可以启动PDML。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部