Re: Asynchronous Commit in Oracle Database 10g R2

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 03 Sep 2005 02:27:09 GMT
Message-Id: <pan.2005.09.03.02.27.05.405866@sbcglobal.net>

On Fri, 02 Sep 2005 16:14:13 +0000, Jonathan Lewis wrote:

>
> This is an undocumented optimization.
> You need only look at the sessions 'redo synch writes'.
>
> This counts the calls made to the log writer to
> flush the log buffer and wait. It's always one -
> which is the one call at the end of the database
> call that contains the series of commits.
>

Here it is:
I started the session with redo synch writes = 0. The next thing was to execute the following procedure:

declare
i integer;
begin
for i in 1..10000
loop
insert into a values(i);
commit;
end loop;
end;
/

Sure enough, redo synch writes was 1.

With the DB link pointing to the local instance (using IPC adapter) and inserting into a_at_local instead of just inserting into a, the number skyrocketed to 10001. It was done on Oracle 9.2. User commits was counted properly and was 20000 (two executions). Timing of the two executions was slightly different:
SQL> truncate table a reuse storage;

Table truncated.

Elapsed: 00:00:00.23
SQL> get /tmp/ins1
  1 declare
  2 i integer;
  3 begin
  4 for i in 1..10000
  5 loop
  6 insert into a values(i);
  7 commit;
  8 end loop;
  9* end;
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62
SQL> get /tmp/ins
  1 declare
  2 i integer;
  3 begin
  4 for i in 1..10000
  5 loop
  6 insert into a_at_local values(i);
  7 commit;
  8 end loop;
  9* end;
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:57.65
SQL> $ tnsping local

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 02-SEP-2005 22:20:09

Used parameter files:
/oracle/product/10g/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = 10g)) (CONNECT_DATA = (SERVICE_NAME = oracle) (SERVER = DEDICATED))) OK (60 msec)
$

My suspicion is that if I used TCP adapter instead of IPC, the time difference would be even more drastic. Interestingly enough, Oracle doesn't show time spent in waiting for commits. Here is the output of select from V$SESSION_EVENT

EVENT 	              TOTAL_WAITS TIME_WAITED 	AVERAGE_WAIT 	MAX_WAIT
SQL*Net message from client 	3 	1 	.23 	         0 
SQL*Net message to client 	4 	0        0 	         0

It looks like some important timings are not shown in the traditional tables we all might be inclined to look.

-- 
http://www.mgogala.com

Received on Fri Sep 02 2005 - 21:27:09 CDT