Re: Looking for ways/suggestions on how to improve this process

nycman wrote:
> I have this proc....it is taking wayyy too much time....I am trying to
> cut it the
> amount of time.. any suggestions...
>
> PROCEDURE chassis
> IS
> CURSOR his_CUR IS
> select i.chassis
> ,i.chassis_id
> ,i.ssl_user_code
> ,INITCAP(s.long_description) SSL_USER_DESCRIPTION
> ,chm.lt_code
> ,i.in_date
> ,i.in_visit
> ,i.in_reference_id
> ,i.out_date
> ,i.out_visit
> ,i.out_reference_id
> ,i.in_trucker_code TRUCKER_CODE
> ,a.name TRUCKER_NAME
> ,i.remarks
> ,'F' AREA
> from inv_chassis i
> ,chassis_masters chm
> ,ssl s
> ,accounts a
> where i.chassis = chm.chassis
> and i.ssl_user_code = s.code
> and i.in_trucker_code = a.code(+)

Well, you should explain the plan of this (or really, do the 10046 trace).

> UNION ALL
> select h.chassis
> ,h.chassis_id
> ,h.ssl_user_code
> ,INITCAP(s.long_description) SSL_USER_DESCRIPTION
> ,h.lt_code
> ,h.in_date
> ,h.in_visit
> ,h.in_reference_id
> ,h.out_date
> ,h.out_visit
> ,h.out_reference_id
> ,h.out_trucker_code TRUCKER_CODE
> ,a.name TRUCKER_NAME
> ,h.remarks
> ,'F' AREA
> from his_chassis h
> ,ssl s
> ,accounts a
> where h.voided_date IS NULL
> and h.ssl_user_code = s.code
> and h.out_trucker_code = a.code(+)
> UNION ALL
> select i.chassis
> ,i.chassis_id
> ,i.ssl_user_code
> ,INITCAP(s.long_description) SSL_USER_DESCRIPTION
> ,chm.lt_code
> ,i.in_date
> ,i.in_visit
> ,i.in_reference_id
> ,i.out_date
> ,i.out_visit
> ,i.out_reference_id
> ,i.in_trucker_code TRUCKER_CODE
> ,a.name TRUCKER_NAME
> ,i.remarks
> ,'T' AREA
> from inv_chassis_at_tripoli i
> ,chassis_masters_at_tripoli chm
> ,ssl_at_tripoli s
> ,accounts_at_tripoli a
> where i.chassis = chm.chassis
> and i.ssl_user_code = s.code
> and i.in_trucker_code = a.code(+)
> UNION ALL
> select h.chassis
> ,h.chassis_id
> ,h.ssl_user_code
> ,INITCAP(s.long_description) SSL_USER_DESCRIPTION
> ,h.lt_code
> ,h.in_date
> ,h.in_visit
> ,h.in_reference_id
> ,h.out_date
> ,h.out_visit
> ,h.out_reference_id
> ,h.out_trucker_code TRUCKER_CODE
> ,a.name TRUCKER_NAME
> ,h.remarks
> ,'T' AREA
> from his_chassis_at_tripoli h
> ,ssl_at_tripoli s
> ,accounts_at_tripoli a
> where h.voided_date IS NULL
> and h.ssl_user_code = s.code
> and h.out_trucker_code = a.code(+);
> -- ordering by chassis and in_date
> -- ORDER BY 1, 6;
>
> his1_REC his_CUR%ROWTYPE := NULL;
> --his2_REC his_CUR%ROWTYPE := NULL;
>
> TYPE his_tab_type IS TABLE OF his_cur%ROWTYPE;
> hisTab his_tab_type;
>
> BEGIN
> EXECUTE IMMEDIATE 'truncate TABLE nvrm_chas_temp';
>
> OPEN his_CUR;
> LOOP
> FETCH his_CUR BULK COLLECT INTO hisTab LIMIT 5000 ;
> nc := 0;
> ChassisTab.DELETE;
> FOR K IN 1 .. hisTab.COUNT LOOP
> out4hours_PROC(his1_REC, hisTab(K));
> -- COMMIT;
> -- move his rec up...
> his1_REC := hisTab(K);
> END LOOP;
> FORALL K IN 1 .. ChassisTab.COUNT
> INSERT INTO nvrm_chas_temp VALUES ChassisTab(K);
> COMMIT;
Yowsa! Bad stuff in nyc, man.

