Home » SQL & PL/SQL » SQL & PL/SQL » Urgent ! 2 lakh records in database
| Urgent ! 2 lakh records in database [message #27408] |
Tue, 30 September 2003 23:20  |
Sunil
Messages: 132
Registered: September 1999
|
Senior Member |
|
|
Hi,
I have a problem with one PL/SQL procedure. The thing is that the procedure fetches about 2 lakh records by joining close to 4 tables ecah of which has about 7 lakh records, Once the select stament fetches these 2 lack records (i.e after joining the number of records are about 2 lakh) we need to update each record with a certain values for which another cursor is writen, and thereafter insert data into temp table, and from there it is aggregated and loaded into another table. the result table after aggregation has close to 60,000 records. Now my problem is that the procedure takes 12 hours to run. Is there any other way to reduce optimise the speed of the procedure?
I need help urgently
Thanks
|
|
|
|
|
|
| Re: Urgent ! 2 lakh records in database [message #27412 is a reply to message #27409] |
Wed, 01 October 2003 02:54  |
Sunil
Messages: 132
Registered: September 1999
|
Senior Member |
|
|
the plsql code is
PROCEDURE up_datecross(path IN VARCHAR2,filename IN VARCHAR2) AS
msg VARCHAR2(118);
rec_cnt NUMBER:=0;
output_file UTL_FILE.FILE_TYPE;
l_parentorderplanorder NUMBER;
l_parentorderrecship NUMBER;
l_parentordervll NUMBER;
l_parentorderschedrec NUMBER;
l_loadqty NUMBER;
l_prodbininv NUMBER;
l_inv NUMBER;
CURSOR dc_cur IS
SELECT
depdmdorder id,
ddo.loc loc,
ddo.item item,
i.descr,
ddo.needdate needdate,
ddo.parentordertype parentordertype,
ddo.qty ddoqty,
sku.cd_controller_mrp mrpcontroller,
sku.cd_grp_plng_prdc subgroup
-- to_NUMBER(inv.qty) invqty,
-- inv.store store,
-- inv.type type
FROM
STSC.depdmdorder ddo, --7lak
-- STSC.inventory inv,
STSC.item i, --19thos
STSC.loc , --56rows
STSC.SKU --1 lakh
WHERE
ddo.item=i.item
AND i.prodline= 'COMP'
AND ddo.loc = loc.loc
AND loc.cd_typ_loc <> 'SUPP'
AND ddo.item = sku.item
AND ddo.loc = sku.loc
-- AND ddo.item = inv.item
-- AND ddo.loc = inv.loc
-- and inv.type = 1 ---currently fetching
/*
GROUP BY
TO_cHAR(DEPDMDORDER),
ddo.loc ,
ddo.item,
i.descr,
TO_DATE(ddo.needdate,'DD-MON-YY') ,
ddo.parentordertype ,
to_NUMBER(inv.qty) ,
inv.store ,
inv.type ;*/
;
dc_cur_rec dc_cur%ROWTYPE;
CURSOR dc_insert IS
SELECT
loc loc,
itm item,
nam_mrpctrl mrpctlr,
subgrp,
txt_descr descr,
trunc(dt_need) needdate,
sum(QTY_ORD_PLN) poqty,
sum(QTY_SHP_RCMND) rsqty,
sum(QTY_RCPT_SCH) srqty,
sum(QTY_LN_LOAD_VHCL) vlqty
-- sum(QTY_INV) invqty,
-- sum(QTY_INV_BIN_PRDN) prodbinqty
FROM
gpsi.ldr_datecross_dts
group by
loc,
itm ,
nam_mrpctrl,
subgrp,
txt_descr ,
trunc(dt_need);
dc_rec dc_insert%ROWTYPE;
CURSOR updateinv_cur is
select
item,
loc,
qty,
type,
store
from STSC.inventory i
where type = 1 and (i.item, i.loc) in (select dc.itm, dc.loc from GPSI.datecross_dts dc);
updateinv_rec updateinv_cur%ROWTYPE;
BEGIN
output_file := UTL_FILE.FOPEN (path,filename, 'W');
msg:='DATECROSS STARTED ON '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss');
UTL_FILE.PUT_LINE (output_file, msg);
DELETE gpsi.datecross_dts;
delete gpsi.ldr_datecross_dts;
COMMIT;
OPEN dc_cur;
LOOP
FETCH dc_cur INTO dc_cur_rec;
EXIT WHEN dc_cur%NOTFOUND;
l_parentorderplanorder:= NULL;
l_parentorderrecship := NULL;
l_parentordervll := NULL;
l_parentorderschedrec := NULL;
l_inv := NULL;
l_prodbininv := NULL;
insert into gpsi.ldr_datecross_dts
(cd_id,
loc,
itm,
dt_need,
nam_mrpctrl,
subgrp,
txt_descr,
QTY_RCPT_SCH,
QTY_ORD_PLN,
QTY_SHP_RCMND,
QTY_LN_LOAD_VHCL,
QTY_INV ,
QTY_INV_BIN_PRDN)
VALUES
(dc_cur_rec.id,
dc_cur_rec.loc,
dc_cur_rec.item,
dc_cur_rec.needdate,
dc_cur_rec.mrpcontroller,
dc_cur_rec.subgroup,
dc_cur_rec.descr,
null,
null,
null,
null,
null,
null);
IF dc_cur_rec.parentordertype = 2 THEN
l_parentorderplanorder := dc_cur_rec.ddoqty;
UPDATE gpsi.ldr_datecross_dts
SET QTY_ORD_PLN = l_parentorderplanorder
where cd_id = dc_cur_rec.id;
ELSIF dc_cur_rec.parentordertype = 4 THEN
l_parentorderrecship := dc_cur_rec.ddoqty;
UPDATE gpsi.ldr_datecross_dts
SET QTY_SHP_RCMND = l_parentorderrecship
where cd_id = dc_cur_rec.id;
ELSIF dc_cur_rec.parentordertype = 5 THEN
l_parentordervll := dc_cur_rec.ddoqty;
UPDATE gpsi.ldr_datecross_dts
SET QTY_LN_LOAD_VHCL = l_parentordervll
where cd_id = dc_cur_rec.id;
ELSIF dc_cur_rec.parentordertype = 6 THEN
l_parentorderschedrec := dc_cur_rec.ddoqty;
UPDATE gpsi.ldr_datecross_dts
SET QTY_RCPT_SCH = l_parentorderschedrec
where cd_id = dc_cur_rec.id;
ELSE
NULL;
END IF;
/*
IF dc_cur_rec.type = 1 AND dc_cur_rec.store <> 'PRODBININV' THEN
l_inv:= dc_cur_rec.invqty;
UPDATE gpsi.ldr_datecross_dts
SET QTY_INV = l_INV
where cd_id = dc_cur_rec.id;
ELSIF
dc_cur_rec.type = 1 AND dc_cur_rec.store = 'PRODBININV' THEN
l_prodbininv:= dc_cur_rec.invqty;
UPDATE gpsi.ldr_datecross_dts
SET QTY_INV_BIN_PRDN = l_PRODBININV
where cd_id = dc_cur_rec.id;
ELSE
NULL;
END IF;
*/
END LOOP;
CLOSE dc_cur;
COMMIT;
open dc_insert;
loop
fetch dc_insert into dc_rec;
exit when dc_insert%NOTFOUND;
INSERT INTO gpsi.datecross_dts
(loc,
itm,
nam_mrpctrl,
subgrp,
dt_need,
txt_descr,
QTY_RCPT_SCH,
QTY_ORD_PLN,
QTY_SHP_RCMND,
QTY_LN_LOAD_VHCL,
QTY_INV,
QTY_INV_BIN_PRDN
) VALUES
(dc_rec.loc,
dc_rec.item,
dc_rec.mrpctlr,
dc_rec.subgrp,
dc_rec.needdate,
dc_rec.descr,
dc_rec.srqty,
dc_rec.poqty,
dc_rec.rsqty,
dc_rec.vlqty,
null,
null
);
rec_cnt:=rec_cnt+1;
end loop;
close dc_insert;
OPEN updateinv_cur;
LOOP
FETCH updateinv_cur into updateinv_rec;
EXIT WHEN updateinv_cur%NOTFOUND;
IF updateinv_rec.store = 'PRODBININV' then
UPDATE GPSI.datecross_dts
SET QTY_INV_BIN_PRDN = updateinv_rec.qty
WHERE itm = updateinv_rec.item
AND loc = updateinv_rec.loc ;
ELSE
UPDATE GPSI.datecross_dts
SET qty_inv = updateinv_rec.qty
WHERE itm = updateinv_rec.item
AND loc = updateinv_rec.loc ;
END IF;
END LOOP;
CLOSE updateinv_cur;
msg:='NUMBER of Records Inserted = '||to_char(rec_cnt);
UTL_FILE.PUT_LINE (output_file, msg);
msg:='DATECROSS FINISHED ON '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss');
UTL_FILE.PUT_LINE (output_file, msg);
msg:='-----------------------------------------------';
UTL_FILE.PUT_LINE (output_file, msg);
UTL_FILE.FCLOSE(output_file);
EXCEPTION
WHEN NO_DATA_FOUND
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
WHEN UTL_FILE.INTERNAL_ERROR
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'INTERNAL ERROR');
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'INVALID FILE HANDLE');
WHEN UTL_FILE.INVALID_OPERATION
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'INVALID PATH');
WHEN UTL_FILE.READ_ERROR
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'READ ERROR');
WHEN UTL_FILE.WRITE_ERROR
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'WRITE ERROR');
WHEN VALUE_ERROR
THEN UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20003,'VALUE ERROR');
WHEN OTHERS
THEN msg := 'SqlCheckPoint check ' ||' '||'Error '|| ' sqlcode = ' || sqlcode || substr(sqlerrm,1,50);
UTL_FILE.PUT_LINE (output_file, msg);
UTL_FILE.FCLOSE (OUTPUT_FILE);
RAISE_APPLICATION_ERROR(-20002,TO_CHAR(SQLCODE)||SQLERRM);
commit;
END up_datecross;
and the table structure is
|
|
|
|
| Re: Urgent ! 2 lakh records in database [message #27447 is a reply to message #27412] |
Fri, 03 October 2003 05:28 |
AlanP
Messages: 4
Registered: October 2003
|
Junior Member |
|
|
1) Make your temp table a global temp table to reduce logging (or you can do create table as ... nologging avoiding using a cursor, just do a straight select).
You might want to index your temp table if you havent already. Also use truncate rather than delete if your not using a global temp table.
2) See if replacing in clauses with joins is more efficient (you might need to do a group by clause if your getting multiple rows back where you are expecting 1).
3) replace multiple statement with one if possible i.e your two update statements for updateinv_cur could be combined into one if you a decode statement.
Same goes for you IF dc_cur_rec.parentordertype = 2 THEN etc, turn them into one update statement
so IF dc_cur_rec.parentordertype in ( 2,4,5,6) etc.
You may find its possible to move it outside your cursor and just execute one update statement or set the columns to their right value in your insert statement using case or decode to calculate which columns to set to which value.
In fact you might be able to replace whole load of cursors and code with just simple sql statements which might be much faster.
4) Are your tables analyzed recently (assuming your using CBO not RBO) and are they indexed correctly?
5) Assign a large rollback segment for your procedure. i.e. create a large rollback segment, make sure its offline. When you proc starts use execute immediate to bring it online, and at the end take it offline.
Alan
|
|
|
|
Goto Forum:
Current Time: Wed Aug 20 07:09:01 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
