Re: HELP needed: PL/SQL Script extremely slow Toadsoft can help

Try putting your query thru T.O.A.D it'll tell you which parts of it are expensive and you can re-structure them.

T.O.A.D is available as *shareware* from www.toadsoft.com Its a great program. It helped me take query from a cost of >650k to 130.

Joe B>

Chor Lip Goh wrote:

> 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 27 1998 - 11:07:19 CST