CODEWITHSUNDEEP
performanceoracle-databaseplsql
Ahmet Karakaya
Ahmet Karakaya

Reputation: 10139

Tune the PLSQL limit parameter in bulk update

I have to tables and each have at least 35M data. I have coded a bulk update script as following I would like to know what I need to look at to tune Limit=10000 parameter to run this script in better performance.

DECLARE
    CURSOR rec_cur IS
    SELECT  id,sirname
    FROM table_user;

    TYPE num_tab_t IS TABLE OF NUMBER;
    TYPE vc2_tab_t IS TABLE OF VARCHAR2(15);

    id_tab NUM_TAB_T;
    sirname_tab VC2_TAB_T;
BEGIN
    OPEN rec_cur;
    LOOP
       FETCH rec_cur BULK COLLECT INTO id_tab, sirname_tab LIMIT 10000;
        EXIT WHEN id_tab.COUNT() = 0;
        FORALL i IN id_tab.FIRST .. id_tab.LAST        
           UPDATE table_user_backup
            SET    sirname = sirname_tab(i)
            ,      sirname_date = sysdate
            WHERE  id = id_tab(i);
    END LOOP;
    CLOSE rec_cur;
END;

I have tested three possible ways of bulk updating, sharing each of them elapsed time. It shows that SQL is better than pl-sql forall bulk update. After changing Limit parameter ranging 1000-100000, I could not see any difference

-- Bulk update --for 2.5M entries


--Takes 4 Minutes
DECLARE
    CURSOR rec_cur IS
        SELECT  id,name
        FROM table_user;
    BEGIN

       FOR sub in rec_cur
       LOOP
           UPDATE table_user_backup
                SET    name_date = sysdate
               WHERE  id = sub.id;
       END LOOP;

END;


--takes 2.5 minutes
DECLARE
    CURSOR rec_cur IS
    SELECT  id,name
    FROM table_user;

    TYPE num_tab_t IS TABLE OF NUMBER;
    TYPE vc2_tab_t IS TABLE OF VARCHAR2(20);

    id_tab NUM_TAB_T;
    name_tab VC2_TAB_T;
BEGIN
    OPEN rec_cur;
    LOOP
       FETCH rec_cur BULK COLLECT INTO id_tab, name_tab LIMIT 10000;
        EXIT WHEN id_tab.COUNT() = 0;

        FORALL i IN id_tab.FIRST .. id_tab.LAST        
           UPDATE table_user_backup
            SET    name = name_tab(i)
            ,      name_date = sysdate
            WHERE  id = id_tab(i);
    END LOOP;
    CLOSE rec_cur;
END;

--SQL is faster than pl sql
--takes 2.0 minutes
UPDATE table_user_backup tu_backup
   SET (name, name_date) = (SELECT name, sysdate
                                    FROM table_user tu
                                   WHERE tu_backup.id = tu.id)
 WHERE EXISTS( SELECT 1
                 FROM table_user tu
                WHERE tu.id=tu_backup.id);

Upvotes: 1

Views: 1768

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

If that is the actual script, it should be more efficient to get rid of the PL/SQL processing and just do everything in SQL. Bulk processing in PL/SQL is more efficient than row-by-row processing in PL/SQL but SQL is more efficient than PL/SQL.

UPDATE table_user_backup tub
   SET (sirname, sirname_date) = (SELECT sirname, sysdate
                                    FROM table_user tu
                                   WHERE tu.id = tub.id)
 WHERE EXISTS( SELECT 1
                 FROM table_user
                WHERE tu.id = tub.id);

If that is not efficient enough, you'll need to tell us what indexes are available, what the query plan is, how many rows are involved, etc.

Upvotes: 6

Related Questions