Ejecutar store procedure en paralelo

[rogly]

Hola Expertos!!

Tengo un Store procedure que tiene sentencias while, el store procedure lee millones de registros e inserta.

Este store procedure es demasiado lento y demora días en terminar de ejecutar; cree índices y ya he revisado mil veces el código.

Ahora he optado por particionar los registros que lee el store procedure, tengo 10 particiones y cada particion la identifico con un numero (una especie de flag).

Este flag lo envio como parametro cuando ejecuto el estore procedure.


Lo que quiero realizar es la llamada del mismo store procedure 10 veces al mismo tiempo, debido que dentro del procedure utiliza algunas tablas, estas tablas las cree temporales para que lea solo los registros de su particion.

Con las siguientes sentencias qué realiza? ejecuta el bloque 1, luego el 2 o los ejecuta todos al mismo tiempo???

begin
sp_prp_15_identifica (0);
sp_prp_15_identifica (1);
sp_prp_15_identifica (2);
sp_prp_15_identifica (3);
sp_prp_15_identifica (4);
sp_prp_15_identifica (5);
sp_prp_15_identifica (6);
sp_prp_15_identifica (7);
sp_prp_15_identifica (8);
sp_prp_15_identifica (9);
end;



Esto en mi misma sesion, lei que tiene que ser en sesiones diferentes cuando se utilizan tablas temporales, debo abrir varias sesiones PL SQL con mi mismo usario y en cada una una linea??? es decir debo tener en una sesion para el flag 1 en la otra sesion para el flag 2.... debería tener 10 sesiones???


Lo que necesito es la forma de cómo ejecutar un mismo procedure 10 veces y cada bloque vaya de forma independiente.

Mucho agradeceré su ayuda.

Gracias

[huesos52]

lei que tiene que ser en sesiones diferentes cuando se utilizan tablas temporales, debo abrir varias sesiones PL SQL con mi mismo usario y en cada una una linea???

Exacto.
Debes abrir varias sesiones y ejecutar cada una en una sesión diferente. Esto funcionará siempre y cuando cada procedimiento no intervenga en los mismos registros ya que se podrían presentar una enorme cantidad de bloqueos de registros.

saludos

[rogly]

Graciassssss!!!! voy aplicar lo que me dices... te comentaré luego cómo me fue.

Muchas gracias por tu tiempo.

[matanga]

No conozco los detalles del procedimiento, pero diría que te estás complicando un poco, el paralelismo que buscas ya lo provee Oracle con Parallel Query en los SELECT, imagina que tienes una tabla muy grande y tienes que recorrer todos los registros, es más eficiente una única sesión haciendo un FULL SCAN a través de múltiples procesos parallel a tener 10 sesiones diferentes ejecutando 10 consultas con un filtro que puede o no usar un índice.

Después, si el proceso modifica datos y no diseñas correctamente las transacciones, puedes terminar en un error por deadlock, algo típico en procesos paralelos que se ejecutan en sesiones diferentes y modifican las mismas tablas. También debes considerar el diseño de los bloqueos, si un proceso tiene un lock sobre una tabla por modificación y tarda en liberar el recurso, el proceso que venga detrás estará en espera hasta que finalice el primero, lo que evita cualquier intento de paralelismo además del costo adicional para el motor en gestionar la espera, esto lo puedes ver en la estadística lockwait.

Por último, las tablas temporales tienen bajo rendimiento si las comparas con las variables de tipo tabla (o colecciones), las dos tienen la posibilidad de almacenar filas, pero las tablas temporales van a disco y están sujetas a transacciones, mientras que las colecciones, al ser tratadas como cualquier otra variable, resuelven todo en memoria.

En general, una buena estrategia es un único proceso que lea muchas filas con parallel, suba rápido las filas a memoria, modifique las filas en variables y finalmente haga los insert en serie.

No se si resuelve tu caso, pero te dejo un código con bulk collect para que veas un ejemplo de como implementar esto.

Código:

create table t1 (id number(8), datos varchar2(30))
/
declare

 cursor c1 is 
  select 1 as id, to_char(1) as datos from all_objects;

 /* tipo de dato tabla con el formato del cursor*/
 type type_t1 is table of c1%rowtype index by pls_integer;
 t type_t1;

begin

 open c1;
 loop

  /* limite de registros por cada bulk collect, esto es para no consumir toda la PGA*/
  fetch c1 bulk collect into t limit 100;
  exit when t.count = 0;

  /* loop con los datos que tengas que procesar*/
  for i in t.first..t.last loop
    insert into t1 (id, datos) values (t(i).id, t(i).datos);
  end loop;
  
 end loop;

end;
/

Saludos

[rogly]

Hola Matanga,

El caso es el siguiente:

Tabla 1: distinct de campos de la tabla 2
Tabla 2: Tabla con transacciones donde se repiten varios registros con distintas transacciones a cualquier hora, un usuario que realiza varias llamadas en el dia por ejemplo.
Tabla 3: Saldos de dónde consume el cliente.
Tabla 4: Resultado de consumos identificando de dónde consume.

La tabla 1 tiene todos los usuarios que han realizado transacciones en la tabla 2 esto para no volver a leer un registro leido anteriormente. (Es un distinct de tabla 2, en la tabla 1 son registros únicos)

