Oracle Nologging And Append

2023-10-14 23:08
文章标签 oracle append nologging

本文主要是介绍Oracle Nologging And Append，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

转载地址

http://gaobo424.iteye.com/blog/1330010


自己测试 如下

环境：10.2.0.4(单机，没开归档)

SQL> alter table t_dz_test nologging;

Table altered.

SQL> set timing on
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;  

11677713 rows created.

Elapsed: 00:00:10.01
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> set table t_dz_test logging;
SP2-0158: unknown SET option "table"
SQL> alter table t_dz_test logging;

Table altered.

Elapsed: 00:00:00.02
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;  

11677713 rows created.

Elapsed: 00:00:12.23
SQL>


环境：10.2.0.5（rac  开了归档）

开始t_dz_test是logging，第一次append耗时45秒，第二次nogging时耗时19秒，第三次改成logging时耗时17秒。。。最后一次nologging耗时45秒

我想看一下redolog大小，开了执行计划，结果报错ora-12838,关了之后不报错，，不知道这个报错之后的准不准，nologging的redolog是小了，但是最后一次耗时45秒。


下面是同事给的图片，，，貌似我测试的没有这么理想


SQL> 
SQL> 
SQL> create table t_dz_test as select * from t_user_mobile_dz where rownum<=1;Table created.SQL> set timing on
SQL> set autot trac
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autot  trace
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12706742 rows created.Elapsed: 00:00:51.16Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------1148864  recursive calls662012  db block gets1380625  consistent gets0  physical reads937864108  redo size345  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client2  sorts (memory)0  sorts (disk)12706742  rows processedSQL> 
SQL> rollback;Rollback complete.Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> 
SQL> 
SQL> insert into t_dz_test  select  * from t_user_mobile_dz;12706882 rows created.Elapsed: 00:00:51.56Execution Plan
----------------------------------------------------------
Plan hash value: 2802676981--------------------------------------------------------------------------------
------| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time|--------------------------------------------------------------------------------
------|   0 | INSERT STATEMENT  |                  |    12M|   674M| 26304   (2)| 00:0
5:16 ||   1 |  TABLE ACCESS FULL| T_USER_MOBILE_DZ |    12M|   674M| 26304   (2)| 00:0
5:16 |--------------------------------------------------------------------------------
------Statistics
----------------------------------------------------------371  recursive calls1080626  db block gets341476  consistent gets304  physical reads918012192  redo size373  bytes sent via SQL*Net to client527  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)12706882  rows processedSQL> SQL> 
SQL> rollback;Rollback complete.Elapsed: 00:00:06.83
SQL> set autot off 
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12706936 rows created.Elapsed: 00:00:45.87
SQL> 
SQL> rollback;Rollback complete.Elapsed: 00:00:00.00
SQL> alter table t_dz_test nologging;Table altered.Elapsed: 00:00:00.01
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12706968 rows created.Elapsed: 00:00:19.75
SQL> rollabck;
SP2-0042: unknown command "rollabck" - rest of line ignored.
SQL> rollback;Rollback complete.Elapsed: 00:00:00.01
SQL> alter table t_dz_test logging;Table altered.Elapsed: 00:00:00.01
SQL> 
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12707115 rows created.Elapsed: 00:00:17.16
SQL> rollback;Rollback complete.Elapsed: 00:00:00.00
SQL> set autot  trace
SQL> 
SQL> 
SQL> 
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12707172 rows created.Elapsed: 00:00:18.80Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------0  recursive calls1277659  db block gets120968  consistent gets0  physical reads903051200  redo size352  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)12707172  rows processedSQL> alter table t_dz_test logging;Table altered.Elapsed: 00:00:00.04
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12707206 rows created.Elapsed: 00:00:44.34Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------1148211  recursive calls1413593  db block gets1379882  consistent gets0  physical reads937850524  redo size353  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client7  sorts (memory)0  sorts (disk)12707206  rows processedSQL> 
SQL> truncate table t_dz_test;Table truncated.Elapsed: 00:00:46.18
SQL> SQL> SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> alter table t_dz_test nologging;Table altered.Elapsed: 00:00:00.01
SQL> insert /*+append*/ into t_dz_test  select  * from t_user_mobile_dz;12708501 rows created.Elapsed: 00:00:47.32Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN reportStatistics
----------------------------------------------------------1149041  recursive calls662152  db block gets1380961  consistent gets2  physical reads35775004  redo size354  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client3  SQL*Net roundtrips to/from client7  sorts (memory)0  sorts (disk)12708501  rows processedSQL> SQL> 
SQL> 
SQL> 
SQL> rollback;Rollback complete.Elapsed: 00:00:00.00
SQL>





对于logging的理解总是以为表的日志设置为NO它就不会去产生日志了，其实不是的下面是对于logging的一些解释和试验。


Logging介绍

可以采用nologging模式执行以下操作：

1．索引的创建和ALTER（重建）。
2．表的批量INSERT（通过/*+append */提示使用“直接路径插入“。或采用SQL*Loader直接路径加载）。表数据生成少量redo，但是所有索引修改会生成大量redo（尽管表不生成日志，但这个表上的索引却会生成redo！）。


