PL/SQL Code Optimization

[ggnanaraj]

I have the following code that takes 27 minutes to execute and display the result. The tables are ...

d_academic & d_personal_academic have 244 records each.

d_config_single has 309 records.

Do give your input to optimize this. Thanks.

set serveroutput on size 1000000

truncate table errors;

DECLARE

	err_num NUMBER;
	err_msg VARCHAR2(100);
	s1 number :=0;
	s2 number :=0;
	s3 number :=0;
	s4 number :=0;	

	CURSOR c1 IS
		SELECT fk_employee_id aa, fk_exam_level bb, fk_qualification cc, group_ofstudy dd, 
				institution ee, month_of_passing ff, year_of_passing gg, grade hh 
				FROM u_port.d_academic;
	CURSOR c2 IS
		SELECT fk_employee_id a, fk_qualif_double_config_id b, fk_course_single_config_id c, fk_inst_single_config_id d,
			  	month_of_passing e, year_of_passing f, grade g 
				FROM d_personal_academic;
	CURSOR c5 IS
		SELECT single_config_id p, value q, code r
				FROM d_sip_config_single;
BEGIN
	FOR C1_REC IN C1 LOOP
		FOR C2_REC IN C2 LOOP
			FOR C5_REC IN C5 LOOP
	
				IF ((C1_REC.ee = C5_REC.q) AND (C2_REC.d = C5_REC.p)) AND 
					((C1_REC.aa = C2_REC.a) AND (C1_REC.ff = C2_REC.e) AND 
					(C1_REC.gg = C2_REC.f) AND (C1_REC.hh = C2_REC.g)) THEN

					s1:=s1+1;
					dbms_output.put_line('=========================================================================================');
					dbms_output.put_line('d_academic empid = '|| C1_REC.aa);
					dbms_output.put_line('d_personal_academic empid = '|| C2_REC.a);
	
					dbms_output.put('C1_REC.ee = '|| C1_REC.ee);
					dbms_output.put_line('  C5_REC.q = '|| C5_REC.q);
					dbms_output.put('C2_REC.d = '|| C2_REC.d);
					dbms_output.put_line('  C5_REC.p = '|| C5_REC.p);

	
					dbms_output.put('C1_REC.ff = '|| C1_REC.ff);
					dbms_output.put_line('  C2_REC.e = '|| C2_REC.e);
					dbms_output.put('C1_REC.gg = '|| C1_REC.gg);
					dbms_output.put_line('  C2_REC.f = '|| C2_REC.f);
					dbms_output.put('C1_REC.hh = '|| C1_REC.hh);
					dbms_output.put_line('  C2_REC.g = '|| C2_REC.g);
					dbms_output.put_line('			s1 = '||s1);
				END IF;
			END LOOP C5;	
		END LOOP C2;
	END LOOP C1;

	dbms_output.put_line('=========================================================================================');	
	dbms_output.put_line('s1 = '||s1);
		
EXCEPTION
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SUBSTR(SQLERRM, 1, 100);
		INSERT INTO errors VALUES (err_num, err_msg);
END;
/