Быстрый коммит каждой 1000-и записей в цикле, счетчик или функция mod()

Finding the Fastest Way
Within a large loop, if I want to commit every 1,000 (or million, or billion) records, which is faster—using mod() and then commit, as in:

LOOP
    cnt := cnt + 1;
    IF ( MOD( cnt, 1000 ) ) = 0 THEN          
       COMMIT;
    END IF;
END LOOP;

or setting up a counter, THEN commit, and then counter := 0, as in:

LOOP
   cnt := cnt + 1;
   IF cnt = 1000 THEN        
     COMMIT;
     cnt := 0;
   END IF;
END LOOP;


Do you want to commit in the loop in the first place? If you don’t, the fastest way is to not even consider committing every n rows. You should configure sufficient rollback and do the update in a single UPDATE statement. Don’t use a loop at all. It is slower than an INSERT INTO SELECT or a single UPDATE statement.

The second-fastest way is to configure sufficient rollback and do the transaction in a single loop without commits. Each time you commit, you must wait for the log-writer process to fully flush the buffers. If you just keep going, LGWR does this in the background—you’ll have to wait once at the end instead of every n rows. The commit is what will slow you down. You’ll have to consider how to restart this process too. Watch out for ORA-01555, which will inevitably get you if you’re updating the table you’re reading.

As for the code above, we can use the source-code profiler provided in Oracle8i to analyze this. I created and ran the following procedures:

CREATE OR REPLACE PROCEDURE do_mod
AS
    cnt NUMBER := 0;
BEGIN
    DBMS_PROFILER.start_profiler( 'mod' );
    FOR i IN 1 .. 500000
    LOOP
        cnt := cnt + 1;
        IF ( MOD(cnt,1000) = 0 )
        THEN
            COMMIT;
        END IF;
    END LOOP;
    DBMS_PROFILER.stop_profiler;
 
END;
/
CREATE OR REPLACE PROCEDURE no_mod
AS
    cnt NUMBER := 0;
BEGIN
    DBMS_PROFILER.start_profiler( 'no mod'
);
    FOR i IN 1 .. 500000
    LOOP
        cnt := cnt + 1;
        IF ( cnt = 1000 )
        THEN
            COMMIT;
            cnt := 0;
        END IF;
    END LOOP;
 
    DBMS_PROFILER.stop_profiler;
END;
/

Running the profiler reports, I find

Percentage of time in each module,
summarized across runs

UNIT_OWNER     UNIT_NAME      SECS     PERCENTAG
----------     --------       ----     ---------
OPS$TKYTE      DO_MOD         8.18     71.67
OPS$TKYTE      NO_MOD         3.23     28.32
SYS            DBMS_PROFILER   .00       .00

Already, this shows that the MOD function takes longer: over 8 seconds in that routine, compared to just over 3 seconds in the routine without the MOD function. To prove that it is the MOD function itself, see Listing 1. Using MOD took about 5.5 seconds, whereas doing if ( cnt=1000 ) took 1 second, plus the time to do cnt := 0, for a total of about 1.5 seconds.

Источник: http://www.oracle.com/oramag/oracle/01-jul/o41asktom.html

Оставить комментарий