please tune this pl/sql code

| 1)Create a supvervisor hierarchy to roll-up employees and contingents |
|   with level mgrs.                                                    |
WHENEVER SQLERROR EXIT SQL.SQLCODE;
WHENEVER OSERROR EXIT SQL.OSCODE;
SET SERVEROUTPUT ON;
 
TRUNCATE TABLE gemprod.t_agg_pmtree
/
 
DECLARE
 
     v_ceoid   CHAR(8) ; 
   
   /* LOG VARIABLES */
   n_ins_cnt      NUMBER := 0;
   n_counter      NUMBER := 0;
   n_errors       NUMBER := 0;
   ora_msg        VARCHAR2(100);
   idx_dups       NUMBER :=0;   
         
   upd_count      NUMBER := 0;  -- counter for number records processed       
   i_rpt          NUMBER := 0;  -- counter for number of reports in chain     
   v_prev_supv_id CHAR(8) ;     -- saves previous supvervisor id             
   v_supv_id      CHAR(8);      -- saves search by supvervisor id             
   temp_emp       CHAR(8);
   temp_sup       CHAR(8);
   i_rpt_level    NUMBER := 0;  -- saves rpt level; below CEO is Level 1     
   i_chld_level   NUMBER := 0;  -- saves rpt level; immediate supv is Level 1
     
   CURSOR c1 IS
      SELECT (LEVEL - 1) AS rptlevel
             ,emplid
             ,supervisor_id
             ,reg_temp
      FROM   (SELECT  emplid
                     ,DECODE(emplid,v_ceoid,'99999999',supervisor_id) AS supervisor_id  
                     ,reg_temp
              FROM    gds_t_employee
              WHERE   supervisor_id IS NOT NULL
              AND     empl_status IN ('A','L','P')
              UNION ALL
              SELECT  emplid
                     ,supervisor_id
                     ,'C' AS reg_temp
              FROM    gds_t_contingent
              WHERE   supervisor_id IS NOT NULL
              AND     empl_status IN ('A','L','P'))
      WHERE emplid <> v_ceoid
      START WITH  emplid = v_ceoid
      CONNECT BY PRIOR emplid = supervisor_id; 
       
BEGIN
 
 
 
   BEGIN
  
    SELECT EMPLID into v_ceoid from CEO_TBL;
 
   EXCEPTION
   WHEN OTHERS THEN
     ora_msg := SUBSTR(SQLERRM(SQLCODE),11,100);
     raise_application_error(-20010,ora_msg ||' :: '||' Problem with CEO_TBL');
   END;
 
   FOR v_c1 IN c1 LOOP
      /*initialize variables*/
      v_supv_id      := NULL; 
      v_prev_supv_id := NULL;  
      i_rpt_level    := 0;
      i_rpt          := v_c1.rptlevel;
      i_chld_level   := 0;
 
      FOR i in 1..i_rpt loop
         i_rpt_level  := (i_rpt - i);
         i_chld_level := i;
           
         --first time through loop use immediate supervisor
         --subsequent times use previous supvid
         IF i = 1 THEN 
            v_supv_id := v_c1.emplid;                
         ELSE
            v_supv_id := v_prev_supv_id;    
         END IF; 
  
         BEGIN
            IF v_c1.reg_temp IN ('R','T') THEN
               SELECT supervisor_id 
               INTO   v_supv_id
               FROM   gds_t_employee
               WHERE  emplid = v_supv_id;
            ELSE
               BEGIN
                  SELECT supervisor_id
                  INTO   v_supv_id
                  FROM   gds_t_contingent
                  WHERE  emplid = v_supv_id;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     SELECT supervisor_id
                     INTO   v_supv_id
                     FROM   gds_t_employee
                     WHERE  emplid = v_supv_id;
               END;
            END IF;
         EXCEPTION
             WHEN OTHERS THEN
                v_supv_id := '99999999';
         END;
         
         temp_emp := v_c1.emplid;
	 temp_sup := v_c1.supervisor_id;
                
         v_prev_supv_id := v_supv_id;
 
         BEGIN
            INSERT INTO gemprod.t_agg_pmtree 
               VALUES (v_supv_id,     --SUPVERVISOR ID 
                       v_c1.emplid,   --CHILD EMPLID    
                       i_rpt_level,   --EQUATES TO THE TOTAL NUMBER OF REPORTS A PERSON HAS
                       i_chld_level,  --INCREMENTED UP FROM IMMEDIATE SUPV 
                       SYSDATE);      --TODAY'S DATE  
                
            n_counter := n_counter + 1;
            n_ins_cnt := n_ins_cnt + 1; 
 
         EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
               idx_dups := idx_dups + 1;
            WHEN OTHERS THEN
               ora_msg := SUBSTR(SQLERRM(SQLCODE),11,100);
               DBMS_OUTPUT.PUT_LINE(ora_msg || ' v_c1.emplid: ' || v_c1.emplid);
                 n_errors := n_errors + 1;
         END;
 
         IF n_counter > 5000 THEN
            n_counter := 0;
            COMMIT;
         END IF;
      END LOOP;
   END LOOP;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE) || ' LOAD_AGG_PMTREE');
      DBMS_OUTPUT.PUT_LINE(ora_msg || ' v_c1.emplid: ' || temp_emp );
      DBMS_OUTPUT.PUT_LINE(ora_msg || ' v_c1.emplid: ' || temp_sup );
      RAISE_APPLICATION_ERROR(-20100, ' LOAD_AGG_PMTREE');
END;
/
 
EXIT;