Testeando el commit asincrónico (COMMIT_WRITE)

En 10g R2 se introdujo el parámetro COMMIT_WRITE cuya finalidad es permitir el uso de commits asincronicos. Como es sabido cada vez que efectuamos un commit (o rollback) se persiste la informacion de la transacción en los redo logs en forma sincronica, esto significa que hasta que no se haga efectiva dicha escritura en disco, no se retorna el control a la aplicación o proceso que confirmó la transacción. Ahora bien, si pensamos en un commit asincronico estariamos violando la premisa de Durabilidad de una transacción, recordemos que en Oracle las transacciones son compatibles con el concepto ACID (Atomicity, Consistency, Isolation, Durability), y por lo tanto se podrian perder transacciones si se produjera un error severo de Hardware. Al no asegurar la durabilidad de las transacciones no podemos confiar que la base de datos maneje en forma totalmente confiable nuestras transaccciones lo cual resulta en sistemas inestables y propensos a errores e insonsistencias. Despues de todo lo que les comenté se preguntaran para que puede servir este nuevo parámetro, no?, bueno... yo diría que en ciertos casos muy particulares y acotados, donde se pueda tolerar perdida de información y donde se requiera muy alta transaccionabilidad, encontrariamos un beneficio en performance (por ejemplo se reducirian las esperas en el evento log file sync) ya que la ejecución de las sentencias COMMIT es instantanea lo cual genera mayor cantidad de ejecuciones.

Antes de ir a la prueba me gustaría aclarar que si se ven tentados a usar este parámetro para acelerar el procesamiento, primero chequeen el diseño de transaccionabilidad de su aplicación y vean si es posible una vuelta de tuerca desde el lado del código, ya sea viendo si es posible "commitear" menos frecuentemente o si se esta "commiteando" en forma redundante, antes de ir una solución (quick and dirty) como es tocar el parámetro COMMIT_WRITE.

Para probar este parámetro, armé un bloque PL/SQL anónimo donde voy a probar las cuatro conbinaciones posibles del COMMIT_WRITE y voy a almacenar los resultados del test en la tabla RES para luego realizar una consulta que me permita comparar en forma sencilla cada opción.

SQL> create table T (X NUMBER(38),
2                    Y VARCHAR2(10),
3                    Z DATE)
/
Table created.

SQL>

SQL> create table RES (NAME     VARCHAR2(200) not null,
2                      VALUE    NUMBER,
3                      NCASE    NUMBER(1) not null)
/
Table created.

rop@BPP3> begin
2   execute immediate 'truncate table res';
3
4   for i in 1..4 loop
5
6    case i when 1 then
7        execute immediate 'alter session set commit_write=''immediate,wait''';
8            when 2 then
9        execute immediate 'alter session set commit_write=''immediate,nowait''';
10           when 3 then
11        execute immediate 'alter session set commit_write=''batch,wait''';
12             when 4 then
13          execute immediate 'alter session set commit_write=''batch,nowait''';
14   end case;
15
16   insert into res
17   select 'STAT...' || a.name name,
18          b.value,
19          case i when 1 then 1
20                 when 2 then 2
21                 when 3 then 3
22                 when 4 then 4
23          end
24   from v$statname a, v$mystat b
25   where a.statistic# = b.statistic#
26   union all
27   select 'LATCH.' || name,
28          gets,
29          case i when 1 then 1
30                 when 2 then 2
31                 when 3 then 3
32                 when 4 then 4
33          end
34   from v$latch
35   union all
36   select 'STAT...Elapsed Time',
37          hsecs,
38          case i when 1 then 1
39                 when 2 then 2
40                 when 3 then 3
41                 when 4 then 4
42          end
43   from v$timer;
44
45   for j in (select trunc(dbms_random.value(1,10000)) x,
46                    dbms_random.string('a',10) y,
47                    sysdate+dbms_random.value(-300,300) z
48             from dual
49              connect by rownum <= 100000)  
50   loop  
51      insert into t values (j.x,j.y,j.z);  
52      commit; 
53   end loop; 
54
55   for k in (select 'STAT...' || a.name name, b.value  
56             from v$statname a, 
57                  v$mystat b  
58             where a.statistic# = b.statistic#  
59             union all  
60             select 'LATCH.' || name,  gets 
61             from v$latch  
62             union all  
63             select 'STAT...Elapsed Time',hsecs  
64             from v$timer)  
65   loop
66       update res 
67       set value = (k.value-value)
68       where res.name = k.name 
69         and res.ncase = i;  
70    end loop; 
71    commit; 
72  end loop;  
73 end; 
/  
PL/SQL procedure successfully completed.    

