Slow PL/SQL bulk bind & forall performance. Is this a bug ?

From: Michael Carter <michael.c.carter_at_au.unisys.com>
Date: 23 Oct 2002 17:22:14 -0700
Message-ID: <b296db09.0210231622.61eb6a0c@posting.google.com>

I've been driven half mad trying to figure out what is happening with the following code. I've stripped this code out from a package and trimmed it into a kind of demonstration of my problem so someone explain this:

I have two procedures proc_1 and proc_2 (which I will attach later in the thread)

proc_1 demonstrates the basic functionality I want to achieve.

1) Open cursor 
2) Loop 
3) Fetch data into nested tables (100 rows each time). 
4) Modify that fetched data inside the nested tables in some way. 
5) Write the modified data back to the original table. (100 rows each

time)
6) Go to the top of the loop and continue until no more data.

For some reason my proc_1 is really really slow. It seems that once I modify the data within the nested table my bulk update slows to a crawl. Most of this time seems to be taken up by physical reads btw.

proc_2 is basically proc1 without the nested table data modification. It basically:

1) Open cursor 
2) Loop 
3) Fetch data into nested tables (100 rows each time). 
5) Write the data from the nested_tables back to the original table.

(100 rows each time)
6) Go to the top of the loop and continue until no more data.

Can someone explain to me what is happening ? Have I missed something obvious.

Note: bi_tkt is a large table

Brgds
-Michael

--------------------------- proc_1

CREATE OR REPLACE PROCEDURE proc_1 (bill_period IN CHAR) IS

TYPE tkt_sales_nm_LIST IS TABLE OF bi_tkt.tkt_sales_nm%TYPE; 
TYPE tkt_tkt_seq_LIST IS TABLE OF bi_tkt.tkt_tkt_seq%TYPE; 
TYPE tkt_tkt_nm_LIST IS TABLE OF bi_tkt.tkt_tkt_nm%TYPE;

/* ... And so on ... one table for each column, 54 columns total */
TYPE ROWLIST IS TABLE OF VARCHAR2(100); CURSOR c1 IS SELECT /*+ PUSH_SUBQ */

tkt_sales_nm,tkt_tkt_seq,tkt_tkt_nm,tkt_bill_i,tkt_agt_ref,tkt_filler,tkt_agtc_c,tkt_agta_c,
tkt_agtn_c,tkt_agt_cd,tkt_air_c,tkt_trnc,tkt_cjcp,tkt_cur_typ,tkt_tdam,tkt_auto,tkt_dais,tkt_stdcmr,
tkt_stdcms,tkt_cms_r,tkt_cms_a,tkt_cais,tkt_ccis,tkt_cmfa,tkt_txca,tkt_txcc,tkt_bal,tkt_lrep_f,tkt_lrep_a,
tkt_bi_d,tkt_tour,tkt_nrid,tkt_nrid_done,tkt_del_f,tkt_tcin,tkt_carf,tkt_stat,tkt_cotp,tkt_ntfa,tkt_sprt,
tkt_spam,tkt_comment,tkt_aeba,tkt_toca,tkt_flag1,tkt_filler2,tkt_apbc,tkt_prds_late,mod_date,mod_usr_id,
tkt_markup,tkt_pxnm,tkt_doc_typ,tkt_txcm,rowid

FROM bi_tkt
WHERE tkt_bill_i = bill_period AND tkt_bi_d IS NULL AND tkt_cjcp = 'PRI' AND tkt_del_f IS NULL AND
EXISTS (SELECT 1 FROM bi_agtbal WHERE agtb_period_i = tkt_bill_i AND agtb_agt_ref = tkt_agt_ref
AND agtb_iata_i='Y' AND agtb_bill_d is null) ORDER BY tkt_agt_ref;

tkt_sales_nm_s tkt_sales_nm_LIST; 
tkt_tkt_seq_s tkt_tkt_seq_LIST; 
tkt_tkt_nm_s tkt_tkt_nm_LIST;

/* ... And so on ... one variable for each nested table */
ROWS NATURAL := 100;
RID ROWLIST;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT into

tkt_sales_nm_s,tkt_tkt_seq_s,tkt_tkt_nm_s,tkt_bill_i_s,tkt_agt_ref_s,tkt_filler_s,tkt_agtc_c_s,
tkt_agta_c_s,tkt_agtn_c_s,tkt_agt_cd_s,tkt_air_c_s,tkt_trnc_s,tkt_cjcp_s,tkt_cur_typ_s,tkt_tdam_s,
tkt_auto_s,tkt_dais_s,tkt_stdcmr_s,tkt_stdcms_s,tkt_cms_r_s,tkt_cms_a_s,tkt_cais_s,tkt_ccis_s,
tkt_cmfa_s,tkt_txca_s,tkt_txcc_s,tkt_bal_s,tkt_lrep_f_s,tkt_lrep_a_s,tkt_bi_d_s,tkt_tour_s,tkt_nrid_s,
tkt_nrid_done_s,tkt_del_f_s,tkt_tcin_s,tkt_carf_s,tkt_stat_s,tkt_cotp_s,tkt_ntfa_s,tkt_sprt_s,tkt_spam_s,
tkt_comment_s,tkt_aeba_s,tkt_toca_s,tkt_flag1_s,tkt_filler2_s,tkt_apbc_s,tkt_prds_late_s,mod_date_s,
mod_usr_id_s,tkt_markup_s,tkt_pxnm_s,tkt_doc_typ_s,tkt_txcm_s,RID

