如果不考虑事务的完整性，是不是在SQL中的COMMIT越多性能越好？

杨奇龙

原帖由 viadeazhu 于 2010-8-26 23:00 发表
现在都是incremental checkpoint，引入的checkpoint queue的概念，所以commit太频繁不会对checkpoint有太大影响，其实是对redo buffer到redo log太过频繁，redo写更不上。

如果commit太少，设想一个很大的transaction，把所有redo都用完了该如何？数据库hung了。

对于归档模式 的数据库，redo 是循环利用的，
当然不排除lgwr的写速度小于产生redo 的速度 导致出现等待事件，一直等到redo 完全归档，数据库hang住！

关于commit 少时，我还是比较赞同 sundong 兄的观点，commit少了，会耗费大量的undo，如果是auto extent 的undo表空间，会致使undo 特别大，网上的这种案例挺多的！

carcase

to 小V
----------------------------------------
现在都是incremental checkpoint，引入的checkpoint queue的概念，所以commit太频繁不会对checkpoint有太大影响，其实是对redo buffer到redo log太过频繁，redo写更不上。
--------------------------------------
提交(COMMIT)  （好像是eygle的书上看来的，^_^）
1.Oracle产生一个SCN
2.在回滚段事务表中标记该事务状态为commited
3.LGWR Flush Log Buffer到日志文件
4.如果此时数据块仍然在Buffer Cache中，那么SCN将被记录到Block Header上，这被称为快速提交(fast commit)
如果dirty block已经被写回到磁盘，那么下一个访问这个block的进程将会自回滚段中获取该事务的状态，确认该事务被提交。然后这个进程获得提交SCN并写回到Block Header上。这被称为延迟块清除(delayed block cleanout)。

commit 过多
1.频繁生成scn --会不会增加redo的量？？  应该会，看下面的实验
2.频繁修改回滚段事务表中的标记，但是回滚段也被释放出来了
3.频繁触发lgwr,而lgwr写有一个叫组提交的，就是说唤醒lgwr写之前，之前的所有redo都被写入磁盘，这一步增加了写磁盘的量，但是一起写速度是不是也会有所增加呢？？
另外一个问题，redo 是什么时候从pga中拷贝到log buffer中的？？ 是commit后，还是有其他触发条件？？
如果是commit后，那么频繁触发lgwr，就会造成 频繁申请redo copy latch,redo allocation latch,造成log file sync等待
4.频繁更新block header，这个会有消耗吗？？

下面是 关于 1的 实验

TEST@ sun>select * from t;

         A          B
---------- ----------
         9          1
         9          2

TEST@ sun>create table t2 as select * from t;

表已创建。

TEST@ sun>select * from t2;

         A          B
---------- ----------
         9          1
         9          2

TEST@ sun>select a.sid,b.name,a.VALUE from v$sesstat a,v$statname b,(select distinct sid from v$myst
at) c
  2  where a.statistic#=b.statistic# and a.sid=c.sid and b.name='redo size';

       SID NAME                                VALUE
---------- ------------------------------ ----------
       142 redo size                           20108

TEST@ sun>update t set a=5;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=6;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=7;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=8;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=9;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=10;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=11;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=12;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=13;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>update t set a=14;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>
TEST@ sun>select a.sid,b.name,a.VALUE from v$sesstat a,v$statname b,(select distinct sid from v$myst
at) c
  2  where a.statistic#=b.statistic# and a.sid=c.sid and b.name='redo size';

       SID NAME                                VALUE
---------- ------------------------------ ----------
       142 redo size                           29108

TEST@ sun>update t2 set a=5;

已更新2行。

TEST@ sun>update t2 set a=6;

已更新2行。

TEST@ sun>update t2 set a=7;

已更新2行。

TEST@ sun>update t2 set a=8;

已更新2行。

TEST@ sun>update t2 set a=9;

已更新2行。

TEST@ sun>update t2 set a=10;

已更新2行。

TEST@ sun>update t2 set a=11;

已更新2行。

TEST@ sun>update t2 set a=12;

已更新2行。

