Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较
Tags： APPENDnologgingOracleparallelredoundo
👉 本文共约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';
- 查看当前会话的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;
实验过程
实验环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | --记录REDO和UNDO量的视图 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; --准备中间表,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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | --记录测试结果 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; |
插入完成后查询结果：
1 2 3 4 5 6 7 8 9 10 | 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的量，至于该存过的算法大家自己看吧。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | --创建存储过程，用来测试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; |
开始实验
归档模式
增加日志组的个数，避免因为日志切换导致的等待。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 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;
非归档模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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;
实验结果
根据以上的实验可以得到一些结论：关于表日志模式(LOGGING/NOLOGGING)、插入模式(APPEND/NOAPPEND)、数据库运行模式(归档/非归档)和并行模式下，REDO、UNDO和执行速度的情况大约如下表所示：
结论
关于效率的结论：
- INSERT INTO： 在APPEND提示的情况下，NOLOGGING或NOARCHIVELOG满足一个即产生少量的REDO和UNDO;另外PARALLEL默认是以DIRECT的方式进行加载数据的，一般在并行情况下SQL执行速度提高。
- CTAS：CTAS本身就是一种DIRECT的操作，归档模式+NOLOGGING模式产生少量REDO；并行模式下时间大幅度减少，但生成的REDO和UNDO成倍增长。
- ALTER TABLE ... MOVE：ARCHIVELOG+NOLOGGING模式产生少量REDO；并行模式下时间大幅度减少，但生成的REDO和UNDO成倍增长 。
- 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使用注意事项：
建议不要经常使用APPEND，这样表空间会一直在高水位上，除非你这个表只插不删。
以APPEND方式插入记录后，要执行COMMIT，才能对表进行查询。否则会出现错误：ORA-12838: 无法在并行模式下修改之后读/修改对象。
APPEND对INSERT INTO ... VALUES语句不起作用，需要使用11gR2的APPEND_VALUES来提示才可以直接路径加载，注意：APPEND_VALUES对INSERT INTO ... SELECT也起作用。
APPEND使用HWM之上的块,减少了搜索FREELIST上块的时间。
在归档模式下：NOLOGGING+APPEND才会显著减少REDO数量；在非归档模式下:单独APPEND即可减少REDO数量。
APPEND不会减少相关表的索引上产生的REDO数量。
APPEND的插入操作是给表加上6级排它锁，会阻塞表上的所有DML语句。
每提交一次，就会取一个新的BLOCK存放，高水位就上推一个BLOCK，若在LOOP循环中，外部循环100W次，但是每循环一次只有一行符合条件的数据插入，这样，大量单条/*+APPEND*/插入，就会使得表急剧增大，除对INSERT本身造成性能影响之外，对以后的SELECT、UPDATE、DELETE更是带来更巨大的性能影响。
NOLOGGING使用注意事项：
NOLOGGING插完后最好对表做个备份。生产上重要的表不建议设置NOLOGGING属性。
如果库处在FORCE LOGGING模式下，此时的NOLOGGING方式是无效的。
PDML使用注意事项：
- 必须使用ALTER SESSION ENABLE PARALLEL DML;才可以启动PDML。
