对表进行dml操作时影响产生日志量的几个因素
来源:互联网 发布:怎样成为淘宝会员 编辑:程序博客网 时间:2024/05/14 03:18
归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive_log
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
1.创建测试表hat_objects
SQL> create table hat_objects as select * from all_objects where 1<>1;
Table created
2.打开执行计划
SQL> set autotrace traceonly statistics;
3.插入数据
SQL> insert into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5877 recursive calls
5779 db block gets
80216 consistent gets
0 physical reads
4684152 redo size
675 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40685 rows processed
4.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
774 db block gets
79281 consistent gets
0 physical reads
4692132 redo size
660 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40685 rows processed
注:直接insert与使用append提示insert产生的日志大小相差无几,未减少日志的产生。
5.设置hat_objects为nologging模式
SQL> alter table hat_objects nologging;
Table altered.
6.插入数据
SQL> insert into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5232 recursive calls
5104 db block gets
80000 consistent gets
0 physical reads
4630652 redo size
676 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40685 rows processed
7.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
776 db block gets
79281 consistent gets
0 physical reads
19648 redo size
662 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40685 rows processed
注:当表hat_objects设置为nologging模式后,使用append提示后insert,日志的产生量明显比直接
insert少。
非归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/archive_log
Oldest online log sequence 29
Current log sequence 31
1.创建测试表
SQL> create table hat_objects as select * from all_objects where 1<>1;
Table created.
2.插入数据
SQL> insert into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
6773 recursive calls
6276 db block gets
80723 consistent gets
2 physical reads
4737072 redo size
680 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
40685 rows processed
3.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;
40684 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
762 db block gets
79281 consistent gets
0 physical reads
18332 redo size
664 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40684 rows processed
注:直接insert与使用append提示insert产生的日志大小相差很大,减少了日志的产生。
4.设置表hat_objects为nologging模式
SQL> alter table hat_objects nologging;
Table altered.
5.插入数据
SQL> insert into hat_objects select * from all_objects;
40684 rows created.
Statistics
----------------------------------------------------------
5454 recursive calls
5104 db block gets
80005 consistent gets
0 physical reads
4630688 redo size
680 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
40684 rows processed
6.使用提示插入数据
SQL> insert/*+ append */ into hat_objects select * from all_objects;
40684 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
782 db block gets
79281 consistent gets
0 physical reads
19648 redo size
664 bytes sent via SQL*Net to client
598 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40684 rows processed
注:当表hat_objects设置为nologging模式后,使用append提示后insert,日志量比直接insert少,
但与表hat_objects设置为logging模式相比较,同样使用append提示产生的日志量差不多。
总结:在归档模式下,只有将表设置为nologging并且使用append提示才能大量减少日志的产生;在非
归档模式下,无论是否将表设置为nologging,只要使用append提示都会大量减少日志的产生。
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive_log
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
1.创建测试表hat_objects
SQL> create table hat_objects as select * from all_objects where 1<>1;
Table created
2.打开执行计划
SQL> set autotrace traceonly statistics;
3.插入数据
SQL> insert into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5877 recursive calls
5779 db block gets
80216 consistent gets
0 physical reads
4684152 redo size
675 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40685 rows processed
4.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
774 db block gets
79281 consistent gets
0 physical reads
4692132 redo size
660 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40685 rows processed
注:直接insert与使用append提示insert产生的日志大小相差无几,未减少日志的产生。
5.设置hat_objects为nologging模式
SQL> alter table hat_objects nologging;
Table altered.
6.插入数据
SQL> insert into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5232 recursive calls
5104 db block gets
80000 consistent gets
0 physical reads
4630652 redo size
676 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40685 rows processed
7.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
776 db block gets
79281 consistent gets
0 physical reads
19648 redo size
662 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40685 rows processed
注:当表hat_objects设置为nologging模式后,使用append提示后insert,日志的产生量明显比直接
insert少。
非归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/archive_log
Oldest online log sequence 29
Current log sequence 31
1.创建测试表
SQL> create table hat_objects as select * from all_objects where 1<>1;
Table created.
2.插入数据
SQL> insert into hat_objects select * from all_objects;
40685 rows created.
Statistics
----------------------------------------------------------
6773 recursive calls
6276 db block gets
80723 consistent gets
2 physical reads
4737072 redo size
680 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
40685 rows processed
3.使用提示插入数据
SQL> insert /*+ append */ into hat_objects select * from all_objects;
40684 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
762 db block gets
79281 consistent gets
0 physical reads
18332 redo size
664 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40684 rows processed
注:直接insert与使用append提示insert产生的日志大小相差很大,减少了日志的产生。
4.设置表hat_objects为nologging模式
SQL> alter table hat_objects nologging;
Table altered.
5.插入数据
SQL> insert into hat_objects select * from all_objects;
40684 rows created.
Statistics
----------------------------------------------------------
5454 recursive calls
5104 db block gets
80005 consistent gets
0 physical reads
4630688 redo size
680 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
40684 rows processed
6.使用提示插入数据
SQL> insert/*+ append */ into hat_objects select * from all_objects;
40684 rows created.
Statistics
----------------------------------------------------------
5278 recursive calls
782 db block gets
79281 consistent gets
0 physical reads
19648 redo size
664 bytes sent via SQL*Net to client
598 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40684 rows processed
注:当表hat_objects设置为nologging模式后,使用append提示后insert,日志量比直接insert少,
但与表hat_objects设置为logging模式相比较,同样使用append提示产生的日志量差不多。
总结:在归档模式下,只有将表设置为nologging并且使用append提示才能大量减少日志的产生;在非
归档模式下,无论是否将表设置为nologging,只要使用append提示都会大量减少日志的产生。
- 对表进行dml操作时影响产生日志量的几个因素
- 数据压缩对DML操作的影响
- 经常DML操作对索引的影响
- DML操作对索引的影响
- 有关Oracle表分区进行(DML)维护后对索引的影响的分析
- 有关Oracle表分区进行(DML)维护后对索引的影响的分析
- 有关Oracle表分区进行(DML)维护后对索引的影响的分析
- 浅谈网站收录量的影响因素
- sql server DML操作对索引的影响
- DML对undo的影响
- 对视图进行 DML 操作
- 操作数据库表的几个DML(Oracle)
- 影响 h00lyshit 成功的几个因素
- 影响WinCE启动速度的几个因素
- 影响ActiveMQ性能的几个重要因素
- 总结:影响玉米容量的几个因素
- 影响ActiveMQ性能的几个重要因素
- 影响HBase insert性能的几个因素
- 【Cocos2d-x游戏引擎开发笔记(3)】在屏幕上渲染菜单并添加消息响应
- 值类型与引用类型及在内存中的存储
- 最新在线客服漂浮代码下载
- 一个ant的build脚本。
- oracle简单存储过程(带参数)
- 对表进行dml操作时影响产生日志量的几个因素
- detours学习
- sql语句 update from
- c# 控件RadioButton
- visualvm远程监控(jstatd和jvm)
- 开始搞ANDROID了--Android源码下载和编译
- 自己做个记录
- eclipse 配置 TDM-GCC 64位版方法
- AnagramsbyStack_1004