commit write的疑问

棉花糖ONE

我在那帖子里发的那篇都写了，你自己不看，wait ,nowait和batch针对的不是一个东西说的，这2个概念比较容易混起来

exitgogo

最初由 ilovemk 发布
[B]请问commit write batch nowait和commit write batch有什么区别啊？commit write batch nowait和commit write nowait又有什么区别呢？
[/B]

IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.

BATCH - The writes to the redo logs are buffered.

WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.

NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log

ilovemk

最初由 exitgogo 发布
[B]



IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.

BATCH - The writes to the redo logs are buffered.

WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.

NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log [/B]

ok，明白了。BTW，"redo logs are buffered"这里的bufferd是缓存到哪里呢？batch提交后重做日志缓冲区的内容可不可以被覆盖呢？批量提交的时间是由什么控制的呢，和lgwr的触发条件一样么？谢谢。

exitgogo

最初由 ilovemk 发布
[B]
ok，明白了。BTW，"redo logs are buffered"这里的bufferd是缓存到哪里呢？batch提交后重做日志缓冲区的内容可不可以被覆盖呢？批量提交的时间是由什么控制的呢，和lgwr的触发条件一样么？谢谢。 [/B]

batch：
Oracle buffers the redo information. The log writer will [/COLOR] write this redo information to disk, but in its own time. Thus, several I/Os can be "batched".

ilovemk

最初由 exitgogo 发布
[B]



batch：
Oracle buffers the redo information. The log writer will [/COLOR] write this redo information to disk, but in its own time. Thus, several I/Os can be "batched". [/B]

谢谢，不过似乎还没回答我的问题。

ilovemk

最初由 ilovemk 发布
[B]
ok，明白了。BTW，"redo logs are buffered"这里的bufferd是缓存到哪里呢？batch提交后重做日志缓冲区的内容可不可以被覆盖呢？批量提交的时间是由什么控制的呢，和lgwr的触发条件一样么？谢谢。 [/B]

up！

ilovemk

继续up

zengmuansha

估计你已经糊涂了......

请问commit write batch nowait和commit write batch有什么区别啊？
commit write batch nowait和commit write nowait又有什么区别呢？

好想不能这样搭配吧
commit write immediate wait      &&  commit write batch nowait
只有上面两种搭配

exitgogo

最初由 zengmuansha 发布
[B]估计你已经糊涂了......

请问commit write batch nowait和commit write batch有什么区别啊？
commit write batch nowait和commit write nowait又有什么区别呢？

好想不能这样搭配吧
commit write immediate wait      &&  commit write batch nowait
只有上面两种搭配 [/B]

可以有N种组合。


ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';

SQL>CREATE TABLE commit_test (
  id           NUMBER(10),
  description  VARCHAR2(50),
  CONSTRAINT commit_test_pk PRIMARY KEY (id)
);


SQL> DECLARE
  PROCEDURE do_loop (p_type  IN  VARCHAR2) AS
  2    3      l_start  NUMBER;
  4      l_loops  NUMBER := 1000;
  5    BEGIN
  6      EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || '''';
  7      EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';
  8  
  9      l_start := DBMS_UTILITY.get_time;
10      FOR i IN 1 .. l_loops LOOP
11        INSERT INTO commit_test (id, description)
12        VALUES (i, 'Description for ' || i);
      COMMIT;
13   14      END LOOP;
15      DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));
  END;
BEGIN
16   17   18    do_loop('WAIT');
19    do_loop('NOWAIT');
  do_loop('BATCH');
20   21    do_loop('IMMEDIATE');
  do_loop('BATCH,WAIT');
22   23    do_loop('BATCH,NOWAIT');
  do_loop('IMMEDIATE,WAIT');
24   25    do_loop('IMMEDIATE,NOWAIT');
END;
26   27  /
COMMIT_WRITE=WAIT             : 286
COMMIT_WRITE=NOWAIT           : 87
COMMIT_WRITE=BATCH            : 53
COMMIT_WRITE=IMMEDIATE        : 91
COMMIT_WRITE=BATCH,WAIT       : 268
COMMIT_WRITE=BATCH,NOWAIT     : 19
COMMIT_WRITE=IMMEDIATE,WAIT   : 284
COMMIT_WRITE=IMMEDIATE,NOWAIT : 36

PL/SQL procedure successfully completed.

zengmuansha

ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';

就三种有效