Nologging到底何时才能生效?

本文围绕Nologging何时生效展开研究。通过测试发现,Nologging设置与数据库运行模式有关。在非归档模式下,常规表的insert append只产生少量redo;归档模式下,常规表的insert append产生和insert同样的redo。对于Nologging的table,无论归档与否,只有append才能减少redo生成。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Nologging到底何时才能生效?

link:

http://www.itpub.net/242761.html

http://www.itpub.net/239905.html

?

我们知道,Nologging只在很少情况下生效
通常,DML操作总是要生成redo的

这个我们不多说.

关于Nologging和append,一直存在很多误解.
经过一系列研究,终于发现了Nologging的真相.

我们来看一下测试:


1.Nologging的设置跟数据库的运行模式有关

a.数据库运行在非归档模式下:


SQL> archive log list;
Database log mode????????????? No Archive Mode
Automatic archival???????????? Enabled
Archive destination??????????? /opt/oracle/oradata/hsjf/archive
Oldest online log sequence???? 155
Current log sequence?????????? 157

SQL> @redo
SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

???? VALUE
----------
???? 63392

SQL>
SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 1150988

SQL>
SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 1152368

SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;

REDO_APPEND?????? REDO
----------- ----------
?????? 1380??? 1087596

SQL> drop table test;

Table dropped.

我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo

b.在归档模式下

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area? 235999908 bytes
Fixed Size?????????????????? 451236 bytes
Variable Size???????????? 201326592 bytes
Database Buffers?????????? 33554432 bytes
Redo Buffers???????????????? 667648 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo
SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

???? VALUE
----------
???? 56288

SQL>
SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 1143948

SQL>
SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 2227712

SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual;

REDO_APPEND?????? REDO
----------- ----------
??? 1083764??? 1087660

SQL> drop table test;

Table dropped.

我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的

通过Logmnr分析日志得到以下结果:

SQL> select operation,count(*)
? 2? from v$logmnr_contents
? 3? group by operation;

OPERATION????????????????????????? COUNT(*)
-------------------------------- ----------
COMMIT?????????????????????????????????? 17
DIRECT INSERT???????????????????????? 10470??
INTERNAL???????????????????????????????? 49
START??????????????????????????????????? 17
????????????????????????????????????????? 1
?????????????????????????????????????????
我们注意到这里是DIRECT INSERT,而且是10470条记录,也就是每条记录都记录了redo.


2.对于Nologging的table的处理

a. 在归档模式下:
SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

???? VALUE
----------
?? 2270284

SQL>
SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 3357644

SQL>
SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 3359024

SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;

REDO_APPEND?????? REDO
----------- ----------
?????? 1380??? 1087360

SQL> drop table test;

Table dropped.

我们注意到,只有append才能减少redo


b.在非归档模式下:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area? 235999908 bytes
Fixed Size?????????????????? 451236 bytes
Variable Size???????????? 201326592 bytes
Database Buffers?????????? 33554432 bytes
Redo Buffers???????????????? 667648 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo
SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

???? VALUE
----------
???? 56580

SQL>
SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 1144148

SQL>
SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

???? VALUE
----------
?? 1145528

SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;

REDO_APPEND?????? REDO
----------- ----------
?????? 1380??? 1087568

SQL>??????????????????????????????????????????

同样只有append才能减少redo的生成.

这就是通常大家认识的情况.?????????????????????????????????????????

### nologging in Database Operations or Configuration In the context of database operations, `NOLOGGING` refers to a mode where certain operations do not generate redo logs. This can significantly improve performance during specific tasks such as direct-path inserts, creating indexes, or performing bulk loads because fewer writes occur to the online redo logs[^1]. However, this comes at the cost of reduced recoverability since these changes will not be recorded in the redo stream. For instance, when an operation is executed with the `NOLOGGING` option, it bypasses the generation of full image undo records and skips writing detailed information into the redo logs. Consequently, if media recovery becomes necessary after such an event has taken place, any data modified under NOLOGGING conditions might become unrecoverable unless other mechanisms (such as backup strategies) have been implemented beforehand[^2]. It should also be noted that while configuring standby databases—whether logical or physical—the use of NOLOGGING could complicate synchronization efforts due to missing entries within archived log files required for applying updates on secondary systems[^3]. Furthermore, pluggable databases operating in MOUNTED state may restrict some activities involving unlogged transactions depending upon their administrative settings; thus careful planning around usage scenarios must precede implementation decisions regarding whether enabling/disabling logging features aligns best practice guidelines set forth per organizational requirements[^4]. Additionally, there exists functionality allowing preservation states across container restart cycles which indirectly impacts considerations surrounding logged vs no-logging configurations given how they interact together inside multi-tenant environments managed via Oracle's architecture framework[^5]. ```sql -- Example of Creating Index With No Logging Option Enabled CREATE INDEX idx_example ON table_name(column_name) NOLOGGING; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值