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 Fri Oct 30 1998 - 17:36:28 CST
