HELP needed: PL/SQL Script extremely slow

From: Chor Lip Goh <clgoh_at_cybernet.com.sg>
Date: Tue, 13 Oct 1998 17:40:05 +0800
Message-ID: <36231FF4.1ED0D73A@cybernet.com.sg>

Hello Oracle experts out there:

I've written a PL/SQL script which contains a select into statement which looks something like this

    Select /*+index (orders idx_p_ord_4)*/

/*+index (reply idx_p_rpy)*/
/*+index (part_master idx_p_prt_2*/

            sum(replied_qty)
            into var_rpy_qty

    from orders o, reply r, part_master p     where o.vendor_code=rec.vendor_code and

              o.cust_code=rec.cust_code and
              o.po_no=r.po_no and
              o.po_line_no=r.po_line_no and
              o.part_no=p.part_no and
              o.item_class=rec.item_class and
              o.item_type=rec.item_type and
              substr(o.po_send_date,1,6)=rec.po_send_mth;

I'm trying to calculate the total quantity of goods replied for a particular month using the above select statement.

Note:
1) rec is a cursor which I'll select will provide the respective values like customer, vendor, part no. and so on. 2) the table size is arranged in order of the no. of records it contained - orders has about 300K records, reply about 150K, part_master about 100K.
3) I need to explicitly instruct the use of indexes 'cos it makes a lot of difference in retrieval time.

The funny thing is, my PL/SQL script seemed to take a very long time just to process one record ( something like an hour !! )

I've tried isolating the problem by simply selecting the cursor values without the above select statement and the PL/SQL script just zoomed to completion. But when I add the above SQL statement into the same PL/SQL script, it began to crawl again!

I've also tried hardcoding the values in the Where clause of the SQL statement but it doesn't seem to help.

And the puzzling thing is - when I put in values and run the above SQL statement in SQL*Plus, it takes less than a second to retrieve the results.

I'm at the end of my wits trying to solve this problem, does anyone has a clue about what's wrong ?

Any help will be greatly appreciated ..

Thanks in advance. Received on Tue Oct 13 1998 - 04:40:05 CDT