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

Michael

Two obvious candidates - Row chaining, indexes.

Are any of the columns that you change in proc_1 indexed ? This would require than changes are made to those indexes. This takes additional time.

Any changes you make will have an impact on the data blocks and therefore on the amount of redo generated. Writing different data back (proc_1) to a block causes more "disruption" than writing the same values back (proc_2) I believe. If you're increasing the amount of space required for rows, for example by increasing lengths the values of fields of type varchar2, you could also be causing row chaining. You can use analyze table <name> list chained rows [into <other table>]. If there are lots of rows chained from proc_1 then you could resolve this by rebuilding the table with a larger pctfree (the default is 10% and you could try 20% or more).

Andy

"Michael Carter" <michael.c.carter_at_au.unisys.com> wrote in message news:b296db09.0210231622.61eb6a0c_at_posting.google.com...
> Hi
>
> 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_ag tc_c,tkt_agta_c,
>

tkt_agtn_c,tkt_agt_cd,tkt_air_c,tkt_trnc,tkt_cjcp,tkt_cur_typ,tkt_tdam,tkt_a uto,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_sta t,tkt_cotp,tkt_ntfa,tkt_sprt,
>

tkt_spam,tkt_comment,tkt_aeba,tkt_toca,tkt_flag1,tkt_filler2,tkt_apbc,tkt_pr ds_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_fil ler_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),t kt_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_ntf a=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),t kt_toca=tkt_toca_s(i),
>

tkt_filler2=tkt_filler2_s(i),tkt_apbc=tkt_apbc_s(i),tkt_prds_late=tkt_prds_l ate_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_ag tc_c,tkt_agta_c,
>

tkt_agtn_c,tkt_agt_cd,tkt_air_c,tkt_trnc,tkt_cjcp,tkt_cur_typ,tkt_tdam,tkt_a uto,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_sta t,tkt_cotp,tkt_ntfa,tkt_sprt,
>

tkt_spam,tkt_comment,tkt_aeba,tkt_toca,tkt_flag1,tkt_filler2,tkt_apbc,tkt_pr ds_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_fil ler_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),t kt_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_ntf a=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),t kt_toca=tkt_toca_s(i),
>

tkt_filler2=tkt_filler2_s(i),tkt_apbc=tkt_apbc_s(i),tkt_prds_late=tkt_prds_l ate_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 Thu Oct 24 2002 - 05:30:22 CDT