ANBOB™

in 10gR2，COMMIT_WRITE is an advanced parameter used to control how redo for transaction commits is written to the redo logs. The IMMEDIATE and BATCH options control how redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo for a commit is flushed to the redo logs.

Syntax COMMIT_WRITE = ‘{IMMEDIATE | BATCH},{WAIT |NOWAIT}’

Default value If this parameter is not explicitly specified, then database commit behavior defaults to writing commit records to disk before control is returned to the client.
If only IMMEDIATE or BATCH is specified, but not WAIT or NOWAIT, then WAIT mode is assumed.

If only WAIT or NOWAIT is specified, but not IMMEDIATE or BATCH, then IMMEDIATE mode is assumed

IMMEDIATE The redo information is written to disk immediately (forcing a disk I/O for each commit).
BATCH Oracle buffers the redo information. The log writer will write this redo information to disk, but in its own time. Thus, several I/Os can be “batched”.
WAIT Oracle does not return from the commit until the commit has completed successfully.
NOWAIT Oracle doesn’t wait for the commit to complete, but returns right away.

This can improve performance, but it should only be used for processes that meet the following criteria.

》They result in large numbers of transactions that require redo log writes.
》Data loss can be tolerated in the event of an instance crash during the process.
》Waiting for redo log writes is a significant part of the waits associated with the process.

SQL> show user
USER is "ANBOB"
SQL> create table testcommit(id int,name varchar2(300));

Table created.



SQL> set serveroutput on
SQL> declare
 procedure loop_insert(p_comty varchar2)
 is
 v_sta number;
 v_times number := 5000;
 begin
 execute immediate 'alter session set commit_write='''||p_comty||'''';
 execute immediate 'truncate table testcommit';
 v_sta := dbms_utility.get_time;
 for i in 1..v_times loop
   insert into testcommit values(i,'current value is '||i);
   commit;
 end loop;
 dbms_output.put_line(rpad('commit_write='||p_comty,30)||':'||(dbms_utility.get_time-v_sta));
 end;
 begin
   loop_insert('WAIT');
   loop_insert('NOWAIT');
   loop_insert('BATCH');
   loop_insert('IMMEDIATE');
   loop_insert('BATCH,WAIT');
   loop_insert('BATCH,NOWAIT');
   loop_insert('IMMEDIATE,WAIT');
   loop_insert('IMMEDIATE,NOWAIT');
 end;
 
-----------------------------------------

commit_write=WAIT             :1677
commit_write=NOWAIT           :425
commit_write=BATCH            :412
commit_write=IMMEDIATE        :1322
commit_write=BATCH,WAIT       :1756
commit_write=BATCH,NOWAIT     :403
commit_write=IMMEDIATE,WAIT   :1110
commit_write=IMMEDIATE,NOWAIT :398

PL/SQL procedure successfully completed.

--如果把提交放到loop 外面

declare
 procedure loop_insert(p_comty varchar2)
 is
 v_sta number;
 v_times number := 5000;
 begin
 execute immediate 'alter session set commit_write='''||p_comty||'''';
 execute immediate 'truncate table testcommit';
 v_sta := dbms_utility.get_time;
 for i in 1..v_times loop
   insert into testcommit values(i,'current value is '||i);
   
 end loop;
 commit;
 dbms_output.put_line(rpad('commit_write='||p_comty,30)||':'||(dbms_utility.get_time-v_sta));
 end;
 begin
   loop_insert('WAIT');
   loop_insert('NOWAIT');
   loop_insert('BATCH');
   loop_insert('IMMEDIATE');
   loop_insert('BATCH,WAIT');
   loop_insert('BATCH,NOWAIT');
   loop_insert('IMMEDIATE,WAIT');
   loop_insert('IMMEDIATE,NOWAIT');
 end;
 
 ---------------------------------------------
commit_write=WAIT             :213
commit_write=NOWAIT           :170
commit_write=BATCH            :139
commit_write=IMMEDIATE        :132
commit_write=BATCH,WAIT       :151
commit_write=BATCH,NOWAIT     :190
commit_write=IMMEDIATE,WAIT   :216
commit_write=IMMEDIATE,NOWAIT :182

PL/SQL procedure successfully completed.

打赏

微信扫一扫，打赏作者吧～