分析Oracle Nologging

這篇文章主要介紹“分析Oracle Nologging”，在日常操作中，相信很多人在分析Oracle Nologging問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”分析Oracle Nologging”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

Nologging：

在對(duì)大表插入數(shù)據(jù)的時(shí)候，經(jīng)常會(huì)用到nologging選項(xiàng)。Nologging并不是不產(chǎn)生redo，

Nologging+append 只是不會(huì)對(duì)數(shù)據(jù)產(chǎn)生redo（但依然有其他的redo，例如數(shù)據(jù)字典變化產(chǎn)生的redo）。

同理 logging+append下undo也是大大地減少，減少的是數(shù)據(jù)的undo，是數(shù)據(jù)本身的undo，就如同redo的減少也一樣，是數(shù)據(jù)本身的redo。

這和數(shù)據(jù)庫(kù)是否產(chǎn)生redo和undo是不同的概念，比如空間分配的redo和undo，這就不是數(shù)據(jù)本身的變化。

Nologging 主要影響： 

sql loader 直接路徑加載

直接路徑的insert(append hint)

create table as select

alter table move  

創(chuàng)建和重建索引

在非歸檔模式下, 對(duì)于nologging 和 logging模式，使用 append，都不會(huì)對(duì)數(shù)據(jù)生成redo。 

在歸檔模式下,只有將表置于nologging 模式，并且使用append 才不會(huì)對(duì)數(shù)據(jù)生成redo. 

通過v$mystat視圖來顯示出當(dāng)前會(huì)話產(chǎn)生的redo來進(jìn)行顯示效果：

select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

測(cè)試：

測(cè)試前一定要確定是否開始force_logging功能：

如果開啟force_logging功能，那么nologging是不生效的。

SYS@prod>select force_logging from v$database;

FOR

---

NO

如果結(jié)果是YES，那么進(jìn)行關(guān)閉

Alter database no force logging；

歸檔模式下的測(cè)試：

SYS@prod>archive log list;

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        /u01/app/oracle/arch/pridb

Oldest online log sequence     230

Next log sequence to archive   232

Current log sequence        232

Create table 測(cè)試：

查看當(dāng)前會(huì)話產(chǎn)生的redo值：

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

Nologging測(cè)試：

SYS@prod>create table test nologging as select * from dba_objects;

Table created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             54928

產(chǎn)生redo大小為54928。

SYS@prod>drop table test;     

Table dropped.

Logging測(cè)試：

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>create table test logging as select * from dba_objects;

Table created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          10262796

產(chǎn)生redo數(shù)量為10262796.

結(jié)論：用nologging 創(chuàng)建表，不會(huì)對(duì)數(shù)據(jù)生成redo，僅對(duì)數(shù)據(jù)字典生成redo. 

DML測(cè)試：

Insert update delete

Delete：

表logging：

SYS@prod>delete from test;

86978 rows deleted.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          32996412

表nologging：

SYS@prod>delete from test;

86978 rows deleted.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          32991352

Insert 與 update測(cè)試就省略了，與delete效果一樣。

結(jié)論：

對(duì)于 INSERT/UPDATE/DELETE的DML 操作， nologging 和 logging 模式?jīng)]有什么區(qū)別。

使用直接路徑append對(duì)insert測(cè)試：

表logging 使用 append插入：

SYS@prod>create table test logging as select * from dba_objects;

Table created.

為了測(cè)試效果明顯，重新啟動(dòng)一個(gè)會(huì)話。

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>insert /*+APPEND*/ into test select * from test;

86980 rows created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          10239296

表nologging使用append插入：

SYS@prod>create table test nologging as select * from dba_objects;

Table created.

為了測(cè)試效果明顯，重新啟動(dòng)一個(gè)會(huì)話。

SYS@prod>insert /*+APPEND*/ into test select * from test;

86980 rows created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             13884

結(jié)論：

對(duì)于logging 模式， 使用append hint 在生成redo 上沒有什么變化

對(duì)于nologging模式，使用append hint 對(duì)數(shù)據(jù)沒有生成redo，僅對(duì)數(shù)據(jù)字典生成了redo.

Alter table move測(cè)試：

表Logging模式下進(jìn)行移動(dòng)：

SYS@prod>create table test logging as select * from dba_objects;

Table created.

重啟一個(gè)會(huì)話

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>alter table test move tablespace example

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          10330784

表nologging模式下進(jìn)行移動(dòng)：

SYS@prod>create table test nologging as select * from dba_objects;

Table created.

重啟一個(gè)會(huì)話

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>alter table test move tablespace example;

Table altered.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             71712

結(jié)論：表在nologging模式下進(jìn)行移動(dòng)，不會(huì)記錄數(shù)據(jù)redo，僅會(huì)記錄數(shù)據(jù)字典變化的redo。

非歸檔模式下的測(cè)試：

SYS@prod>archive log list;

Database log mode        No Archive Mode

Automatic archival        Disabled

Archive destination        /u01/app/oracle/arch/pridb

Oldest online log sequence     250

Current log sequence        252

1.create table測(cè)試

Logging建表：

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>create table test logging as select * from dba_objects;

Table created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             54476

Nologging建表：

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>create table test nologging as select * from dba_objects;

Table created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             53700

結(jié)論：在非歸檔模式下，nologging和logging方式建表差距不大。

2.DML測(cè)試：（僅測(cè)試insert）

Insert：

表Nologging

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>insert into test select * from test;

86980 rows created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          10153240

表Logging：

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>insert into test select * from test;

86980 rows created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          10162700

結(jié)論：

對(duì)于 INSERT/UPDATE/DELETE的DML 操作， nologging 和 logging 模式?jīng)]有什么區(qū)別

3.Insert+Append測(cè)試：

Logging：

SYS@prod>create table test logging as select * from dba_objects;

Table created.

重啟一個(gè)會(huì)話

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>insert /*+APPEND*/ into test select * from test;

86980 rows created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             13752

Nologging：

SYS@prod>create table test nologging as select * from dba_objects;

Table created.

重啟一個(gè)會(huì)話：

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size          0

SYS@prod>insert /*+APPEND*/ into test select * from test;

86980 rows created.

SYS@prod>select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';

NAME              VALUE

---------------------------------------------------------------- ----------

redo size             13884

總結(jié)： 對(duì)于非歸檔模式，對(duì)于nologging 和 logging模式，使用 append，都不會(huì)對(duì)數(shù)據(jù)生成redo。 

對(duì)于歸檔模式，只有nologging+Append，才不會(huì)對(duì)數(shù)據(jù)生成redo，僅對(duì)數(shù)據(jù)字典生成redo

兩種模式下的測(cè)試結(jié)論：

歸檔模式下：

用nologging 創(chuàng)建表，不會(huì)對(duì)數(shù)據(jù)生成redo，僅對(duì)數(shù)據(jù)字典生成redo. 

insert/update/delete 的DML 操作，在logging和nologging上沒有區(qū)別

對(duì)于logging 模式， 使用append hint 在生成redo 上沒有什么變化

對(duì)于nologging模式，使用append hint 對(duì)數(shù)據(jù)沒有生成redo，僅對(duì)數(shù)據(jù)字典生成了redo。

非歸檔模式：

在非歸檔模式下，create table 在nologging 和 logging 模式差別不大。

對(duì)于 INSERT/UPDATE/DELETE的DML 操作， nologging 和 logging 上沒有區(qū)別。

對(duì)于nologging 和 logging模式，使用 append，都不會(huì)對(duì)數(shù)據(jù)生成redo。

到此，關(guān)于“分析Oracle Nologging”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！