I suspect the hints that you are specifying in your query are NOT working.
Look at your sql closely.
Your supposed to leave a space after the '/*+ ' portion.
Also you EXPLAIN PLAN to determine if there are any full table scans.
Sylvain Faust wrote in message ...
>See what PC Week thinks the best SQL integrated development environment
>is:
>http://www.zdnet.com/pcweek/stories/news/0,4153,363685,00.html
>
>
>> -----Original Message-----
>> From: Joe Bonner [SMTP:jbonner_at_umich.edu]
>> Posted At: Tuesday, October 27, 1998 12:07 PM
>> Posted To: server
>> Conversation: HELP needed: PL/SQL Script extremely slow Toadsoft can
>> help
>> Subject: 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 Mon Nov 02 1998 - 06:17:01 CST
