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(+)
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@tripoli i
,chassis_masters@tripoli chm
,ssl@tripoli s
,accounts@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@tripoli h
,ssl@tripoli s
,accounts@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;

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@tripoli gc
,gate_visit@tripoli gv
,ssl@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@tripoli gc
,gate_visit@tripoli gv
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit;

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_i d);
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_i d);
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;

On 12 May 2006 12:27:12 -0700, "lou_nyc" <lnlx@aol.com> wrote:

>I have this proc....it is taking wayyy too much time....I am trying to
>cut it the
>amount of time.. any suggestions

Redesign the data model: implementing subtypes in separate tables is
not one of the 50 ways to leave your lover (Simon and Garfunkel) but
it is one of the 50 certified ways to hell.

--
Sybrand Bakker, Senior Oracle DBA

lou_nyc 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

[]
> from inv_chassis@tripoli i
> ,chassis_masters@tripoli chm
> ,ssl@tripoli s
> ,accounts@tripoli a

remote links can slow things down.
Sybrand's advice is good too.

ED

Ed Prochak wrote:
> lou_nyc 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
>
> []
> > from inv_chassis@tripoli i
> > ,chassis_masters@tripoli chm
> > ,ssl@tripoli s
> > ,accounts@tripoli a
>
> remote links can slow things down.
> Sybrand's advice is good too.
>
> ED

cant remove the db links....