viernes, 25 de julio de 2008

Selects y commits a través de DB Links

Me he encontrado en muchas ocasiones problemas de desempeño por un mal diseño de la programación, generalmente por el no usar bind variables, algunas por usar bind variables cuando se podría usar una literal en histogramas. Pero generalmente los problemas más comunes son los excesos de commits.

El "Commit" es un serializador en oracle, es algo que por un pequeño instante frena todo lo que está sucediendo para lograr un:

"Log File Sync"


Una vez que se realiza el log file sync, oracle a través de redo logs nos permite recuperar una base de datos en caso de un crash en el momento hasta el punto en donde dimos commit.

Pero hay desarrolladores que abusan del uso del commit, no sé si sea que vienen desarrollando para otros manejadores de bases de datos que requieren commits a cada rato para poder permitir lecturas a más usuarios sobre bloques de datos o simplemente porque desconocen cómo ejecutar código de forma óptima dentro de oracle.

Hay un punto que no siempre es considerado por los desarrolladores, los "SELECTS" a través de db links son transacciones y que el estar dando commits muy frecuentes, puede ocasionar un problema de desempeño. Para ejemplificarlo, haremos a prueba enb un ciclo que tiene un commit dentro del ciclo, y otro ejemplo con commit fuera del ciclo:


SQL> CREATE DATABASE link LOOPBACK
2 CONNECT TO system
3 identified by alanis11
4 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hugo-win)
5 (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

Enlace con la base de datos creado.

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10046 trace name context forever, level 8;
Sentencia procesada.

SQL> set timing on
SQL> declare
2 numero number:=0;
3 begin
4 for registro in (select object_id from dba_objects)
5 loop
6 select /*+ Sin Commit*/
7 count(1) into numero
8 from dba_objects@loopback
9 where object_id = registro.object_id;
10 end loop;
11 commit;
12 end;
13 /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:19.84

SQL> declare
2 numero number:=0;
3 begin
4 for registro in (select object_id from dba_objects)
5 loop
6 select /*+ Con Commit*/
7 count(1) into numero
8 from dba_objects@loopback
9 where object_id = registro.object_id;
10 commit;
11 end loop;
12 end;
13 /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:02:53.26



Como se puede ver, sólo estamos haciendo un select, no existe update,delete o insert, sin embargo, al ser a través de un dblink, entra como transacción y al tener los commits, se pierde mucho tiempo en esperas.

Al revisar las esperas encontramos lo siguiente. Para el commit fuera del ciclo:

SELECT /*+ Sin Commit*/ COUNT(1)
FROM
DBA_OBJECTS@LOOPBACK WHERE OBJECT_ID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 50660 5.73 8.83 0 0 0 0
Fetch 50660 5.75 9.95 0 0 0 50660
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101321 11.48 18.80 0 0 0 50660

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 101322 0.00 0.24
SQL*Net message from dblink 101322 0.00 14.17


Y para el caso del commit dentro del ciclo:


SELECT /*+ Con Commit*/ COUNT(1)
FROM
DBA_OBJECTS@LOOPBACK WHERE OBJECT_ID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50660 33.84 142.56 50630 14211 182788 0
Fetch 50660 7.18 15.94 0 0 0 50660
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101321 41.03 158.50 50630 14211 182788 50660

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 151981 0.00 0.46
SQL*Net message from dblink 151981 0.00 28.84
db file sequential read 50754 0.26 59.72
latch: cache buffers lru chain 1 0.00 0.00
rdbms ipc reply 621 0.21 5.01
control file sequential read 2294 0.13 6.28
Data file init write 684 0.26 11.90
flashback buf free by RVWR 63 0.03 0.06
control file parallel write 434 0.06 0.77
db file single write 62 0.16 0.21
latch: object queue header operation 1 0.00 0.00
enq: CF - contention 1 0.01 0.01



El manual de conceptos dice lo siguiente:

"Cuando un objeto es referenciado, o el uso de una función remota es ejecutada, los database links establecen una conexión a una sesión en la base de datos remota a nombre de la sesión local. La conexión remota y la sesión son creadas únicamente si no han sido previamente creadas para el usuario que la solicitó.

Las conexiones y sesiones establecidas, persisten durante la existencia de la sesión local, a menos que la aplicación, o el usuario force de manera manual la terminación de la mima.

Hay que notar, que cuando se ejecuta en select a través de un database link, un bloqueo transaccional es requerido en los segmentos de undo. Para liberar los segmentos de undo de estos bloqueos, es necesario dar un commit o rollback."

Y efectivamente las esperas por "db file sequential read" son todas por algún segmento de undo:


WAIT #4: nam='db file sequential read' ela= 222 file#=2 block#=54448 blocks=1 obj#=0 tim=88041128812

SQL> SELECT /*+RULE*/
2 segment_name, segment_type
3 FROM dba_extents
4 WHERE file_id = 2
5 AND 54448 BETWEEN block_id
6 AND block_id + blocks -1;

SEGMENT_NAME SEGMENT_TYPE
----------------- ------------------
_SYSSMU2$ TYPE2 UNDO



Cuando por primera vez me topé con algo similar con un cliente, la optimización de su proceso consistió en eliminar los commits innecesarios, y dejar sólo un commit al final de su procedimiento, de esta forma se respetó la atomicidad del procedimiento, y se logró tener un desempeño óptimo.

5 comentarios:

rachisa dijo...

Interesante, en mi anterior empleo me tocaba ver mucho estos eventos de espera a traves de DBlinks en las cargas de DWH, creo que me hubiera servido hace algunos meses.

Saludos

Hugo E. Contreras Gamiño dijo...

Lo que habrá que revisar es sí los Waits "db file sequential read" en tus cargas eran relacionadas a el uso de un índice, o eran por lecturas en los segmentos de Undo.

Facundo dijo...

Hola Hugo, antes que nada quería felicitarlo por el muy buen blog que tiene.

Ahora tengo una consulta que pienso me podrá ayudar.

Tengo un simple query que realiza un select en otra instancia a través de un dblink, pero cuando lo ejecuto, desde la instancia en donde lo corro se queda en el evento: "sql*net message from dblink" mientras que en la instancia a la cual apunta el dblink y de donde necesita sacar la información se queda en el evento: "sql*net more data to client".

Si ver el query le serviría para entender mejor el contexto no dude en pedírmelo.

Muchas gracias.

Facundo dijo...

Hola Hugo, antes que nada quería felicitarlo por el muy buen blog que tiene.

Ahora tengo una consulta que pienso me podrá ayudar.

Tengo un simple query que realiza un select en otra instancia a través de un dblink, pero cuando lo ejecuto, desde la instancia en donde lo corro se queda en el evento: "sql*net message from dblink" mientras que en la instancia a la cual apunta el dblink y de donde necesita sacar la información se queda en el evento: "sql*net more data to client".

Si ver el query le serviría para entender mejor el contexto no dude en pedírmelo.

Muchas gracias.

Anónimo dijo...

Hola, muy interesante el articulo, muchos saludos desde Argentina!