Oracle 10g: Posible optimización de volcado masivo de datos

En execucions batch que facin un bolcat massiu de dades en una mateixa taula utilitzant un insert o update per registre dins d'un bloc pel motiu x es poden optimitzar amb l'ús de paràmetres (si el client ho permet) o si fem servir odbc amb bind variables.

Recordem els passos que segueix Oracle per processar una consulta:

1) Validació Sintáctica
2) Validació Semàntica
3) Optimització
4) Generació del QEP (Query Execution Pla)
5) Execució del QEP (Query Execution Pla)

Les sentències poden recollir els paràmetres per valor (where salari> 1000) o un cop compilat la sentència fent ús de Bind Variables (where salari>: b1). L'avantatge de la segona opció és que Oracle compilant un única vegada la sentència i reutilitza el codi compilat per a cada un dels valors per als paràmetres. Però cal anar amb ull ja que en aquest segon cas, Oracle no pot calcular el grau de selectivitat d'una consulta i, en el seu lloc, aplica un grau de selectivitat per defecte (associat a cada tipus d'operació), cosa que pot donar lloc a decisions "equivocades". Per tant, treballant per costos és desaconsellable l'ús de Bind Variables, llevat que treballem amb sentències que es van a executar repetides vegades i que no ofereixin molts dubtes pel que fa als possibles plans d'accés que pot generar.

En casos provats com aquesta el temps d'execució es redueix fins a un 90% en una inserció de 100.000 registres ...

 

Creació de la taula necessària per a la prova:
create table PROVA
( 
NUM number (22),
TEXT varchar (100)
);

 

Càrrega amb paràmetres per valor:
declari
v_i number;
begin
loop
INSERT INTO STA.STA_PRUEBA VALUES (3, '50 ');
v_i := i + 1;
exit when i> 1000000;
end loop;
rollback;
end;

 

Càrrega amb paràmetres utilitzant bind variables

declari 
v_i number;
begin
loop
execute immediate 'INSERT INTO STA.STA_PRUEBA VALUES (: x,: i)' using 3, '50 '; 
v_i: = i + 1; 
exit when i> 1000000; 
end loop;
rollback;
end;

Adjunt els resultats de la prova realitzada abans d'aplicar canvis en producció (un divendres a la tarda )

 

FILES	PER VALOR	AMB BIND VARIABLES
10000	5,3350	0,4370
100000	58,5160	6,1000
1000000	(A)570,1060	(B)54,1950