>
> EXIT WHEN his_CUR%NOTFOUND;
> END LOOP;
> CLOSE his_CUR;
> COMMIT;
> EXCEPTION
> WHEN OTHERS THEN
> IF his_CUR%ISOPEN THEN
> CLOSE his_CUR;
> END IF;
> ROLLBACK;
> RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.chassis -
> '||SQLERRM);
> END chassis;
>
> end nvrm_PKG;
> /
>
> The out4hours_PROC is the following;
>
> PROCEDURE out4hours_PROC(his_rec_1 IN chas_his_rec_TYPE
> ,his_rec_2 IN chas_his_rec_TYPE)
> IS
> v_booking gate_containers.booking%TYPE := NULL;
> v_action gate_containers.action%TYPE := NULL;
> v_coop_date DATE := NULL;
> v_user ssl.code%TYPE := NULL;
> v_user_desc ssl.long_description%TYPE := NULL;
>
> CURSOR fg_cur(pvisit gate_visit.visit%TYPE
> ,pref_id gate_containers.reference_id%TYPE
> )
> IS
> SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code,
> s.long_description
> FROM gate_containers gc
> ,gate_visit gv
> ,ssl s
> WHERE gc.visit = pvisit
> AND gc.reference_id = pref_id
> AND gc.visit = gv.visit
> AND gc.ssl_user_code = s.code(+);
>
> CURSOR tg_cur(pvisit gate_visit.visit%TYPE
> ,pref_id gate_containers.reference_id%TYPE
> )
> IS
> SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code,
> s.long_description
> FROM gate_containers_at_tripoli gc
> ,gate_visit_at_tripoli gv
> ,ssl_at_tripoli s
> WHERE gc.visit = pvisit
> AND gc.reference_id = pref_id
> AND gc.visit = gv.visit
> AND gc.ssl_user_code = s.code(+);
>
> CURSOR fa_cur(pvisit gate_visit.visit%TYPE
> ,pref_id gate_containers.reference_id%TYPE
> )
> IS
> SELECT gc.action, gv.coop_in_date
> FROM gate_containers gc
> ,gate_visit gv
> WHERE gc.visit = pvisit
> AND gc.reference_id = pref_id
> AND gc.visit = gv.visit;
>
> CURSOR ta_cur(pvisit gate_visit.visit%TYPE
> ,pref_id gate_containers.reference_id%TYPE
> )
> IS
> SELECT gc.action, gv.coop_in_date
> FROM gate_containers_at_tripoli gc
> ,gate_visit_at_tripoli gv
> WHERE gc.visit = pvisit
> AND gc.reference_id = pref_id
> AND gc.visit = gv.visit;

So you are joining information over dblinks? You may be running into issues (both bug and inherent) with what is being done where - you may need to go to materialized views or something (like hints) here. Again, tracing should point you in the right direction.

>
> BEGIN
> IF his_rec_1.chassis = his_rec_2.chassis THEN
> IF his_rec_1.out_date IS NULL OR his_rec_1.out_visit IS NULL OR
> his_rec_1.out_reference_id IS NULL THEN
> RAISE EXIT_EXCEPTION;
> END IF;
> IF his_rec_2.in_visit IS NULL OR his_rec_2.in_reference_id IS
> NULL THEN
> RAISE EXIT_EXCEPTION;
> END IF;
> IF his_rec_1.area = 'F' THEN
> OPEN fg_cur(his_rec_1.out_visit,his_rec_1.out_reference_id);
> FETCH fg_cur INTO v_action, v_booking, v_coop_date, v_user,
> v_user_desc;
> CLOSE fg_cur;
> IF v_action IS NULL
> THEN
> OPEN tg_cur(his_rec_1.out_visit,
> his_rec_1.out_reference_id);
> FETCH tg_cur INTO v_action, v_booking, v_coop_date,
> v_user, v_user_desc;
> CLOSE tg_cur;
> IF v_action IS NULL
> THEN
> RAISE EXIT_EXCEPTION;
> END IF;
> END IF;
>
> ELSE
> OPEN tg_cur(his_rec_1.out_visit,his_rec_1.out_reference_id);
> FETCH tg_cur INTO v_action, v_booking, v_coop_date, v_user,
> v_user_desc;
> CLOSE tg_cur;
> IF v_action IS NULL
> THEN
> RAISE EXIT_EXCEPTION;
> END IF;
>
> END IF;
> IF v_coop_date IS NULL OR v_action <>
> global_PKG.DELIVERED_NAKED_CHASSIS THEN
> RAISE EXIT_EXCEPTION;
> END IF;
> -- Chassis has to have returned thru Coop Depot NAKED
> OPEN fa_cur(his_rec_2.in_visit,his_rec_2.in_reference_id);
> FETCH fa_cur INTO v_action, v_coop_date;
> CLOSE fa_cur;
> IF v_action IS NULL
> THEN
> OPEN ta_cur(his_rec_2.in_visit,his_rec_2.in_reference_id);
> FETCH ta_cur INTO v_action, v_coop_date;
> CLOSE ta_cur;
> IF v_action IS NULL
> THEN
> RAISE EXIT_EXCEPTION;
> END IF;
> END IF;
>
> IF v_coop_date IS NULL OR v_action <>
> global_PKG.RECEIVED_NAKED_CHASSIS THEN
> RAISE EXIT_EXCEPTION;
> END IF;
> -- and, Chassis has to have out for more than 4 hours...
> IF his_rec_2.in_date - his_rec_1.out_date > 1/6 THEN
> write_to_chas_temp_PROC(his_rec_1
> ,his_rec_2.in_date
> ,his_rec_2.in_visit
> ,v_booking
> ,v_user
> ,v_user_desc);
> END IF;
> END IF;
> EXCEPTION
> WHEN EXIT_EXCEPTION THEN
> NULL;
> WHEN OTHERS THEN
> ROLLBACK;
> RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.out4hours_PROC
> - '||SQLERRM);
> END;
See Tom Kyte's writings (asktom.oracle.com, though you ought to read his books) on committing in a cursor loop and if you can translate this to plain SQL (though those NULL exceptions... oh well, I won't make design comments) . For more on tracing, see the performance tuning guide, hotsos.com, Jonathan Lewis' and friends writings (ie http://www.jlcomp.demon.co.uk/faq/sql_trace.html and check out his books).

And of course, versions and platforms of everything is important.

jg

--
@home.com is bogus.
Shame, shame.
http://www.signonsandiego.com/uniontrib/20060515/news_1n15china.html