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

于 2021-04-15 18:27:17 发布
阅读量179 收藏
点赞数
文章标签： oracle 执行速度

--记录测试结果

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;

weixin_39670849
关注
oracle append parallel,大量数据快速插入方法探究[nologging+parallel+append]
weixin_27717759的博客
04-04 2062
大量数据快速插入方法探究快速插入千万级别的数据，无非就是nologging+parallel+append。1环境搭建构建一个千万级别的源表，向一个空表insert操作。参考指标：insert动作完成的实际时间。SQL>droptabletest_empcascadeconstraintspurge;Tabledropped.SQL>createtabletest_e...
oracle日志模式，logging的用法，force logging的用法，nologging的用法
最新发布
lz_N_one的博客
12-05 2102
oracle日志模式，logging的用法，force logging的用法，nologging的用法
【知识点整理】NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较...
coco3600的博客
09-30 208
【知识点整理】Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下，REDO、UNDO和执行速度的比较 1BLOG文档结构图 ...
oracle 优化之parallel和append
D2cOneluo的博客
08-12 2219
并发parallel和append /+append/ 注意事项 1.在非归档模式中 nologing和 logging 都不会产生大量的redo日志,但是在归档模式中如果是logging 表会产生大量的redo日志 但是nologging表就不会产生. (建议在归档模式中大量插入数据的时候先把表改成nologging模式’’ ALTER TABLE table_name NOLOGGING 然后...
oracle appen,oracle insert、append、parallel、随后查询的redo与磁盘读写
weixin_42544838的博客
04-03 233
SQL> set autotrace traceonly statistics;SQL> insert into big_table_dir_test1 select * frombig_table_dir_test;2853792rows created.Statistics-------------------------------------------------------...
oracle中设置数据表并行dml,批量DML操作大汇总
weixin_42246401的博客
04-05 857
(一)、CTAS方式：create table t1asselect * from t;为了提高速度可以使用下面方法，来减少插入过程中产生的日志，并且可以制定并行度：create table t1 nologging parallel(degree 2) as select * from t;(二)、常规的插入方式：insert into t1 select * from t;为了提高速度可以使用...
oracle的nologging
甲骨虫的家
03-19 3229
官方如是说： Controlling the Writing of Redo Records For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, supp
Direct Path Insert与APPEND, PARALLEL的梳理与小结
06-11 5233
Oracle里面的写入数据方式分为2种，一个是传统方式写入(conventional insert)，另外一个是直接路径写入(direct path insert)。   传统方式写入: Oracle会重用表里面空闲空间，并且写入会先写入到buffer cache。   直接路径写入：Oracle不重用表里面的空闲空间，直接写入到新分配的块，并且数据直接写入到data file，不写入到b
oracle append parallel,oracle 优化之parallel和append
weixin_29774037的博客
04-04 838
并发parallel和append/*+append*/注意事项1.在非归档模式中 nologing和logging 都不会产生大量的redo日志,但是在归档模式中如果是logging 表会产生大量的redo日志 但是nologging表就不会产生.(建议在归档模式中大量插入数据的时候先把表改成nologging模式'ALTER TABLE table_name NOLOGGING' 然后 等...
ORACLE性能优化简述
m0_38036502的博客
05-26 375
ORACLE性能 ORALCE插入：ORACLE用PARALLEL并行+APPEND直接加载技术+NOLOGGING不记日志技术 比直接INSERT插入效率能提升30%以上。 DB2插入：通过LOAD FROM CURSOR可以实现在不通的表空间或数据库之间快速轻松地转移数据的方法， 比INSET INTO效率要相对快一些，但是如果执行中出现错误，容易导致表不可用。 1.SELECT子句中避免使用‘*’，多表查询时，列名前有表名标示； 2.能用UNION ALL的情况绝不用UNION； 3.减少数据库访问次
oracle并行parallel update两张表_Oracle并行模式（Parallel）
weixin_34096885的博客
02-02 1286
1. 实例(1)insert 加速insert into /*+ append parallel nologging */ dcustcomposmsgselect /*+ parallel(e,18)*/ * from dcustcomposmsg_new e;commit;(2)select create加速CREATE TABLE TEMP_DCUST_GRADE NOLOGGING PAR...
oracle多线程索引,Oracle多线程并行使用、关联与指定索引执行
weixin_36383252的博客
04-04 1364
CREATE TABLE T_SMAINT_2016012703 parallel 4 nologging ASSELECT /*+parallel(4) leading(s a) use_hash(A) index(s IDX_CS_SERVICE_RECORD_MD2_04)*/S.SMAINT_ID, S.SMAINT_DESCFROMT_CS_SERVICE_RECORD SJOINT_C...
oracle12c nolog,12c 关于nologging的一些方法-补充篇2
weixin_35710893的博客
04-09 268
为一个包含上千万数据量的大表创建索引是一个不小的消耗。为了尽快完成任务可以采用nologging和并行相结合的方法。虽然使用nologging的方式创建索引会有小小的隐患，也就是该索引不受redo保护，一旦介质故障该索引无法恢复。此时访问索引会出现ORA-26040: Data block was loaded using the NOLOGGING opation但索引和表不一样，不能用了可以r...
Oracle Delete分批提交
Maker
12-10 5532
1、Oracle Delete操作会产生redo log，undo log，即使delete 语句中加上nologging 参数，还是会记录日志的，这样保证了数据安全性，利于rollback。为了不使undo表空间被撑爆，可能需要分批提交，以下是分批提交语句： declare    cnt number(10):=0;    i number(10):=0;     begin    s
关于oracle的NOLOGGING
wuhongze的专栏
04-09 664
可以采用NOLOGGING模式执行以下操作： 1 索引的创建和ALTER（重建）。 2 表的批量INSERT（通过/*+APPEND */提示使用“直接路径插入“。或采用SQL*Loader直接路径加载）。表数据不生成redo，但是 所有索引修改会生成redo，但是所有索引修改会生成redo（尽管表不生成日志，但这个表上的索引却会生成redo！）。 3 LOB操作（对大对象的更新不必生成日...
Oracle中的nologging
热门推荐
邵冬梁的专栏
04-12 3万+
Oracle数据库有联机重做日志，这个日志是记录对数据库所做的修改，比如插入，删除，更新数据等，对这些操作都会记录在联机重做日志里。一般数据库至少要有2个联机重做日志组。当一个联机重做日志组被写满的时候，就会发生日志切换，这时联机重做日志组2成为当前使用的日志，当联机重做日志组2写满的时候，又会发生日志切换，去写联机重做日志组1，就这样反复进行。如果数据库处于非归档模式,联机日志在切换时就
常见dml、ddl语句使用nologging选项所生成的redo和undo大小比较
小麦苗DBA宝典
08-11 1103
说明：反映undo、redo占用量的统计指标是： undo change vector size redo size DDL/DML Operations Direct-path nologging parallel noarchivelog mode archivel...
Oracle中的NOLOGGING、APPEND、ARCHIVE与PARALLEL对REDO、UNDO及执行速度的影响
"这篇博客文章主要探讨了Oracle数据库中NOLOGGING、APPEND、ARCHIVE和PARALLEL等特性对REDO日志、UNDO段以及执行速度的影响。作者提供了系统和会话级别查询REDO和UNDO量的方法，并详细对比了在不同模式下这些参数的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值