Re: Possible Tune-up?

Adley wrote:
> Peace all,
>
> This is actually my friend's question. He is the developer here. The
> main problem is the PL/SQL script written below. It will be very slow
> when trying to fetch a lot of rows, in the range of thousands.
>
> The Oracle version is still 8.1.7 on AIX 4.3.
>
> ***********************
> SET SERVEROUTPUT ON SIZE 1000000;
>
> DECLARE
> v_PREQ_STK_CODE_221 msf221.PREQ_STK_CODE%TYPE;
> v_PREQ_STK_CODE_OLD msf221.PREQ_STK_CODE%TYPE;
> v_po_no_221 msf221.po_no%TYPE;
> v_po_item_no_221 msf221.po_item_no%TYPE;
> v_po_no_231 msf231.po_no%TYPE;
> v_po_item_no_231 msf231.po_item_no%TYPE;
> v_preq_no_231 msf231.preq_no%TYPE;
> v_preq_item_no_231 msf231.preq_item_no%TYPE;
> v_count1 NUMBER(6);
>
> CURSOR c_po_no_231 IS
> SELECT b.PREQ_STK_CODE, b.po_no, b.po_item_no,
> a.po_no, a.po_item_no,
> a.preq_no, a.preq_item_no
> FROM msf231 a,msf221 b
> WHERE (a.po_no <> b.po_no OR a.po_item_no <> b.po_item_no)
> AND b.status_221 <> '3'
> AND b.PO_ITEM_TYPE = 'P'
> AND a.PURCHASE_REQ_3 = b.PREQ_STK_CODE
> AND a.dstrct_code = b.dstrct_code
> AND a.dstrct_code = '0460'
> ORDER BY a.PURCHASE_REQ_3;
>
> BEGIN
> DBMS_OUTPUT.PUT_LINE('TEST1');
>
> v_PREQ_STK_CODE_OLD := ' ';
>
> OPEN c_po_no_231;
> DBMS_OUTPUT.PUT_LINE('TEST2');
>
> LOOP
> FETCH c_po_no_231
> INTO v_PREQ_STK_CODE_221, v_po_no_221, v_po_item_no_221,
> v_po_no_231, v_po_item_no_231,
> v_preq_no_231, v_preq_item_no_231;
>
> IF c_po_no_231%FOUND THEN
> IF (v_PREQ_STK_CODE_221 <> v_PREQ_STK_CODE_OLD) THEN
> v_PREQ_STK_CODE_OLD := v_PREQ_STK_CODE_221;
>
> DBMS_OUTPUT.PUT_LINE('TEST');
>
> SELECT COUNT (*) INTO v_count1
> FROM msf221
> WHERE PREQ_STK_CODE = v_PREQ_STK_CODE_221
> AND PO_ITEM_TYPE = 'P'
> AND dstrct_code = '0460'
> AND status_221 <> '3';
>
> IF (v_count1 = 1) THEN
> UPDATE msf231
> SET po_no = v_po_no_221,
> po_item_no = v_po_item_no_221
> WHERE dstrct_code = '0460'
> AND preq_no = v_preq_no_231
> AND preq_item_no = v_preq_item_no_231;
> ELSE
> DBMS_OUTPUT.PUT_LINE('DUPLICATE PO Items for PR Item No: '
> ||
> v_PREQ_STK_CODE_221 || ': ' ||
> v_count1);
> END IF;
> END IF;
> END IF;
>
> EXIT WHEN c_po_no_231%NOTFOUND;
>
> END LOOP;
>
> CLOSE c_po_no_231;
> END;
> ***************************************
>
> The tables in concern are as follows:
>
> SQL> desc ellipse.msf221
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> PO_NO NOT NULL CHAR(6)
> PO_ITEM_NO NOT NULL CHAR(3)
> ...
> PREQ_STK_CODE NOT NULL CHAR(9)
> ...
>
> SQL> desc ellipse.msf231
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> DSTRCT_CODE NOT NULL CHAR(4)
> PREQ_NO NOT NULL CHAR(6)
> PREQ_ITEM_NO NOT NULL CHAR(3)
> ...
> PO_NO NOT NULL CHAR(6)
> PO_ITEM_NO NOT NULL CHAR(3)
> ...
>
> *************************
>
> Here's a bit of index info:
> INDEX_NAME TABLE_NAME COLUMN_NAME
> COLUMN_POSITION COLUMN_LENGTH
> --------------- ---------- -----------------------------------
> --------------- -------------
> MSF22101IX0 MSF221 PO_NO
> 1 6
> MSF22101IX0 MSF221 PO_ITEM_NO
> 2 3
> MSF22101IX1 MSF221 RECEIPT_STATUS
> 1 1
> MSF22101IX1 MSF221 CURR_DUE_DATE
> 2 8
> MSF22101IX1 MSF221 PO_NO
> 3 6
> MSF22101IX2 MSF221 PREQ_STK_CODE
> 1 9
> MSF22101IX2 MSF221 REVSD_CRT_DATE
> 2 8
> MSF22101IX2 MSF221 PO_NO
> 3 6
> MSF22101IX2 MSF221 PO_ITEM_NO
> 4 3
> MSF22101IX3 MSF221 DSTRCT_CODE
> 1 4
> MSF22101IX3 MSF221 RECEIPT_STATUS
> 2 1
> MSF22101IX3 MSF221 EXPEDITE_DATE
> 3 8
> MSF22101IX3 MSF221 PO_NO
> 4 6
> MSF22101IX4 MSF221 STATUS_221
> 1 1
> MSF22101IX4 MSF221 DSTRCT_CODE
> 2 4
> MSF22101IX4 MSF221 PO_NO
> 3 6
> MSF22101IX4 MSF221 PO_ITEM_NO
> 4 3
> MSF23101IX0 MSF231 DSTRCT_CODE
> 1 4
> MSF23101IX0 MSF231 PREQ_NO
> 2 6
> MSF23101IX0 MSF231 PREQ_ITEM_NO
> 3 3
> MSF23101IX1 MSF231 DSTRCT_CODE
> 1 4
> MSF23101IX1 MSF231 STATUS_231
> 2 1
> MSF23101IX1 MSF231 SUPPLIER_NO
> 3 6
> MSF23101IX1 MSF231 PURCH_OFFICER
> 4 10
> MSF23101IX1 MSF231 FRT_DLV_RUN_NO
> 5 19
> MSF23101IX2 MSF231 DSTRCT_CODE
> 1 4
> MSF23101IX2 MSF231 PRINT_RUN_NO_2
> 2 4
> MSF23101IX2 MSF231 STATUS_231
> 3 1
> MSF23101IX2 MSF231 PO_TAG_SUP
> 4 18
> MSF23101IX2 MSF231 PURCHASE_REQ_3
> 5 9
> MSF23101IX3 MSF231 STATUS_231
> 1 1
> MSF23101IX3 MSF231 PO_TAG_SUP
> 2 18
> MSF23101IX3 MSF231 PURCHASE_REQ_3
> 3 9
>
> *************************
>
> SQL> sho parameter size
>
> NAME TYPE VALUE
> ------------------------------------ -------
> ------------------------------
> bitmap_merge_area_size integer 1048576
> create_bitmap_area_size integer 8388608
> db_block_size integer 8192
> hash_area_size integer 131072
> java_max_sessionspace_size integer 0
> java_pool_size string 20000K
> large_pool_size string 0
> max_dump_file_size string 10240
> object_cache_max_size_percent integer 10
> object_cache_optimal_size integer 102400
> oracle_trace_collection_size integer 5242880
> parallel_execution_message_size integer 2148
> shared_pool_reserved_size string 1280000
> shared_pool_size string 25600000
> sort_area_retained_size integer 0
> sort_area_size integer 65536
>
> *********************
>
> Is there something wrong with the PL/SQL script above?
>
> Thanks,
> =adley=

As others have said ... always do in one SQL statement what you can.

But assuming you could not ... there is essentially no valid reason to ever use a cursor loop in Oracle unless you have a Neolithic version. Move away from single-row processing and discover the power of BULK COLLECT and FORALL array processing.

Demos in Morgan's Library at www.psoug.org under ARRAY PROCESSING.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org