这篇文章主要介绍“分析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 selectalter table move 创建和重建索引在非归档模式下,对于nologging和logging模式,使用append,都不会对数据生成redo。在归档模式下,只有将表置于nologging模式,并且使用append才不会对数据生成redo.通过v$mystat视图来显示出当前会话产生的redo来进行显示效果:select a.name,b.value from v$statname a,v$mystat bwhere 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 ModeAutomatic archival EnabledArchive destination /u01/app/oracle/arch/pridbOldest online log sequence 230Next log sequence to archive 232Current log sequence 232Create 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 0Nologging测试: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 0SYS@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 deleteDelete:表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&g免费主机域名t;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
32991352Insert 与
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 0SYS@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模式,使用appendhint在生成redo上没有什么变化对于nologging模式,使用appendhint对数据没有生成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 0SYS@prod>alter table test move tablespace exampleSYS@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 0SYS@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 ModeAutomatic archival DisabledArchive destination /u01/app/oracle/arch/pridbOldest online log sequence 250Current log sequence 2521.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 0SYS@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
54476Nologging建表: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 0SYS@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:表NologgingSYS@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 0SYS@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 0SYS@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 0SYS@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
13752Nologging: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 0SYS@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模式,使用appendhint在生成redo上没有什么变化对于nologging模式,使用appendhint对数据没有生成redo,仅对数据字典生成了redo。
非归档模式:在非归档模式下,createtable在nologging和logging模式差别不大。对于 INSERT/UPDATE/DELETE的DML 操作, nologging 和 logging 上没有区别。对于nologging和logging模式,使用append,都不会对数据生成redo。到此,关于“分析Oracle Nologging”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注云技术网站,小编会继续努力为大家带来更多实用的文章!
小编给大家分享一下如何找出Mysql查询速度慢的SQL语句,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!启动Mysql时加参数–log-slow-queries来记录执行时间超过l…