Proceso:

Tabla 1 entra a un while donde selecciona uno a uno los registros, registro 1 busca todas las transacciones en la tabla 2, almacena estos registros en una tabla temporal, realiza algunos cálculos dependiendo de los saldos de la TABLA 3, luego inserta en una nueva tabla TABLA 4 los resultados.

Despues regresa a buscar el registro 2 de la tabla 1 realiza lo mismo y regresa hasta terminar todos los registros de la tabla1.

Por eso el proceso es muy lento, y es la única forma de identificación, el store procedure funciona pero cuando son todos los registros se vuelve muy lento el tiempo de respuesta.


La tabla a la que "particioné" (Flag) es la tabla 1 y tiene la siguiente estructura:

Campo1,campo2,campo3,partition
aaa,bbbb,cccc,0
ddd,eee,ffffffff,0
ggg,hhh,iiiii,1
jjjj,kkkk,lllllll,1
.
.
xxx,yyy,zzz, 9


Envié como parámetro el campo partition para que busque uno a uno sólo los de su partición y ejecutar 10 veces el mismo procedure.

Leyendo lo que me aconsejas, encontré la siguiente sintaxis:

ALTER TABLE table_name PARALLEL (DEGREE 8);

en mi caso aplicaría el PARALLEL a la tabla 1


ALTER TABLE tabla1 PARALLEL (DEGREE 9);

dime DEGREE son las particiones de la tabla ???

De esta forma la tabla 1 estaría particionada?

en tu ejemplo:

fetch c1 bulk collect into t limit 100;
exit when t.count = 0;

aquí sólo lee de 100 en 100, cómo envío el parámetro al estore procedure aplicando PARALLEL o ya no es necesario ya que con la sentencia ya está particionado en 9 y leerá en grupos de 100??


Gracias por tu ayuda

[matanga]

Ayuda si posteas la estructura de las 4 tablas, a simple vista parece ser posible crear una única consulta relacionando (join) las tablas 1, 2 y 3 que devuelva toda la información necesaria en un paso, esto evitaría que tengas que consultar T2 por cada registro de T1 en el while, que es uno de los motivos principales de la lentitud. En caso de ser posible, esta consulta será la definición del cursor c1 en el código de ejemplo. Puede que sea algo como:

Código:

select 
  t1.usuario, t2.transaccion, t3.cliente, t3.saldo
from t1, t2, t3
 where 
 t1.usuario = t2.usuario 
 and t2.cliente = t3.cliente

Después de tener esta consulta es cuando se analiza el plan de ejecución (informe de como se accede a las tablas) y ver como optimizar los tiempos, donde la solución puede estar en normalización de tablas, uso de índices, paralelismo, etc. El plan de ejecución lo obtienes con el comando EXPLAIN PLAN FOR tu_consulta_sql, ejemplo:

Código:

EXPLAIN PLAN FOR select * from dual;

En cuanto a la propiedad PARALLEL DEGREE N, el valor de N indica la cantidad de procesos en paralelo que se van a iniciar para recorrer los datos + 1, es decir, un degree de 4 implica 4 procesos esclavos para leer porciones de datos más un quinto proceso coordinador encargado de juntar cada porción. Por otro lado, el valor de N no es algo para configurar como parámetro de entrada en un procedimiento, esto se hace con un alter table, además de ser algo estático que se modifica en casos como cambios de hardware.

Tras optimizar la consulta, lo que sigue es evaluar el número de filas simultaneas que subes a memoria en cada fetch del cursor (este número se define con el valor de limit, en el código de ejemplo se suben 100 filas por fetch), aquí no hay receta, incrementa el valor mientras tengas mejoras de rendimiento, y te detienes cuando ya no hace la diferencia, es importante no pasarse con el valor, ya que si pones uno muy alto corres el riesgo de tener problemas de memoria.

Por último, comentas el particionamiento de tablas, esto es almacenar una tabla en diferentes segmentos (partes físicas) donde cada uno contiene información relacionada por algún criterio, por ejemplo una tabla de facturación particionada por año, en caso de una consulta por todas las facturas del año actual, la base de datos puede responder recorriendo una sola partición que es mucho menos trabajo que recorrer todas las filas. Esto se tiene que definir al momento de crear la tabla y no tiene relación con la propiedad degree.

Código:

create table t1
  (id number,
   fecha date )
   partition by range(trunc(fecha, 'YEAR'))
  (
    partition p1 values less than (2011)
  ) ;

En tu caso, diría que el particionamiento no ayuda, ya que la única tabla candidata para esto parece ser T2, y por lo que veo, no tiene un criterio para agrupar la información.

Saludos

[jc3000]

Muy buena explicación, Matanga.

Otra idea que se me ocurre, es que si la Join entre las tres tablas es muy costosa, hacer un full de la tabla mas pequeña y con lo que trae esa parametrizar la join de las otras 2 ( si es posible, claro ). Hacer eso a veces te sorprende sobremanera el resultado, yo he llegado a reducir el tiempo de ejecución de procesos de facturación de varias horas a 20 minutos simplemente dividiendo en 2 ó 3 un cursor que hacía join de varias tablas