Some users contacted me this morning about a yearly procedure they run, that usually takes about 5 minutes. It updates some history tables from data retrieved over a db link.
This time when they run it they say it's taking hours - I did some checking and it seems to be hung on the last update statement. This is the stored procedure:
create or replace PROCEDURE load_is_mark_approved (last_run CHAR) AS
/* 13 January 2016 CB PS-2502 Load mark approved directly from pasi to district
29 Feb 16 Fix bug - joining on wrong field a.ab_course_enroll_markid should be a.course_enrolid
2 May 17 PS-2502 Another join error - using c.id and should be c.dcid
*/
last_run_date DATE;
schYear NUMBER;
currYear NUMBER;
CURSOR mark_c IS
SELECT a.is_mark_approved, c.course_number, c.schoolid, RPAD(c.section_number, 2) AS section_number, c.termid, d.student_number, c.studentid AS ccStud, d.id AS demoStud
FROM ps.ab_course_enroll_mark@psprod a, ps.ab_course_enroll@psprod b, ps.cc@psprod c, ps.students@psprod d
WHERE a.course_enrolid = b.ab_course_enrollid AND b.local_id = c.dcid AND b.state_province_id = D.state_studentNumber
AND a.whenModified > last_run_date
ORDER BY d.student_number, c.course_number, c.termid DESC, c.section_number, a.whenmodified;
BEGIN
BEGIN
last_run_date := TO_DATE(last_run, 'YYYYMMDD');
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101, 'Parameter date must be in YYYYMMDD format');
END;
SELECT ps.ps_date_procs.getCurrentSISyear INTO currYear FROM DUAL;
FOR mark_rec IN mark_c LOOP
-- we may have duplicates with dropped courses - do we care if they have set the flag anyway?
IF mark_rec.ccStud = mark_rec.demoStud THEN -- otherwise, we have a mismatch with the cc and the ASN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(mark_rec.student_number ) || mark_rec.course_number || TO_CHAR(mark_rec.termid));
SELECT ps.ps_date_Procs.getSisYear(ABS(mark_rec.termid)) INTO schYear FROM DUAL;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(schYear) || ' ' || TO_CHAR(currYear));
IF schYear = currYear THEN
UPDATE sis.sis_st_courses_full
SET mark_approved_by_prov = CASE WHEN mark_rec.is_mark_approved = 1 THEN 'Y' ELSE NULL END
WHERE student_no = mark_rec.student_number
AND school_no = LPAD(mark_rec.schoolid, 3, '0')
AND course_cd || course_suffix = mark_rec.course_number
AND course_section = mark_rec.section_number;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('0 rows updated');
UPDATE sis.sis_st_crs_hst
SET mark_approved_by_prov = CASE WHEN mark_rec.is_mark_approved = 1 THEN 'Y' ELSE NULL END
WHERE student_no = mark_rec.student_number
AND school_yr = schYear
AND school_no = LPAD(mark_rec.schoolid, 3, '0')
AND course_cd || course_suffix = mark_rec.course_number AND course_section = mark_rec.section_number;
DBMS_OUTPUT.PUT_LINE(to_char(sql%rowcount) || ' rows updated');
END IF;
ELSE
UPDATE sis.sis_st_crs_hst
SET mark_approved_by_prov = CASE WHEN mark_rec.is_mark_approved = 1 THEN 'Y' ELSE NULL END
WHERE student_no = mark_rec.student_number
AND school_yr = schYear
AND school_no = LPAD(mark_rec.schoolid, 3, '0')
AND course_cd || course_suffix = mark_rec.course_number AND course_section = mark_rec.section_number;
DBMS_OUTPUT.PUT_LINE(to_char(sql%rowcount) || ' history rows updated');
END IF;
END IF;
END LOOP;
COMMIT;
END;
It gets to the last update statement after the ELSE, and just hangs without updating anything. It is updating a table via a view, when I query the view for the fields it's updating nothing is being updated.
When I run the select over dblink statement that populates the cursor it returns results very fast.
When I look in OEm there are no blocking locks or deadlocks, and the SQL showing in OEM for the session SID is:
UPDATE SIS.SIS_ST_CRS_HST SET MARK_APPROVED_BY_PROV = CASE WHEN :B6 = 1 THEN 'Y' ELSE NULL END WHERE STUDENT_NO = :B5 AND SCHOOL_YR = :B4 AND SCHOOL_NO = LPAD(:B3 , 3, '0') AND COURSE_CD || COURSE_SUFFIX = :B2 ANDCOURSE_SECTION = :B1
How can I best track down the cause of the hang to see what the issue is?
thanks in advance.