LIMIT rows;
FOR i IN RID.first .. RID.last LOOP
tkt_bi_d_s(i) := SYSDATE;
END LOOP;
INSERT INTO MICK_TAB values ('Begin bi_tkt bulk ' ||TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') ); COMMIT;
FORALL i in RID.first .. RID.last
UPDATE bi_tkt
SET tkt_cur_typ=tkt_cur_typ_s(i),tkt_tdam=tkt_tdam_s(i),tkt_auto=tkt_auto_s(i),tkt_stdcmr=tkt_stdcmr_s(i),

tkt_stdcms=tkt_stdcms_s(i),tkt_cms_r=tkt_cms_r_s(i),tkt_cms_a=tkt_cms_a_s(i),tkt_cais=tkt_cais_s(i),tkt_ccis=tkt_ccis_s(i),
tkt_cmfa=tkt_cmfa_s(i),tkt_txca=tkt_txca_s(i),tkt_txcc=tkt_txcc_s(i),tkt_bal=tkt_bal_s(i),tkt_lrep_f=tkt_lrep_f_s(i),
tkt_lrep_a=tkt_lrep_a_s(i),tkt_bi_d=tkt_bi_d_s(i),tkt_tour=tkt_tour_s(i),tkt_nrid=tkt_nrid_s(i),
tkt_carf=tkt_carf_s(i),tkt_stat=tkt_stat_s(i),tkt_cotp=tkt_cotp_s(i),tkt_ntfa=tkt_ntfa_s(i),tkt_sprt=tkt_sprt_s(i),
tkt_spam=tkt_spam_s(i),tkt_comment=tkt_comment_s(i),tkt_aeba=tkt_aeba_s(i),tkt_toca=tkt_toca_s(i),
tkt_filler2=tkt_filler2_s(i),tkt_apbc=tkt_apbc_s(i),tkt_prds_late=tkt_prds_late_s(i),mod_date=SYSDATE,mod_usr_id=SUBSTR(USER,5,6)

WHERE rowid = RID(i);
COMMIT;
INSERT INTO MICK_TAB values ('End bi_tkt bulk '||TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS')); COMMIT;
EXIT WHEN c1%NOTFOUND;

END LOOP; CLOSE c1;
COMMIT;
END proc_1;
/

Timings ...
Begin bi_tkt bulk 24-OCT-02 10:51:18
End bi_tkt bulk 24-OCT-02 10:55:46
Begin bi_tkt bulk 24-OCT-02 10:55:46
End bi_tkt bulk 24-OCT-02 10:59:55
Begin bi_tkt bulk 24-OCT-02 10:59:56
End bi_tkt bulk 24-OCT-02 11:01:18
Begin bi_tkt bulk 24-OCT-02 11:01:18
End bi_tkt bulk 24-OCT-02 11:03:17
Begin bi_tkt bulk 24-OCT-02 11:03:18
End bi_tkt bulk 24-OCT-02 11:05:04

proc_2 CREATE OR REPLACE PROCEDURE proc_2 (bill_period IN CHAR) IS TYPE tkt_sales_nm_LIST IS TABLE OF bi_tkt.tkt_sales_nm%TYPE; TYPE tkt_tkt_seq_LIST IS TABLE OF bi_tkt.tkt_tkt_seq%TYPE; TYPE tkt_tkt_nm_LIST IS TABLE OF bi_tkt.tkt_tkt_nm%TYPE;
/* ... And so on ... one table for each column, 54 columns total */
TYPE ROWLIST IS TABLE OF VARCHAR2(100); CURSOR c1 IS SELECT /*+ PUSH_SUBQ */ tkt_sales_nm,tkt_tkt_seq,tkt_tkt_nm,tkt_bill_i,tkt_agt_ref,tkt_filler,tkt_agtc_c,tkt_agta_c, tkt_agtn_c,tkt_agt_cd,tkt_air_c,tkt_trnc,tkt_cjcp,tkt_cur_typ,tkt_tdam,tkt_auto,tkt_dais,tkt_stdcmr, tkt_stdcms,tkt_cms_r,tkt_cms_a,tkt_cais,tkt_ccis,tkt_cmfa,tkt_txca,tkt_txcc,tkt_bal,tkt_lrep_f,tkt_lrep_a, tkt_bi_d,tkt_tour,tkt_nrid,tkt_nrid_done,tkt_del_f,tkt_tcin,tkt_carf,tkt_stat,tkt_cotp,tkt_ntfa,tkt_sprt, tkt_spam,tkt_comment,tkt_aeba,tkt_toca,tkt_flag1,tkt_filler2,tkt_apbc,tkt_prds_late,mod_date,mod_usr_id, tkt_markup,tkt_pxnm,tkt_doc_typ,tkt_txcm,rowid FROM bi_tkt WHERE tkt_bill_i = bill_period AND tkt_bi_d IS NULL AND tkt_cjcp = 'PRI' AND tkt_del_f IS NULL AND EXISTS (SELECT 1 FROM bi_agtbal WHERE agtb_period_i = tkt_bill_i AND agtb_agt_ref = tkt_agt_ref AND agtb_iata_i='Y' AND agtb_bill_d is null) ORDER BY tkt_agt_ref; tkt_sales_nm_s tkt_sales_nm_LIST; tkt_tkt_seq_s tkt_tkt_seq_LIST; tkt_tkt_nm_s tkt_tkt_nm_LIST;
/* ... And so on ... one variable for each nested table */
ROWS NATURAL := 100; RID ROWLIST;

BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT into

tkt_sales_nm_s,tkt_tkt_seq_s,tkt_tkt_nm_s,tkt_bill_i_s,tkt_agt_ref_s,tkt_filler_s,tkt_agtc_c_s,
tkt_agta_c_s,tkt_agtn_c_s,tkt_agt_cd_s,tkt_air_c_s,tkt_trnc_s,tkt_cjcp_s,tkt_cur_typ_s,tkt_tdam_s,
tkt_auto_s,tkt_dais_s,tkt_stdcmr_s,tkt_stdcms_s,tkt_cms_r_s,tkt_cms_a_s,tkt_cais_s,tkt_ccis_s,
tkt_cmfa_s,tkt_txca_s,tkt_txcc_s,tkt_bal_s,tkt_lrep_f_s,tkt_lrep_a_s,tkt_bi_d_s,tkt_tour_s,tkt_nrid_s,
tkt_nrid_done_s,tkt_del_f_s,tkt_tcin_s,tkt_carf_s,tkt_stat_s,tkt_cotp_s,tkt_ntfa_s,tkt_sprt_s,tkt_spam_s,
tkt_comment_s,tkt_aeba_s,tkt_toca_s,tkt_flag1_s,tkt_filler2_s,tkt_apbc_s,tkt_prds_late_s,mod_date_s,
mod_usr_id_s,tkt_markup_s,tkt_pxnm_s,tkt_doc_typ_s,tkt_txcm_s,RID

LIMIT rows;
INSERT INTO MICK_TAB values ('Begin bi_tkt bulk ' ||TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') ); COMMIT;
FORALL i in RID.first .. RID.last
UPDATE bi_tkt
SET tkt_cur_typ=tkt_cur_typ_s(i),tkt_tdam=tkt_tdam_s(i),tkt_auto=tkt_auto_s(i),tkt_stdcmr=tkt_stdcmr_s(i),

tkt_stdcms=tkt_stdcms_s(i),tkt_cms_r=tkt_cms_r_s(i),tkt_cms_a=tkt_cms_a_s(i),tkt_cais=tkt_cais_s(i),tkt_ccis=tkt_ccis_s(i),
tkt_cmfa=tkt_cmfa_s(i),tkt_txca=tkt_txca_s(i),tkt_txcc=tkt_txcc_s(i),tkt_bal=tkt_bal_s(i),tkt_lrep_f=tkt_lrep_f_s(i),
tkt_lrep_a=tkt_lrep_a_s(i),tkt_bi_d=tkt_bi_d_s(i),tkt_tour=tkt_tour_s(i),tkt_nrid=tkt_nrid_s(i),
tkt_carf=tkt_carf_s(i),tkt_stat=tkt_stat_s(i),tkt_cotp=tkt_cotp_s(i),tkt_ntfa=tkt_ntfa_s(i),tkt_sprt=tkt_sprt_s(i),
tkt_spam=tkt_spam_s(i),tkt_comment=tkt_comment_s(i),tkt_aeba=tkt_aeba_s(i),tkt_toca=tkt_toca_s(i),
tkt_filler2=tkt_filler2_s(i),tkt_apbc=tkt_apbc_s(i),tkt_prds_late=tkt_prds_late_s(i),mod_date=SYSDATE,mod_usr_id=SUBSTR(USER,5,6)

WHERE rowid = RID(i);
COMMIT;
INSERT INTO MICK_TAB values ('End bi_tkt bulk '||TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS')); COMMIT;
EXIT WHEN c1%NOTFOUND;

END LOOP; CLOSE c1;
COMMIT;
END proc_2;
/

Timings ...
Begin bi_tkt bulk 24-OCT-02 10:46:45
End bi_tkt bulk 24-OCT-02 10:46:45
Begin bi_tkt bulk 24-OCT-02 10:46:45
End bi_tkt bulk 24-OCT-02 10:46:45
Begin bi_tkt bulk 24-OCT-02 10:46:45
End bi_tkt bulk 24-OCT-02 10:46:45
Begin bi_tkt bulk 24-OCT-02 10:46:45
End bi_tkt bulk 24-OCT-02 10:46:45
Begin bi_tkt bulk 24-OCT-02 10:46:45
End bi_tkt bulk 24-OCT-02 10:46:45 Received on Wed Oct 23 2002 - 19:22:14 CDT