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

Oracle 10g: Possible optimization in massive data dump il_masacratore Tue, 02/16/2010 - 13:59

In batch runs to make a massive data dump into the same table using an INSERT or UPDATE for register within a block, the process can be optimized with the use of parameters (if client supports it) or if we use ODBC with bind variables.
Recall the steps taken by Oracle to process a query:
1) Sintactic Validation 
2) Semantic Validation
3) Optimization 
4) Generation of the QEP (Query Execution Plan)
5) Implementation of the QEP (Query Execution Plan)
Sentences can pick up the parameters by value (where salary > 1000) or once the sentence is compiled using Bind Variables (where salary>: b1). The advantage of the second option is that Oracle compile the sentence only one-time and reuses the compiled code for each of the values for the parameters.
But we must be aware because in the latter case because Oracle can't calculate the degree of selectivity of a query and, instead, apply a degree of selectivity by default (associated with each type of operation), which can give in wrong decisions.
Therefore, is inadvisable to use Bind Variables if you work with costs, except that we work with sentences to be executed repeatedly and not offering many doubts as to the possible access plans.
In proven cases like this, the execution time is reduced by up to 90% on an insert of 100.000 records ...

Creating the necessary table for testing:


create table TEST (  NUM number (22),  TEXT varchar (100) );

Loading parameters by value:


declare v_i number; 
begin
    loop
       INSERT INTO TEST VALUES (3, '50');
       v_i := v_i + 1;
       exit when v_i > 1000000;
    end loop;
    rollback;
  end; 

Loading parameters using bind variables


declare v_i number;
begin
  loop
    execute immediate 'INSERT INTO TEST VALUES (:x, :y)' using 3, '50';
    v_i := v_i + 1;
    exit when v_i > 1000000;
  end loop;
  rollback;
end;

I enclose the test results before applying changes in production (on a Friday afternoon )

 

ROWS BY VALUE WITH BIND VARIABLES
10000 5,3350 0,4370
100000 58,5160 6,1000
1000000 (A)570,1060 (B)54,1950