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?

I see two possibilities: either your data contains some weirdness, e.g. your subquery returns a trillion rows (you say this is proven to be false), or there is some other resource contention on the server (e.g. locking). Have you looked at the server's memory, CPU and disk usage? — Neville Kuyt, Jun 17 '19 at 12:53
what are `joining_date` and `termination_date` in the predicate? Are they parameters or are they simply column names? — Boneist, Jun 17 '19 at 12:53
they are simply column. sorry..it is join_date instead of joining_date. — PalamCoder, Jun 17 '19 at 12:55
Why are you looping? In your real code, what is being done in the loop? Are you updating something, inserting, etc? — Boneist, Jun 17 '19 at 12:57
no..I have not checked memory, cpu. I mean...I checked in the monitor session and this query was showing as 'active session' and above query was shown and waiting time was around 20 mins. — PalamCoder, Jun 17 '19 at 12:59
yes...I am updating on the basis of parameter return from the query. — PalamCoder, Jun 17 '19 at 12:59
Please update your question to include the update statement. It's most likely that you're looping unnecessarily here, but without more info, it's difficult to say. — Boneist, Jun 17 '19 at 13:09
Is your update statement supposed to be linked back to the outer cursor in some way? — Boneist, Jun 17 '19 at 13:34
If it's only slow once or twice a week, could be because of something else resource intensive that only runs once in a great while and just bad timing. Something that takes a lot of cpu time, causes disc caches to be flushed, locked tables, etc — Shawn, Jun 17 '19 at 13:55
Which query is slow? You posted a cursor loop, a `select into` and an update. Any one of them might lead to performance problems, especially the ones in the loop. What does `dbms_profiler` say? — William Robertson, Jun 17 '19 at 22:14

score 2 · Accepted Answer · edited Jun 19 '19 at 14:14

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 */
    )
);

this is useful but removing it all the places (somewhere even complex) is tiresome...however I found that table employeedata is creating problem in making simple select sometimes...any other solution would be appreciated. — PalamCoder, Jun 18 '19 at 09:42
If you have a problem with specific query, open a new question and focus on it only. You will need to provide more details to the query, *execution plan* at least. Please check e.g. [here](https://stackoverflow.com/a/34975420/4808122) what information you should provide — Marmite Bomber, Jun 18 '19 at 09:45

Why given query hangs or slow down?

1 Answers1