TEST@ sun>update t2 set a=13;

已更新2行。

TEST@ sun>update t2 set a=14;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>
TEST@ sun>select a.sid,b.name,a.VALUE from v$sesstat a,v$statname b,(select distinct sid from v$myst
at) c
  2  where a.statistic#=b.statistic# and a.sid=c.sid and b.name='redo size';

       SID NAME                                VALUE
---------- ------------------------------ ----------
       142 redo size                           34768

TEST@ sun>select (29108-20108) 分开commit的redo量 ,(34768-29108) 一起commit的redo量 from dual;

分开COMMIT的REDO量 一起COMMIT的REDO量
------------------ ------------------
              9000               5660
              
多commit生成的commit 大于一起commit的量

newdelete

原帖由 viadeazhu 于 2010-8-27 09:34 发表
设想一个场景，你的redo log是100MB一个，一共有三个，那就是300MB。

如果此时你做了一个大事务的update语句，其间会产生400MB的redo，那么当所有的redo log都用光的时候，请问你认为此时数据库没hung么？

当然不会因为这个hang，但可能会产生一小段时间的等待：一个checkpoint而已。

viadeazhu

呵呵，你可以保留你的意见。

logfile的大小和transaction产生的redo大小是一个相对的概念。
例如即使logfile是1G一个，但我一个transaction产生的redo是10G，那么logfile就相对小了。
此时，就很有可能产生的脏数据写回不够快的情况。

所以我刚才给你做了个实验，redo log缩小为10M，减少为2个，为的更快模拟出这个问题：
在一个idle的环境里run了一个很大的transaction，结果脏数据写回的速度<<产生脏数据的速度，于是两个logfile都写满了：

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------------
         5          1        678   10485760          1 YES ACTIVE                42881023 27-AUG-10
         6          1        679   10485760          1 NO  CURRENT               42881037 27-AUG-10


以后所有的dml语句都在等待log file switch (checkpoint incomplete)。
在一个高并发的环境下，session开始堆积到一定的程度，于是数据库就可以轻松地hung。

我上面有人

看来体系架构还有很多误区啊！

我上面有人

你认为commit能解决你说的问题吗？

viadeazhu

不会。在此情况下还是跟不上。

viadeazhu

在dbwr写脏数据跟不上时，可能需要在多个部分数据commit后sleep一段时间。

杨奇龙

原帖由 ZALBB 于 2010-8-26 22:52 发表


COMMIT 触发产生检查点?

抱歉，我理解错了!
找了一些关于触发检查点的资料
二、触发的条件
这里需要明白两个概念“完全检查点和增量检查点”的区别。
增量检查点（incremental checkpoint）
oracle8以后推出了incremental checkpoint的机制，在以前的版本里每checkpoint时都会做一个full thread checkpoint,这样的话所有脏数
据会被写到磁盘，巨大的i/o对系统性能带来很大影响。为了解决这个问题，oracle引入了checkpoint queue机制，每一个脏块会被移到检查点
队列里面去，按照low rdb（第一次对此块修改对应的redo block address）来排列，靠近检查点队列尾端的数据块的low rba值是最小的，而
且如果这些赃块被再次修改后它在检查点队列里的顺序也不会改变，这样就保证了越早修改的块越早写入磁盘。每隔3秒钟ckpt会去更新控制文
件和数据文件，记录checkpoint执行的情况。
在运行的Oracle 数据中，有很多事件、条件或者参数来触发检查点。比如
1 当已通过正常事务处理或者立即选项关闭例程时；（shutdown immediate或者Shutdown normal;）
2  当通过设置初始化参数LOG_CHECKPOINT_INTERVAL、LOG_CHECKPOINT_TIMEOUT 和FAST_START_IO_TARGET强制时；
3 当数据库管理员手动请求时；（ALter system checkpoint）
4 alter tablespace … offline;
5 每次日志切换时;（alter system switch logfile）
6 alter system switch logfile也将触发完全检查点的发生。
7 alter database datafile … offline不会触发检查点进程。
如果是单纯的offline datafile，那么将不会触发文件检查点，只有针对offline tablespace的时候才会触发文件检查点，这也是为什么
online datafile需要media recovery而online tablespace不需要。
对于表空间的offline后再online这种情况，最好做个强制的checkpoint比较好。
上面几种情况，将触发完全检查点，促使DBWR 将检查点时刻前所有的脏数据写入数据文件。
另外，一般正常运行期间的数据库不会产生完全检查点，下面很多事件将导致增量检查点，比如：
在联机热备份数据文件前，要求该数据文件中被修改的块从DB_Buffer 写入数据文件中。所以，发出这样的命令：
1  ALTER TABLESPACE tablespace_name BIGEN BACKUP & end backup; 也将触发和该表空间的数据文件有关的局部检查点；另外，
2  ALTER TABLESPACE tablespace_name READ ONLY;
3  ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
等命令都会触发增量检查点。
                                                             from http://www.oracleonlinux.cn/2009/12/16/oracle-background-processes/

