10G中的异步提交

我们知道每当发出COMMIT命令的时候,LGWR进程将会被唤醒，将LOG BUFFER中的剩余的内容同步到REDO LOGFILE中。
此时前台进程将处于等待中，直到相关的REDO信息全部完全的写入到磁盘中，我们才会看到“提成成功”返回信息。
而COMMIT_WRITE可以改变这种行为，默认COMMIT_WRITE为IMMEDIATE,WAIT，表示每当COMMIT的时候，LGWR立马写磁盘，前台进程处于等待中。

IMMEDIATE/BATCH  LGWR是否立马写REDO信息到磁盘。
WAIT/NOWAIT      控制权是否立马返回给客户端。

 

联机文档对这个参数描述如下：

 

COMMIT_WRITE

Property	Description
Parameter type	String
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
Modifiable	Yes (at both session-level and system-level). Values supplied for COMMIT_WRITE in an ALTER SYSTEM or ALTER SESSION statement must be separated by a comma.
Range of values	Single-quoted, comma-separated list of either IMMEDIATE or BATCH, and either WAIT or NOWAIT.
Basic	No
Real Application Clusters	Each instance may have its own setting

 

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.

 

 

 

 

SQL> CREATE TABLE T(ID INT);

表已创建。

SQL> CREATE OR REPLACE PROCEDURE P_TEST(p_action VARCHAR2)
  2  AS
  3    l_begintime NUMBER;
  4    l_endtime   NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'TRUNCATE TABLE T';
  7    EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE='||P_ACTION;
  8    l_begintime:=DBMS_UTILITY.GET_TIME;
  9    FOR I IN 1..100000 LOOP
 10      INSERT INTO T VALUES(I);
 11      COMMIT;
 12    END LOOP;
 13    l_endtime:=DBMS_UTILITY.GET_TIME;
 14    DBMS_OUTPUT.PUT_LINE(P_ACTION||':'||TO_CHAR((l_endtime-l_begintime)/100))
;
 15  END;
 16  /

过程已创建。

首先建立了一个存储过程插入一条提交一次。

SQL> BEGIN
  2     P_TEST('IMMEDIATE,WAIT');
  3     P_TEST('IMMEDIATE,NOWAIT');
  4     P_TEST('BATCH,WAIT');
  5     P_TEST('BATCH,NOWAIT');
  6  END;
  7  /
IMMEDIATE,WAIT:387.89
IMMEDIATE,NOWAIT:12.67
BATCH,WAIT:385.51
BATCH,NOWAIT:11.53

PL/SQL 过程已成功完成。

测试发现设置NOWAIT比WAIT性能的提高很给力啊。对于频繁的提交的事务还是提高很多性能的。

将存储过程简单修改下：

将COMMIT放到FOR循环的外面。

SQL> CREATE OR REPLACE PROCEDURE P_TEST(p_action VARCHAR2)
  2  AS
  3    l_begintime NUMBER;
  4    l_endtime   NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'TRUNCATE TABLE T';
  7    EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE='||P_ACTION;
  8    l_begintime:=DBMS_UTILITY.GET_TIME;
  9    FOR I IN 1..100000 LOOP
 10      INSERT INTO T VALUES(I);
 11    END LOOP;
 12    COMMIT;
 13    l_endtime:=DBMS_UTILITY.GET_TIME;
 14    DBMS_OUTPUT.PUT_LINE(P_ACTION||':'||TO_CHAR((l_endtime-l_begintime)/100))
;
 15  END;
 16  /

过程已创建。

SQL> BEGIN
  2     P_TEST('IMMEDIATE,WAIT');
  3     P_TEST('IMMEDIATE,NOWAIT');
  4     P_TEST('BATCH,WAIT');
  5     P_TEST('BATCH,NOWAIT');
  6  END;
  7  /
IMMEDIATE,WAIT:3.84
IMMEDIATE,NOWAIT:4.47
BATCH,WAIT:3.97
BATCH,NOWAIT:4.77

PL/SQL 过程已成功完成。

对于不是频繁提交的事务，性能提升就没那么明显了。
对于频繁的提交的SQL，而又不好修改代码，可以通过改参数来暂时解决问题。