Ahora voy a ejecutar una consulta sobre la tabla de resultados de forma tal de encolumnar cada caso y poder comparar mas facilmente.

rop@BPP3> select name,
            max(decode(ncase,1,value,null)) "IMMEDIATE,WAIT",
            max(decode(ncase,2,value,null)) "IMMEDIATE,NOWAIT",
            max(decode(ncase,3,value,null)) "BATCH,WAIT",
            max(decode(ncase,4,value,null)) "BATCH,NOWAIT"
     from res
     group by name

NAME                                                         IMMEDIATE,WAIT IMMEDIATE,NOWAIT BATCH,WAIT BATCH,NOWAIT

------------------------------------------------------------ -------------- ---------------- ---------- ------------

STAT...recursive cpu usage                                             1393              887       1293          849

STAT...messages sent                                                 100040            10449     100008           53

STAT...commit wait requested                                         100000                0     100000            0

STAT...SMON posted for undo segment shrink                                0                0          0            0

STAT...commit batch performed                                             0                0     100000       100000

STAT...commit immediate performed                                    100000           100000          0            0

STAT...commit nowait performed                                            0           100000          0       100000

LATCH.session timer                                                      19                3         34            3

STAT...redo synch time                                                 4042                0       8777            0

STAT...commit batch requested                                             0                0     100000       100000

STAT...commit wait/nowait requested                                  100000           100000     100000       100000

STAT...commit wait performed                                         100000                0     100000            0

LATCH.messages                                                       287070            23614     462948          447

STAT...Elapsed Time                                                    5543              965      10056          927

STAT...redo synch writes                                             100000                0     100000            0

STAT...commit batch/immediate requested                              100000           100000     100000       100000

STAT...commit immediate requested                                    100000           100000          0            0

LATCH.Consistent RBA                                                 100035            10447     100015           54

LATCH.cache buffers lru chain                                           652              600       4870         3282

LATCH.object queue header heap                                           93               16     216728          344

LATCH.redo allocation                                                587541           414896     504629       304247

LATCH.JS queue state obj latch                                          396               72        720           72

STAT...CPU used by this session                                        1493              956       1388          915

STAT...session pga memory max                                       4587520            65536          0            0

STAT...commit nowait requested                                            0           100000          0       100000

LATCH.client/application info                                             4                0          8            0

LATCH.OS process allocation                                              19                3         34            3

LATCH.active checkpoint queue latch                                      24                5      54186           89

LATCH.archive process latch                                              26                9         46            9

LATCH.post/wait queue                                                150161                5     150132            3

LATCH.parameter table allocation management                               2                0          4            0

LATCH.redo writing                                                   300168            31357     354299          258

El resultado de la consulta me arrojó mas de 700 estadisticas y latches. Solo copié
las filas donde noté diferencias importantes en los casos. En especial, la fila resaltada (Elapsed Time) muestra el tiempo total de procesamiento en cada caso en centisegundos. Como se observa para los casos donde se usa WAIT los tiempos son bastante mayores, lo cual comprueba la mejora en performance al usar asincronismo (NOWAIT).

Mientras escribia esta nota descubrí que en 11g el parametro commit_write esta "deprecated", parece que tuvo poca vida, no?. De todas formas la documentación aclara que fue reemplazado por los parametros COMMIT_WAIT y COMMIT_LOGGING.
Usando estos dos nuevos parametros en conjunto se obtendría el mismo funcionamiento que COMMIT_WRITE, aunque que el parametro COMMIT_WAIT agregó una nueva opción para forzar el wait (force_wait)