dba_shashi
asked on
Taking too much time Inserting bulk load in oracle.
Hi Experts
Please have a look at below script and help me load data faster. I have over 600 Million Records to be inserted
.
Many Thanks
CREATE OR REPLACE PROCEDURE GSDWRPTUSR.proc_ods_data_l oad (
rec_ret_code OUT NUMBER,
xreturn_msg OUT VARCHAR2
)
AS
TYPE rec_acct IS RECORD (
unique_acct_cd gsdwrptusr.stag_tab_ods_dl y.unique_a cct_cd%TYP E,
pos_date gsdwrptusr.stag_tab_ods_dl y.pos_date %TYPE
);
TYPE rec_accttype IS TABLE OF rec_acct;
c_rec_accttype rec_accttype;
--CURSOR FOR GETTING THE RECORDS FOR ACCT_HIST
CURSOR accthistload
IS
SELECT DISTINCT unique_acct_cd, MIN (pos_date)
FROM stag_tab_ods_dly
GROUP BY unique_acct_cd;
TYPE rec_issu IS RECORD (
unique_sec_cd gsdwrptusr.stag_tab_ods_dl y.unique_s ec_cd%TYPE ,
reg_cusip gsdwrptusr.stag_tab_ods_dl y.reg_cusi p%TYPE,
ticker gsdwrptusr.stag_tab_ods_dl y.ticker%T YPE,
sec_issue_nm gsdwrptusr.stag_tab_ods_dl y.sec_issu e_nm%TYPE,
gic_sector gsdwrptusr.stag_tab_ods_dl y.gic_sect or%TYPE,
gic_industry gsdwrptusr.stag_tab_ods_dl y.gic_indu stry%TYPE,
sec_type_cd gsdwrptusr.stag_tab_ods_dl y.sec_type _cd%TYPE,
mat_date gsdwrptusr.stag_tab_ods_dl y.mat_date %TYPE,
curr_cd gsdwrptusr.stag_tab_ods_dl y.curr_cd% TYPE,
price_multiplier gsdwrptusr.stag_tab_ods_dl y.price_mu ltiplier%T YPE,
country_cd gsdwrptusr.stag_tab_ods_dl y.country_ cd%TYPE,
coupon_rate gsdwrptusr.stag_tab_ods_dl y.coupon_r ate%TYPE,
shares_outstanding gsdwrptusr.stag_tab_ods_dl y.shares_o utstanding %TYPE,
inv_type_cd gsdwrptusr.stag_tab_ods_dl y.inv_type _cd%TYPE,
pos_date gsdwrptusr.stag_tab_ods_dl y.pos_date %TYPE
);
TYPE rec_issutype IS TABLE OF rec_issu;
c_rec_issutype rec_issutype;
--CURSOR FOR GETTING THE RECORDS FOR ISSU_HIST
CURSOR issuhistload
IS
SELECT DISTINCT unique_sec_cd, reg_cusip, ticker, sec_issue_nm,
gic_sector, gic_industry, sec_type_cd, mat_date,
curr_cd, price_multiplier, country_cd, coupon_rate,
shares_outstanding, inv_type_cd, MIN (pos_date)
pos_date
FROM gsdwrptusr.stag_tab_ods_dl y
GROUP BY unique_sec_cd,
reg_cusip,
ticker,
sec_issue_nm,
gic_sector,
gic_industry,
sec_type_cd,
mat_date,
curr_cd,
price_multiplier,
country_cd,
coupon_rate,
shares_outstanding,
inv_type_cd
ORDER BY pos_date;
--CURSOR FOR GETTING THE RECORDS FOR POSN_HIST
TYPE rec_posn IS RECORD (
sec_sok gsdwrptusr.issu_hist.sec_s ok%TYPE,
acct_sok gsdwrptusr.acct_hist.acct_ sok%TYPE,
pos_date gsdwrptusr.stag_tab_ods_dl y.pos_date %TYPE,
prc_base gsdwrptusr.stag_tab_ods_dl y.prc_base %TYPE,
prc_local gsdwrptusr.stag_tab_ods_dl y.prc_loca l%TYPE,
base_cost gsdwrptusr.stag_tab_ods_dl y.base_cos t%TYPE,
local_cost gsdwrptusr.stag_tab_ods_dl y.local_co st%TYPE,
share_par_value gsdwrptusr.stag_tab_ods_dl y.share_pa r_value%TY PE,
spot_rate gsdwrptusr.stag_tab_ods_dl y.spot_rat e%TYPE,
sec_issue_nm gsdwrptusr.stag_tab_ods_dl y.sec_issu e_nm%TYPE
);
TYPE rec_posntype IS TABLE OF rec_posn;
c_rec_posntype rec_posntype;
CURSOR posnhistload
IS
SELECT (SELECT sec_sok
FROM issu_hist
WHERE sec_id = c.unique_sec_cd AND ROWNUM = 1),
(SELECT acct_sok
FROM acct_hist
WHERE acct_id = c.unique_acct_cd AND ROWNUM = 1), c.pos_date,
c.prc_base, c.prc_local, c.base_cost, c.local_cost,
c.share_par_value, c.spot_rate, c.sec_issue_nm
FROM stag_tab_ods_dly c
ORDER BY c.pos_date;
exit_on_error EXCEPTION;
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
DECLARE
exit_on_error EXCEPTION;
BEGIN
rec_ret_code := 0;
--RAISE EXCEPTION IN CASE OF FAILURE
IF rec_ret_code != 0
THEN
RAISE exit_on_error;
END IF;
OPEN accthistload;
LOOP
FETCH accthistload
BULK COLLECT INTO c_rec_accttype LIMIT 100000;
DBMS_OUTPUT.put_line ( 'Iteration count for acct_hist -'
|| TO_CHAR (c_rec_accttype.COUNT)
);
FORALL i IN 1 .. c_rec_accttype.COUNT
INSERT /*+ parallel (6,1) */INTO acct_hist
(acct_id, acct_sok,
data_src_id,
version_start_tmsmp,
version_end_tmsmp,
crte_tstp,
crte_pgm
)
VALUES (c_rec_accttype (i).unique_acct_cd, acct_seq.NEXTVAL,
'O',
TO_TIMESTAMP (c_rec_accttype (i).pos_date,
'DD-MON-RR HH.MI.SS.FF AM'
),
TO_TIMESTAMP ('30-SEP-11 12.00.00.000000000 AM',
'DD-MON-RR HH.MI.SS.FF AM'
),
TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
'BATCH'
);
COMMIT;
EXIT WHEN accthistload%NOTFOUND;
END LOOP;
--RAISE EXCEPTION IN CASE OF FAILURE
IF rec_ret_code != 0
THEN
RAISE exit_on_error;
END IF;
OPEN issuhistload;
LOOP
FETCH issuhistload
BULK COLLECT INTO c_rec_issutype LIMIT 100000;
DBMS_OUTPUT.put_line ( 'Iteration count for issu_hist -'
|| TO_CHAR (c_rec_issutype.COUNT)
);
FORALL i IN 1 .. c_rec_issutype.COUNT
INSERT /*+ parallel (6,1) */INTO gsdwrptusr.issu_hist hist
(hist.sec_sok, hist.sec_id,
hist.data_src_id, hist.version_start_tmsmp,
hist.version_end_tmsmp,
hist.crte_tstp, hist.crte_pgm, hist.cusip_id,
hist.ticker_id,
hist.pref_iss_nme,
hist.gics_sector_cde,
hist.gics_indus_cde,
hist.iss_typ,
hist.mat_exp_tmsmp,
hist.denom_curr_cde,
hist.prc_mltplr_crte,
hist.issuance_cntry_nme,
hist.cpn_crte,
hist.shr_outst_cqty,
hist.inv_type_cde
)
VALUES (issu_seq.NEXTVAL, c_rec_issutype (i).unique_sec_cd,
'O', c_rec_issutype (i).pos_date,
TO_TIMESTAMP ('30-SEP-11 12.00.00',
'DD-MON-RR HH.MI.SS'
),
SYSDATE, 'BATCH', c_rec_issutype (i).reg_cusip,
c_rec_issutype (i).ticker,
c_rec_issutype (i).sec_issue_nm,
c_rec_issutype (i).gic_sector,
c_rec_issutype (i).gic_industry,
c_rec_issutype (i).sec_type_cd,
c_rec_issutype (i).mat_date,
c_rec_issutype (i).curr_cd,
c_rec_issutype (i).price_multiplier,
c_rec_issutype (i).country_cd,
c_rec_issutype (i).coupon_rate,
c_rec_issutype (i).shares_outstanding,
c_rec_issutype (i).inv_type_cd
);
COMMIT;
EXIT WHEN issuhistload%NOTFOUND;
END LOOP;
CLOSE issuhistload;
INSERT /*+ parallel (6,1) */INTO issu_temp_tab
(sec_id, sec_sok, version_start_tmsmp, version_end_tmsmpn)
SELECT DISTINCT b.sec_id, b.sec_sok, b.version_start_tmsmp,
(a.version_start_tmsmp - 1) version_end_tmsmp_new
FROM gsdwrptusr.issuview a, gsdwrptusr.issuview b
WHERE a.sec_id = b.sec_id
AND b.ranking = a.ranking + 1
AND a.ranking > 0
ORDER BY version_start_tmsmp DESC;
--INSERTING VALUES IN TO POSN_HIST TABLE
OPEN posnhistload;
LOOP
FETCH posnhistload
BULK COLLECT INTO c_rec_posntype LIMIT 100000;
DBMS_OUTPUT.put_line ( 'Iteration count for posn_hist -'
|| TO_CHAR (c_rec_posntype.COUNT)
);
FORALL i IN 1 .. c_rec_posntype.COUNT
INSERT INTO gsdwrptusr.posn_hist
(pos_sok, acct_sok,
sec_sok, data_src_id,
crte_tstp,
crte_pgm,
holding_dte,
bkpg_curr_book_cprc,
local_curr_book_cprc,
bkpg_unit_cost_camt,
local_unit_cost_camt,
qty_cqty,
spot_rate
)
VALUES (posn_seq.NEXTVAL, c_rec_posntype (i).acct_sok,
c_rec_posntype (i).sec_sok, 'O',
TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
'BATCH',
TO_TIMESTAMP (c_rec_posntype (i).pos_date,
'DD-MON-RR HH.MI.SS.FF AM'
),
c_rec_posntype (i).prc_base,
c_rec_posntype (i).prc_local,
c_rec_posntype (i).base_cost,
c_rec_posntype (i).local_cost,
c_rec_posntype (i).share_par_value,
c_rec_posntype (i).spot_rate
);
COMMIT;
EXIT WHEN posnhistload%NOTFOUND;
END LOOP;
CLOSE posnhistload;
proc_issu_update ();
--THIS HAS TO BE UN-COMMENTED POST TESTING THE DATA
--DELETE FROM ISSU_TEMP_TAB
--DELETE FROM STG_ACCT;
--CATCH THE RAISED EXCEPTION AND ROLLBACK THE TRANSACTION
--ALSO SHOW THE EXCEPTION MESSAGE.
EXCEPTION
WHEN exit_on_error OR NO_DATA_FOUND
THEN
BEGIN
ROLLBACK;
rec_ret_code := 1;
xreturn_msg := SUBSTR (SQLERRM, 1, 256);
END;
WHEN OTHERS
THEN
BEGIN
ROLLBACK;
rec_ret_code := 1;
xreturn_msg := SUBSTR (SQLERRM, 1, 256);
END;
END;
END;
/
Please have a look at below script and help me load data faster. I have over 600 Million Records to be inserted
.
Many Thanks
CREATE OR REPLACE PROCEDURE GSDWRPTUSR.proc_ods_data_l
rec_ret_code OUT NUMBER,
xreturn_msg OUT VARCHAR2
)
AS
TYPE rec_acct IS RECORD (
unique_acct_cd gsdwrptusr.stag_tab_ods_dl
pos_date gsdwrptusr.stag_tab_ods_dl
);
TYPE rec_accttype IS TABLE OF rec_acct;
c_rec_accttype rec_accttype;
--CURSOR FOR GETTING THE RECORDS FOR ACCT_HIST
CURSOR accthistload
IS
SELECT DISTINCT unique_acct_cd, MIN (pos_date)
FROM stag_tab_ods_dly
GROUP BY unique_acct_cd;
TYPE rec_issu IS RECORD (
unique_sec_cd gsdwrptusr.stag_tab_ods_dl
reg_cusip gsdwrptusr.stag_tab_ods_dl
ticker gsdwrptusr.stag_tab_ods_dl
sec_issue_nm gsdwrptusr.stag_tab_ods_dl
gic_sector gsdwrptusr.stag_tab_ods_dl
gic_industry gsdwrptusr.stag_tab_ods_dl
sec_type_cd gsdwrptusr.stag_tab_ods_dl
mat_date gsdwrptusr.stag_tab_ods_dl
curr_cd gsdwrptusr.stag_tab_ods_dl
price_multiplier gsdwrptusr.stag_tab_ods_dl
country_cd gsdwrptusr.stag_tab_ods_dl
coupon_rate gsdwrptusr.stag_tab_ods_dl
shares_outstanding gsdwrptusr.stag_tab_ods_dl
inv_type_cd gsdwrptusr.stag_tab_ods_dl
pos_date gsdwrptusr.stag_tab_ods_dl
);
TYPE rec_issutype IS TABLE OF rec_issu;
c_rec_issutype rec_issutype;
--CURSOR FOR GETTING THE RECORDS FOR ISSU_HIST
CURSOR issuhistload
IS
SELECT DISTINCT unique_sec_cd, reg_cusip, ticker, sec_issue_nm,
gic_sector, gic_industry, sec_type_cd, mat_date,
curr_cd, price_multiplier, country_cd, coupon_rate,
shares_outstanding, inv_type_cd, MIN (pos_date)
pos_date
FROM gsdwrptusr.stag_tab_ods_dl
GROUP BY unique_sec_cd,
reg_cusip,
ticker,
sec_issue_nm,
gic_sector,
gic_industry,
sec_type_cd,
mat_date,
curr_cd,
price_multiplier,
country_cd,
coupon_rate,
shares_outstanding,
inv_type_cd
ORDER BY pos_date;
--CURSOR FOR GETTING THE RECORDS FOR POSN_HIST
TYPE rec_posn IS RECORD (
sec_sok gsdwrptusr.issu_hist.sec_s
acct_sok gsdwrptusr.acct_hist.acct_
pos_date gsdwrptusr.stag_tab_ods_dl
prc_base gsdwrptusr.stag_tab_ods_dl
prc_local gsdwrptusr.stag_tab_ods_dl
base_cost gsdwrptusr.stag_tab_ods_dl
local_cost gsdwrptusr.stag_tab_ods_dl
share_par_value gsdwrptusr.stag_tab_ods_dl
spot_rate gsdwrptusr.stag_tab_ods_dl
sec_issue_nm gsdwrptusr.stag_tab_ods_dl
);
TYPE rec_posntype IS TABLE OF rec_posn;
c_rec_posntype rec_posntype;
CURSOR posnhistload
IS
SELECT (SELECT sec_sok
FROM issu_hist
WHERE sec_id = c.unique_sec_cd AND ROWNUM = 1),
(SELECT acct_sok
FROM acct_hist
WHERE acct_id = c.unique_acct_cd AND ROWNUM = 1), c.pos_date,
c.prc_base, c.prc_local, c.base_cost, c.local_cost,
c.share_par_value, c.spot_rate, c.sec_issue_nm
FROM stag_tab_ods_dly c
ORDER BY c.pos_date;
exit_on_error EXCEPTION;
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
DECLARE
exit_on_error EXCEPTION;
BEGIN
rec_ret_code := 0;
--RAISE EXCEPTION IN CASE OF FAILURE
IF rec_ret_code != 0
THEN
RAISE exit_on_error;
END IF;
OPEN accthistload;
LOOP
FETCH accthistload
BULK COLLECT INTO c_rec_accttype LIMIT 100000;
DBMS_OUTPUT.put_line ( 'Iteration count for acct_hist -'
|| TO_CHAR (c_rec_accttype.COUNT)
);
FORALL i IN 1 .. c_rec_accttype.COUNT
INSERT /*+ parallel (6,1) */INTO acct_hist
(acct_id, acct_sok,
data_src_id,
version_start_tmsmp,
version_end_tmsmp,
crte_tstp,
crte_pgm
)
VALUES (c_rec_accttype (i).unique_acct_cd, acct_seq.NEXTVAL,
'O',
TO_TIMESTAMP (c_rec_accttype (i).pos_date,
'DD-MON-RR HH.MI.SS.FF AM'
),
TO_TIMESTAMP ('30-SEP-11 12.00.00.000000000 AM',
'DD-MON-RR HH.MI.SS.FF AM'
),
TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
'BATCH'
);
COMMIT;
EXIT WHEN accthistload%NOTFOUND;
END LOOP;
--RAISE EXCEPTION IN CASE OF FAILURE
IF rec_ret_code != 0
THEN
RAISE exit_on_error;
END IF;
OPEN issuhistload;
LOOP
FETCH issuhistload
BULK COLLECT INTO c_rec_issutype LIMIT 100000;
DBMS_OUTPUT.put_line ( 'Iteration count for issu_hist -'
|| TO_CHAR (c_rec_issutype.COUNT)
);
FORALL i IN 1 .. c_rec_issutype.COUNT
INSERT /*+ parallel (6,1) */INTO gsdwrptusr.issu_hist hist
(hist.sec_sok, hist.sec_id,
hist.data_src_id, hist.version_start_tmsmp,
hist.version_end_tmsmp,
hist.crte_tstp, hist.crte_pgm, hist.cusip_id,
hist.ticker_id,
hist.pref_iss_nme,
hist.gics_sector_cde,
hist.gics_indus_cde,
hist.iss_typ,
hist.mat_exp_tmsmp,
hist.denom_curr_cde,
hist.prc_mltplr_crte,
hist.issuance_cntry_nme,
hist.cpn_crte,
hist.shr_outst_cqty,
hist.inv_type_cde
)
VALUES (issu_seq.NEXTVAL, c_rec_issutype (i).unique_sec_cd,
'O', c_rec_issutype (i).pos_date,
TO_TIMESTAMP ('30-SEP-11 12.00.00',
'DD-MON-RR HH.MI.SS'
),
SYSDATE, 'BATCH', c_rec_issutype (i).reg_cusip,
c_rec_issutype (i).ticker,
c_rec_issutype (i).sec_issue_nm,
c_rec_issutype (i).gic_sector,
c_rec_issutype (i).gic_industry,
c_rec_issutype (i).sec_type_cd,
c_rec_issutype (i).mat_date,
c_rec_issutype (i).curr_cd,
c_rec_issutype (i).price_multiplier,
c_rec_issutype (i).country_cd,
c_rec_issutype (i).coupon_rate,
c_rec_issutype (i).shares_outstanding,
c_rec_issutype (i).inv_type_cd
);
COMMIT;
EXIT WHEN issuhistload%NOTFOUND;
END LOOP;
CLOSE issuhistload;
INSERT /*+ parallel (6,1) */INTO issu_temp_tab
(sec_id, sec_sok, version_start_tmsmp, version_end_tmsmpn)
SELECT DISTINCT b.sec_id, b.sec_sok, b.version_start_tmsmp,
(a.version_start_tmsmp - 1) version_end_tmsmp_new
FROM gsdwrptusr.issuview a, gsdwrptusr.issuview b
WHERE a.sec_id = b.sec_id
AND b.ranking = a.ranking + 1
AND a.ranking > 0
ORDER BY version_start_tmsmp DESC;
--INSERTING VALUES IN TO POSN_HIST TABLE
OPEN posnhistload;
LOOP
FETCH posnhistload
BULK COLLECT INTO c_rec_posntype LIMIT 100000;
DBMS_OUTPUT.put_line ( 'Iteration count for posn_hist -'
|| TO_CHAR (c_rec_posntype.COUNT)
);
FORALL i IN 1 .. c_rec_posntype.COUNT
INSERT INTO gsdwrptusr.posn_hist
(pos_sok, acct_sok,
sec_sok, data_src_id,
crte_tstp,
crte_pgm,
holding_dte,
bkpg_curr_book_cprc,
local_curr_book_cprc,
bkpg_unit_cost_camt,
local_unit_cost_camt,
qty_cqty,
spot_rate
)
VALUES (posn_seq.NEXTVAL, c_rec_posntype (i).acct_sok,
c_rec_posntype (i).sec_sok, 'O',
TO_TIMESTAMP (SYSDATE, 'DD-MON-RR HH.MI.SS.FF AM'),
'BATCH',
TO_TIMESTAMP (c_rec_posntype (i).pos_date,
'DD-MON-RR HH.MI.SS.FF AM'
),
c_rec_posntype (i).prc_base,
c_rec_posntype (i).prc_local,
c_rec_posntype (i).base_cost,
c_rec_posntype (i).local_cost,
c_rec_posntype (i).share_par_value,
c_rec_posntype (i).spot_rate
);
COMMIT;
EXIT WHEN posnhistload%NOTFOUND;
END LOOP;
CLOSE posnhistload;
proc_issu_update ();
--THIS HAS TO BE UN-COMMENTED POST TESTING THE DATA
--DELETE FROM ISSU_TEMP_TAB
--DELETE FROM STG_ACCT;
--CATCH THE RAISED EXCEPTION AND ROLLBACK THE TRANSACTION
--ALSO SHOW THE EXCEPTION MESSAGE.
EXCEPTION
WHEN exit_on_error OR NO_DATA_FOUND
THEN
BEGIN
ROLLBACK;
rec_ret_code := 1;
xreturn_msg := SUBSTR (SQLERRM, 1, 256);
END;
WHEN OTHERS
THEN
BEGIN
ROLLBACK;
rec_ret_code := 1;
xreturn_msg := SUBSTR (SQLERRM, 1, 256);
END;
END;
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for these valuable suggestion.
But all the requirement above is necessity here and nothing should be changed. Please guide me if any modification can be done further?