【知识点整理】Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较
1 BLOG文档结构图
2 前言部分
2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 系统和会话级别的REDO和UNDO量的查询
② NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较（重点）
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）有同步更新。
② 文章中用到的所有代码，相关软件，相关资料请前往小麦苗的云盘下载（http://blog.itpub.net/26736162/viewspace-1624453/）。
③ 若网页文章代码格式有错乱，推荐使用360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式显示有问题，也可以去博客园地址阅读。
④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
3 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';
2、查看当前会话的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;
4 实验过程
4.1 实验环境准备
|
--记录REDO和UNDO量的视图 CREATE OR REPLACE VIEW VW_REDO_UNDO_LHR AS SELECT (SELECT NB.VALUE FROM VMYSTATNB,VMYSTATNB,VSTATNAME ST WHERE NB.STATISTIC# = ST.STATISTIC# AND ST.NAME = 'redo size') REDO, (SELECT NB.VALUE FROM VMYSTATNB,VMYSTATNB,VSTATNAME ST WHERE NB.STATISTIC# = ST.STATISTIC# AND ST.NAME = 'undo change vector size') UNDO FROM DUAL;
--准备中间表,T_A为500W，T_B为500W的数据量，T_A表删掉少量数据 DROP TABLE T_A PURGE; DROP TABLE T_B PURGE; CREATE TABLE T_A AS SELECT * FROM DBA_OBJECTS; CREATE TABLE T_B AS SELECT * FROM DBA_OBJECTS;
INSERT INTO T_A SELECT * FROM T_A; INSERT INTO T_A SELECT * FROM T_A; INSERT INTO T_A SELECT * FROM T_A; INSERT INTO T_A SELECT * FROM T_A; INSERT INTO T_A SELECT * FROM T_A; INSERT INTO T_A SELECT * FROM T_A; COMMIT; INSERT INTO T_B SELECT * FROM T_A; DELETE FROM T_A WHERE OBJECT_ID>=90000; COMMIT;
SELECT COUNT(1) FROM T_A; --5548800 SELECT COUNT(1) FROM T_B; --5668976 |
|
--记录测试结果 DROP TABLE T_RU_160929_LHR; CREATE TABLE T_RU_160929_LHR ( ID NUMBER PRIMARY KEY, SQL_TYPES VARCHAR2(255), SQL1 VARCHAR2(255), SQL2 VARCHAR2(255), SQL3 VARCHAR2(4000), IS_DIRECT VARCHAR2(20), IS_NOLOGGING VARCHAR2(20), IS_PARALLEL VARCHAR2(20), ARCH_REDO NUMBER, ARCH_UNDO NUMBER, NOARCH_REDO NUMBER, NOARCH_UNDO NUMBER, ARCH_USE_TIME NUMBER, NOARCH_USE_TIME NUMBER, SQL_EXPLAIN CLOB, COMMENTS VARCHAR2(255) );
--插入要执行的SQL语句
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (1, 'CTAS', NULL, NULL, 'CREATE TABLE T_RU_CTAS_LHR AS SELECT * FROM T_B', 'Y', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (2, 'CTAS', NULL, NULL, 'CREATE TABLE T_RU_CTAS_LHR NOLOGGING AS SELECT * FROM T_B', 'Y', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (3, 'CTAS', NULL, NULL, 'CREATE TABLE T_RU_CTAS_LHR NOLOGGING PARALLEL 4 AS SELECT * FROM T_B', 'Y', 'Y', 'Y');
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (4, 'CI', NULL, NULL, 'CREATE INDEX IND_TA_LHR ON T_A(OBJECT_ID)', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (5, 'CI', NULL, NULL, 'CREATE INDEX IND_TA_LHR ON T_A(OBJECT_ID) NOLOGGING', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (6, 'CI', NULL, NULL, 'CREATE INDEX IND_TA_LHR ON T_A(OBJECT_ID) NOLOGGING PARALLEL 4', 'N', 'Y', 'Y');
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (7, 'MOVE', NULL, NULL, 'ALTER TABLE T_A MOVE', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (8, 'MOVE', NULL, NULL, 'ALTER TABLE T_A MOVE NOLOGGING', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (9, 'MOVE', NULL, NULL, 'ALTER TABLE T_A MOVE NOLOGGING PARALLEL 4', 'N', 'Y', 'Y');
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (10, 'INSERT', NULL, NULL, 'INSERT INTO T_A SELECT * FROM T_B', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (11, 'INSERT', 'ALTER TABLE T_A NOLOGGING', NULL, 'INSERT INTO T_A SELECT * FROM T_B', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (12, 'INSERT', NULL, NULL, 'INSERT /*+ APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (13, 'INSERT', 'ALTER TABLE T_A NOLOGGING', NULL, 'INSERT /*+ APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (14, 'INSERT', 'ALTER TABLE T_A NOLOGGING', NULL, 'INSERT /*+ PARALLEL(4) APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'Y', 'Y'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (15, 'INSERT', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'INSERT /*+ PARALLEL(4) APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'Y', 'Y(PDML)');
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (16, 'UPDATE', NULL, NULL, 'UPDATE T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (17, 'UPDATE', NULL, NULL, 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'N', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (18, 'UPDATE', 'ALTER TABLE T_A NOLOGGING', NULL, 'UPDATE T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (19, 'UPDATE', 'ALTER TABLE T_A NOLOGGING', NULL, 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (20, 'UPDATE', 'ALTER SESSION ENABLE PARALLEL DML', NULL, 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'N', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (21, 'UPDATE', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(PDML)');
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (22, 'MERGE', 'ALTER TABLE T_A NOLOGGING', NULL, 'MERGE INTO T_A T USING (SELECT TA.ROWID ROWIDS, MAX(TB.DATA_OBJECT_ID) DATA_OBJECT_ID FROM T_B TB, T_A TA WHERE TB.OBJECT_ID = TA.OBJECT_ID AND TA.OBJECT_ID <= 1000 GROUP BY TA.ROWID) T1 ON (T.ROWID = T1.ROWIDS)WHEN MATCHED THEN UPDATE SET T.DATA_OBJECT_ID = T1.DATA_OBJECT_ID', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (23, 'MERGE', 'ALTER TABLE T_A NOLOGGING', NULL, 'MERGE /*+ PARALLEL(4) */ INTO T_A T USING (SELECT TA.ROWID ROWIDS, MAX(TB.DATA_OBJECT_ID) DATA_OBJECT_ID FROM T_B TB, T_A TA WHERE TB.OBJECT_ID = TA.OBJECT_ID AND TA.OBJECT_ID <= 1000 GROUP BY TA.ROWID) T1 ON (T.ROWID = T1.ROWIDS)WHEN MATCHED THEN UPDATE SET T.DATA_OBJECT_ID = T1.DATA_OBJECT_ID', 'N', 'Y', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (24, 'MERGE', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'MERGE /*+ PARALLEL(4) */ INTO T_A T USING (SELECT TA.ROWID ROWIDS, MAX(TB.DATA_OBJECT_ID) DATA_OBJECT_ID FROM T_B TB, T_A TA WHERE TB.OBJECT_ID = TA.OBJECT_ID AND TA.OBJECT_ID <= 1000 GROUP BY TA.ROWID) T1 ON (T.ROWID = T1.ROWIDS)WHEN MATCHED THEN UPDATE SET T.DATA_OBJECT_ID = T1.DATA_OBJECT_ID', 'N', 'Y', 'Y(PDML)');
INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (25, 'DELETE', NULL, NULL, 'DELETE FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (26, 'DELETE', NULL, NULL, 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'N', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (27, 'DELETE', 'ALTER TABLE T_A NOLOGGING', NULL, 'DELETE FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (28, 'DELETE', 'ALTER TABLE T_A NOLOGGING', NULL, 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (29, 'DELETE', 'ALTER SESSION ENABLE PARALLEL DML', NULL, 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'N', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (30, 'DELETE', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(PDML)'); COMMIT; |
插入完成后查询结果：
|
SELECT ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL FROM T_RU_160929_LHR D ORDER BY D.ID; |
下边的存过可以测试REDO和UNDO的量，至于该存过的算法大家自己看吧。
|
--创建存储过程，用来测试REDO量 CREATE OR REPLACE PROCEDURE PRO_TEST_RU_LHR AS
V_REDO NUMBER := 0; V_UNDO NUMBER := 0; V_REDO1 NUMBER := 0; V_UNDO1 NUMBER := 0; V_ARCH VARCHAR2(30); V_START_TIME NUMBER := 0; V_END_TIME NUMBER := 0;
BEGIN
SELECT D.LOG_MODE INTO V_ARCH FROM V$DATABASE D;
FOR CUR IN (SELECT D.ID, D.SQL1, D.SQL2, D.SQL3 FROM T_RU_160929_LHR D ORDER BY D.ID) LOOP
BEGIN EXECUTE IMMEDIATE CUR.SQL1; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN EXECUTE IMMEDIATE CUR.SQL2; EXCEPTION WHEN OTHERS THEN NULL; END; SELECT DBMS_UTILITY.GET_TIME INTO V_START_TIME FROM DUAL; SELECT V.REDO, V.UNDO INTO V_REDO, V_UNDO FROM VW_REDO_UNDO_LHR V; EXECUTE IMMEDIATE CUR.SQL3; SELECT V.REDO, V.UNDO INTO V_REDO1, V_UNDO1 FROM VW_REDO_UNDO_LHR V; SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL; ROLLBACK; IF V_ARCH = 'ARCHIVELOG' THEN UPDATE T_RU_160929_LHR T SET T.ARCH_REDO = V_REDO1 - V_REDO, T.ARCH_UNDO = V_UNDO1 - V_UNDO, T.ARCH_USE_TIME = (V_END_TIME - V_START_TIME) / 100, T.COMMENTS = T.COMMENTS || 'ARCHIVELOG:' || (SELECT COUNT(1) FROM T_A) || ' ' WHERE T.ID = CUR.ID;
ELSE UPDATE T_RU_160929_LHR T SET T.NOARCH_REDO = V_REDO1 - V_REDO, T.NOARCH_UNDO = V_UNDO1 - V_UNDO, T.NOARCH_USE_TIME = (V_END_TIME - V_START_TIME) / 100, T.COMMENTS = T.COMMENTS || 'NOARCHIVELOG:' || (SELECT COUNT(1) FROM T_A) || ' ' WHERE T.ID = CUR.ID;
END IF; COMMIT; EXECUTE IMMEDIATE 'ALTER TABLE T_A LOGGING'; EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML'; EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE'; BEGIN EXECUTE IMMEDIATE 'DROP INDEX IND_TA_LHR'; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN EXECUTE IMMEDIATE 'DROP TABLE T_RU_CTAS_LHR PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP;
END; |
4.2 开始实验
4.2.1 归档模式
增加日志组的个数，避免因为日志切换导致的等待。
|
SYS@lhrdb> select * from v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production
SYS@lhrdb> select GROUP#,BYTES,STATUS from v$log;
GROUP# BYTES STATUS ---------- ---------- ---------------- 1 104857600 ACTIVE 2 104857600 ACTIVE 3 104857600 ACTIVE 4 104857600 CURRENT 5 104857600 ACTIVE 6 104857600 ACTIVE
6 rows selected.
SYS@lhrdb> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 401 Next log sequence to archive 406 Current log sequence 406 SYS@lhrdb> SET TIMING ON SYS@lhrdb> exec PRO_TEST_RU_LHR;
PL/SQL procedure successfully completed.
Elapsed: 00:12:49.83 SYS@lhrdb> |
在PL/SQL DEVELOPER中查询结果：
SELECT D.*
FROM T_RU_160929_LHR D
ORDER BY D.ID;
4.2.2 非归档模式
|
SYS@lhrdb> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@lhrdb> startup mount ORACLE instance started.
Total System Global Area 1720328192 bytes Fixed Size 2247072 bytes Variable Size 486540896 bytes Database Buffers 1224736768 bytes Redo Buffers 6803456 bytes Database mounted.
SYS@lhrdb> alter database noarchivelog;
Database altered.
SYS@lhrdb> alter database open;
Database altered.
SYS@lhrdb> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 419 Current log sequence 424 SYS@lhrdb>
SYS@lhrdb> set timing on SYS@lhrdb> exec PRO_TEST_RU_LHR;
PL/SQL procedure successfully completed.
Elapsed: 00:13:31.67 |
在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;
4.3 实验结果
NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较_实验结果_LHR.png
NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较_实验结果_LHR.xls
根据以上的实验可以得到一些结论：关于表日志模式(LOGGING/NOLOGGING)、插入模式(APPEND/NOAPPEND)、数据库运行模式(归档/非归档)和并行模式下，REDO、UNDO和执行速度的情况大约如下表所示：
5 结论
（一）关于效率的结论：
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的执行速度。
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 IDNX11LOGGING 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 IDNX11LOGGING 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。