Frequent or Infrequent Commit I'm trying to run an update query but still taking to long I have a commit in it.
Is it better to set the commit to a higher value (5000) or lower (1)?
I have to update 15 million records in a table of 21 million

edit here's my query for the update but I need to know how low/high I should set the commit

DECLARE
CURSOR c_itemloc
IS
  SELECT item, loc 
  FROM dc_item_loc;

 l_item item_loc.item%TYPE;
 l_loc item_loc.loc%TYPE;
 i        NUMBER;
 l_commit VARCHAR2(1) := 'Y';

 BEGIN
   i:=0;
   FOR r_itemloc IN c_itemloc
   LOOP
     i := i+1;
     UPDATE item_loc il
     SET il.status             = 'D',
        il.last_update_datetime = get_vdate,
        IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
     WHERE item                = r_itemloc.item
          AND loc                   = r_itemloc.loc;

     IF l_commit = 'Y' AND mod(i, 5000) = 0  THEN 
        COMMIT; 
     END IF;

    END LOOP;

  EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line('YOU FAIL');
  END;
Why are you doing this in PL/SQL in the first place?  The most efficient way to do anything in an Oracle database should be to do it in SQL

 UPDATE item_loc il
 SET il.status             = 'D',
    il.last_update_datetime = get_vdate,
    IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
 WHERE EXISTS( SELECT 1
                 FROM dc_item_loc d
                WHERE d.item = il.item
                  AND d.loc  = il.loc );