3．Lob操作（对大对象的更新不必生成日志）。
4．通过create table as select创建表。
5．各种alter table操作，如move和split。
在一个archivelog模式的数据库上，如果nologging使用得当，可以加快许多操作的速度，因为它能显著减少生成的重做日志量。假设你有一个表，需要从一个表空间移到另一个表空间，原先需要N小时才能完成的操作可能只需要N/2小时。要想适当地使用这个特性，需要DBA的参与，或者必须与负责数据库备份和恢复（或任何备用数据库）的人沟通。如果这个人不知道使用了这个特性，一旦出现介质失败，就可能丢失数据，或者备用数据库的完整性可能遭到破坏，对此一定要三思。

对象Logging状态查询

通过此查询SQL语句查询表的logging状态

SELECT T.TABLE_NAME, T.LOGGING
  FROM USER_TABLES T
WHERE T.TABLE_NAME LIKE '%TEST_FUTUFARES%';

Create和Insert的Logging测试
Create table …. as select ….及 insert into …..select ….测试

改变logging状态值的方法：

ALTER TABLE table_name NOLOGGING/logging;

通过以上测试其实表在Nologging与Logging状态时插入2百万的数据耗时差不多的，也就是说DML不是说不记日志而只是在特定的情况下是不记日志的，比如用SQL*Loader直接装载及INSERT /*+Append*/选项直接路径装载，也就是说不管是否是NOLOGGING状态DML操作正常情况下肯定会产生日志。

Nologging模式下数据库操作只有如下几种情况下不产成redo记录：

１、用sql*load的direct load方式时，不采用redo记录
已测试
２、用insert的direct方式，即在append方式insert
已测试

３、create table ….as select….
已测试

４、create index
create index  TEST_FUTUFARES2_log  on TEST_FUTUFARES2 (FARE_KIND,FUTUFARE_TYPE) nologging;

创建索引要想产生极少的REDO必须要按上面的那种方式创建索引，按照上面的那种方法去创建索引不管表的日志是处在nologging还是logging状态下都是一样都会产生很少的REDO日志，否则还是会产生大量的REDO日志。
５、alter table ... move partition
６、alter table ... split partition
７、alter index ... split partition
８、alter index ... rebuild
９、alter index ... rebuild partition
１０、INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line





Append介绍


非归档模式情况下：

1．查看当前会话所有产生的REDO总量

表处于nologging状态:

SQL> set timing on;
SQL>INSERT  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 36.25 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                   VALUE
--------------------------------------------------------------------------
Redo size                                             113495212

SQL>INSERT /*+append*/ INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 9.062 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
Redo size                                                113560764

SQL>select 113560764-113495212 from dual;
113560764-113495212
-------------------
              65552


表处于logging状态:
   对于此测试得出的结果其实跟上面的nologging得出的测试结果几乎是一模一样的，就不贴出来了。



归档模式情况下：

表处于logging状态:

SQL> INSERT  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 44.031 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                     VALUE
--------------------------------------------------------------------------
Redo size                                              113460280

SQL>INSERT /*+append*/  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 24.297 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
Redo size                                               223253980

SQL> select 223253980-113460280 from dual;
223253980-113460280
-------------------
          109793700

表处于nologging状态:

SQL> INSERT /*+append*/  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 6.391 seconds



SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
redo size                                               223576712

SQL> select 223576712-223253980 from dual;
223576712-223253980
-------------------
             322732



2．查看全局数据库redo生成量，可以通过v$sysstat视图看到

SQL> select name,value  from v$sysstat where name='redo size';
NAME                                                    VALUE
--------------------------------------------------------------------------
Redo size                                               122314360


总结：
关于Nologging与append测试的一些总结，通过上面的SQL语句查看可以得出在大量数据插入过程的语句中加入/*+append*/的这个SQL语句产生的REDO日志明显示是会少同时时间节约了很多，当然这样可能会影响备份因此nologging加载数据后要做一个数据库的全备。

insert append并不是在任何时候都可以节省时间的以下是测试的一些总结：



第一种情况：

database为archivelog状态，这种情况下，就算你用insert append也是不一定提高插入效率的。但是如果你将目标表设置为nologging，然后再使用insert append就会很快。
第二种情况：

database为noarchivelog状态，如果在这种情况下直接采用insert方法而不加入append向表中插入数据，占用的redo空间的大小与archivelog状态下占用的大小是相当的，不论表是否为nologging。但是如果采用insert append方法的话，通过redo的占用值大家可以发现，不论表是否为nologging，所占用的redo的大小都是很小的。也就说明：在数据库为noarchivelog的状态下，采用insert append方法，如果表不是nologging，系统也会自动将表转换为nologging（即在执行insert append之前，先执行一个alter table arch1 nologging）。
第三种情况：