杨奇龙

原帖由 carcase 于 2010-8-27 09:54 发表
to 小V
----------------------------------------
现在都是incremental checkpoint，引入的checkpoint queue的概念，所以commit太频繁不会对checkpoint有太大影响，其实是对redo buffer到redo log太过频繁，redo写更不上。
--------------------------------------
提交(COMMIT)  （好像是eygle的书上看来的，^_^）
1.Oracle产生一个SCN
2.在回滚段事务表中标记该事务状态为commited
3.LGWR Flush Log Buffer到日志文件
4.如果此时数据块仍然在Buffer Cache中，那么SCN将被记录到Block Header上，这被称为快速提交(fast commit)
如果dirty block已经被写回到磁盘，那么下一个访问这个block的进程将会自回滚段中获取该事务的状态，确认该事务被提交。然后这个进程获得提交SCN并写回到Block Header上。这被称为延迟块清除(delayed block cleanout)。

commit 过多
1.频繁生成scn --会不会增加redo的量？？  应该会，看下面的实验
2.频繁修改回滚段事务表中的标记，但是回滚段也被释放出来了
3.频繁触发lgwr,而lgwr写有一个叫组提交的，就是说唤醒lgwr写之前，之前的所有redo都被写入磁盘，这一步增加了写磁盘的量，但是一起写速度是不是也会有所增加呢？？
另外一个问题，redo 是什么时候从pga中拷贝到log buffer中的？？ 是commit后，还是有其他触发条件？？
如果是commit后，那么频繁触发lgwr，就会造成 频繁申请redo copy latch,redo allocation latch,造成log file sync等待
4.频繁更新block header，这个会有消耗吗？？

下面是 关于 1的 实验

。。。。。。。。。。。。
TEST@ sun>update t2 set a=5;

已更新2行。

TEST@ sun>update t2 set a=6;

已更新2行。

TEST@ sun>update t2 set a=7;

已更新2行。

TEST@ sun>update t2 set a=8;

已更新2行。

TEST@ sun>update t2 set a=9;

已更新2行。

TEST@ sun>update t2 set a=10;

已更新2行。

TEST@ sun>update t2 set a=11;

已更新2行。

TEST@ sun>update t2 set a=12;

已更新2行。

TEST@ sun>update t2 set a=13;

已更新2行。

TEST@ sun>update t2 set a=14;

已更新2行。

TEST@ sun>commit;

提交完成。

TEST@ sun>
TEST@ sun>select a.sid,b.name,a.VALUE from v$sesstat a,v$statname b,(select distinct sid from v$myst
at) c
  2  where a.statistic#=b.statistic# and a.sid=c.sid and b.name='redo size';

       SID NAME                                VALUE
---------- ------------------------------ ----------
       142 redo size                           34768

TEST@ sun>select (29108-20108) 分开commit的redo量 ,(34768-29108) 一起commit的redo量 from dual;

分开COMMIT的REDO量 一起COMMIT的REDO量
------------------ ------------------
              9000               5660
              
多commit生成的commit 大于一起commit的量

多commit生成的commit 大于一起commit的量 这些多出来的redo开销是什么？