Why given query hangs or slow down?

Question

Usually, it executes ok but sometimes once or twice in a week, it got hanged or slowdown in Oracle.

Is there a better way to optimize it?

FOR i IN ( SELECT * FROM process_data) LOOP
    BEGIN
        SELECT employee_plan_id
          INTO lc_emp_plan_id
          FROM employee
         WHERE employeeid = i.emp_id
           AND join_year = (
            SELECT join_year
              FROM employeedata
             WHERE employeeid = i.emp_id
               AND i.joining_date BETWEEN join_date AND termination_date
        );
    END;
    SELECT employee_plan_type
      INTO lc_emp_type
      FROM employee_plans
     WHERE employee_plan_id = lc_emp_plan_id;

    -- Mark failed record if emp_lastname is null
    UPDATE process_data
       SET
        is_failure = 1
     WHERE emp_lastname IS NULL
       AND emp_plan_type = lc_emp_type;
END LOOP;

Remember

SELECT join_year
  FROM employeedata
 WHERE employeeid = i.emp_id
   AND i.joining_date BETWEEN joining_date AND termination_date;

It will always return 1 record and that is proven.

Here lc_emp_plan_id is a variable and this for loop executes within procedure?

Answer 1

The most native explanation of the observed behaviour is, that the number of rows to be processed (process_data) varies. The total elapsed time is linearly proportional to the number of processed rows, so on the days with lot of rows the loop "hangs".

A best way to speed up a is not to use FOR LOOP in PL/SQL.

Simple reformultion it in a SQL statement (this is not always possible, sometimes leads to a complex SQL, but may produce a dramatic speed up.

In your case this should be rather simple exercise:

This query returns the same result as your first loop.

SELECT e.employee_plan_id
  FROM process_data p
  JOIN employee e ON p.emp_id = e.employeeid
  JOIN employeedata ed ON p.emp_id = ed.employeeid
   AND p.joining_date BETWEEN ed.join_date AND ed.termination_date;

Accordingly, you can rewrite the whole procedure with one UPDATE statement.

UPDATE process_data
   SET is_failure = 1
 WHERE emp_lastname IS NULL
   AND emp_plan_type IN (
    SELECT employee_plan_type
      FROM employee_plans
     WHERE employee_plan_id IN (
        /* copy the query above here */
    )
);