分析OracleNologging
这篇文章主要介绍“分析Oracle Nologging”,在日常操作中,相信很多人在分析Oracle Nologging问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”分析Oracle Nologging”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
成都创新互联是一家集网站建设,武鸣企业网站建设,武鸣品牌网站建设,网站定制,武鸣网站建设报价,网络营销,网络优化,武鸣网站推广为一体的创新建站企业,帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿,时刻以成就客户成长自我,坚持不断学习、思考、沉淀、净化自己,让我们为更多的企业打造出实用型网站。
Nologging:
在对大表插入数据的时候,经常会用到nologging选项。Nologging并不是不产生redo,
Nologging+append 只是不会对数据产生redo(但依然有其他的redo,例如数据字典变化产生的redo)。
同理 logging+append下undo也是大大地减少,减少的是数据的undo,是数据本身的undo,就如同redo的减少也一样,是数据本身的redo。
这和数据库是否产生redo和undo是不同的概念,比如空间分配的redo和undo,这就不是数据本身的变化。
Nologging 主要影响:
sql loader 直接路径加载
直接路径的insert(append hint)
create table as select
alter table move
创建和重建索引
在非归档模式下, 对于nologging 和 logging模式,使用 append,都不会对数据生成redo。
在归档模式下,只有将表置于nologging 模式,并且使用append 才不会对数据生成redo.
通过v$mystat视图来显示出当前会话产生的redo来进行显示效果:
select a.name,b.value from v$statname a,v$mystat b where a.statistic# = b.statistic# and a.name='redo size';
测试:
测试前一定要确定是否开始force_logging功能:
如果开启force_logging功能,那么nologging是不生效的。
SYS@prod>select force_logging from v$database;
FOR
---
NO
如果结果是YES,那么进行关闭
Alter database no force logging;
归档模式下的测试:
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 测试:
查看当前会话产生的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测试:
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
产生redo大小为54928。
SYS@prod>drop table test;
Table dropped.
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 10262796
产生redo数量为10262796.
结论:用nologging 创建表,不会对数据生成redo,仅对数据字典生成redo.
DML测试:
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测试就省略了,与delete效果一样。
结论:
对于 INSERT/UPDATE/DELETE的DML 操作, nologging 和 logging 模式没有什么区别。
使用直接路径append对insert测试:
表logging 使用 append插入:
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 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.
为了测试效果明显,重新启动一个会话。
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
结论:
对于logging 模式, 使用append hint 在生成redo 上没有什么变化
对于nologging模式,使用append hint 对数据没有生成redo,仅对数据字典生成了redo.
Alter table move测试:
表Logging模式下进行移动:
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 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模式下进行移动:
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 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
结论:表在nologging模式下进行移动,不会记录数据redo,仅会记录数据字典变化的redo。
非归档模式下的测试:
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测试
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
结论:在非归档模式下,nologging和logging方式建表差距不大。
2.DML测试:(仅测试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
结论:
对于 INSERT/UPDATE/DELETE的DML 操作, nologging 和 logging 模式没有什么区别
3.Insert+Append测试:
Logging:
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 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.
重启一个会话:
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
总结: 对于非归档模式,对于nologging 和 logging模式,使用 append,都不会对数据生成redo。
对于归档模式,只有nologging+Append,才不会对数据生成redo,仅对数据字典生成redo
两种模式下的测试结论:
归档模式下:
用nologging 创建表,不会对数据生成redo,仅对数据字典生成redo.
insert/update/delete 的DML 操作,在logging和nologging上没有区别
对于logging 模式, 使用append hint 在生成redo 上没有什么变化
对于nologging模式,使用append hint 对数据没有生成redo,仅对数据字典生成了redo。
非归档模式:
在非归档模式下,create table 在nologging 和 logging 模式差别不大。
对于 INSERT/UPDATE/DELETE的DML 操作, nologging 和 logging 上没有区别。
对于nologging 和 logging模式,使用 append,都不会对数据生成redo。
到此,关于“分析Oracle Nologging”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!
当前题目:分析OracleNologging
转载来源:http://scyanting.com/article/jgeccp.html