如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo数量可能比表的redo数量还要大。用insert append可以实现直接路径加载速度是快很多，但有一点需要注意: insert append时在表上加”6”类型的锁，会阻塞表上的所有DML语句，因此在有业务运行的情况下要慎重使用。若同时执行多个insert append对同一个表并行加载数据，并不一定会提高速度，因为每一时刻只能有一个进程在加载(排它锁造成)à此观点是在网上查到的对于此疑问对于锁的问题此兄弟可能不是太理解，对于ORACLE数据库来说只要DML特别是insert操作他肯定会将表给锁住而且是独占锁除非进行commit,rollback，及其它的DDL操作来释放否则锁会一直独占导致其它的DML操作无法进行正常的操作，而跟所谓的APPEND无关。

以下是关于表模式(LOGGING/NOLOGGING)，插入模式(APPEND/NOAPPEND)，数据库运行模式(归档/非归档)，REDO日志产生的关系
数据库模式            表模式              插入模式            REDO生成
----------------------------------------------------------------------------------
ARCHIVELOG          LOGGING          APPEND                有REDO
                                                         NO APPEND          有REDO

                               NOLOGGING     APPEND                 无REDO
                                                         NO APPEND           有REDO
----------------------------------------------------------------------------------
NOARCHIVELOG    LOGGING          APPEND                 无REDO
                                                        NO APPEND           有REDO
                              NOLOGGING     APPEND                  无REDO
                                                        NO APPEND           有REDO
----------------------------------------------------------------------------------

这篇关于Oracle Nologging And Append的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/213746。

相关文章

Oracle type （自定义类型的使用）

Oracle type （自定义类型的使用）

oracle - type   type定义： oracle中自定义数据类型 oracle中有基本的数据类型，如number，varchar2，date，numeric，float....但有时候我们需要特殊的格式， 如将name定义为（firstname,lastname）的形式，我们想把这个作为一个表的一列看待，这时候就要我们自己定义一个数据类型 格式 ：create or repla
阅读更多...
ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g，在使用Database configuration Assistant创建数据库时，在创建到85%的时候报错，错误如下： 解决办法： 在listener.ora中增加对BlueAeri-PC或ip地址的侦听，具体步骤如下： 1.启动Net Manager，在“监听程序”--Listener下添加一个地址，主机名写计
阅读更多...
Oracle Start With关键字

Oracle Start With关键字

Oracle Start With关键字 前言 旨在记录一些Oracle使用中遇到的各种各样的问题. 同时希望能帮到和我遇到同样问题的人. Start With (树查询) 问题描述: 在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下: ID, DSC, PID; 三个字段, 分别表示 当前标识的 ID(主键), DSC 当
阅读更多...
oracle分页和mysql分页

oracle分页和mysql分页

mysql 分页 --查前5 数据select * from table_name limit 0,5 select * from table_name limit 5 --limit关键字的用法：LIMIT [offset,] rows--offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。   oracle 分页 --查前1-9
阅读更多...
ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理

ORACLE语法-包(package)、存储过程(procedure)、游标(cursor)以及java对Result结果集的处理

陈科肇 示例： 包规范 CREATE OR REPLACE PACKAGE PACK_WMS_YX IS-- Author : CKZ-- Created : 2015/8/28 9:52:29-- Purpose : 同步数据-- Public type declarations，游标 退休订单TYPE retCursor IS REF CURSOR;-- RETURN vi_co_co
阅读更多...
Oracle主键和外键详解及实用技巧

Oracle主键和外键详解及实用技巧

在 Oracle 数据库中，主键（Primary Key）和外键（Foreign Key）用于维护数据库表之间的数据完整性。 1. 主键（Primary Key） 主键是一列或多列，能够唯一标识表中的每一行。表中只能有一个主键，并且主键列不能为空（即 NOT NULL）。 特性： 唯一性：主键中的每一个值都是唯一的，不能重复。非空性：主键列不能包含 NULL 值。索引：Oracle 自动为
阅读更多...
分享MSSQL、MySql、Oracle的大数据批量导入方法及编程手法细节

分享MSSQL、MySql、Oracle的大数据批量导入方法及编程手法细节

1：MSSQL SQL语法篇： BULK INSERT      [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]         FROM 'data_file'        [ WITH       (      [ [ , ] BATCHSIZE = batch_siz
阅读更多...
Oracle start with connect BY 死循环

Oracle start with connect BY 死循环

解决办法 检查start with前有没有where条件， 如果有的话，套一层select，再 Oracle start with connect BY
阅读更多...
Java调用Oracle存储过程一

Java调用Oracle存储过程一

一、通过PL/SQL工具连接上Oracle数据库，创建表zx_test_procedure 二、创建存储过程 ①无返回值的存储过程 存储过程为： create or replace procedure testa (para1 in varchar2,para2 in varchar2) as begin insert into zx_test_procedure(i_id,i_n
阅读更多...
PL/SQL工具创建Oracle数据库表，实现id字段的自动递增

PL/SQL工具创建Oracle数据库表，实现id字段的自动递增

通过PL/SQL工具，创建Oracle数据库表，如何实现字段ID自动递增； Oracle的自增需要依靠序列和触发器共同实现 比如：先创建一个表 create table test (id int primary key, name varchar2(10)); 创建一个序列 create sequence test_seq increment by 1 start with 1  min
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2