Re: Performance issue in a function

Hi Brian Peasland,

Thanks for your analysis. Your analysis is right.The function uses 4 cursors and it has a parameter value. When i pass the parameter in the function(get_dm_amount) say for example get_dm_amount(debit memo number), and for that debit memo number, it has to first find what is the type and based on that value it determines which cursor is right and then calls the cursor and return the value. For 2500 records, if it has to do this, it will definetly take time. But i dont know how to tune the function since it has cursors..I thought of creating a table for the function but dont have any idea of how to? Can you suggests something?

Thanks
Regards
Sujatha.k

Brian Peasland wrote:
> Sujatha wrote:
> > Hi,
> >
> > I have a main view from where all the columns are displayed at front
> > end.
> > This view has 6 subqueries in it, of which in one of the view, if i
> > comment a function to get an amount based on a input value, it brings
> > records in 10-15 secs. If i dont comment, then it takes about 2 mins.
> >
> > The same function i have used in other views but only in this view it
> > is taking a long time. This view retrieves more than 2500 records but
> > the others has very few records(say about 10-15 records at the max.)
> >
> > Can anybody give me any suggestions to solve the problem?
> >
> > Note:
> > I have used cost based optimizer. All my tables are analyzed and have
> > statistics collected.
> >
> > Thanks
> > Regards
> > Sujatha.k
> >
>
> Sounds like a classic case where tuning is needed. If the view returns
> 10-15 records, the function lets results be returned in 10-15 seconds.
> Yet if the view returns 2,500 records, the run time approaches 2
> minutes. There are two things to think about here....One, tune the
> underlying SQL of the view. Two, tune the function. Your function likely
> contains queries within the function. Have you tuned those queries in
> the function? Have you looked to ensure that your PL/SQL logic in the
> function is not detrimental to the performance of the function? It
> sounds like most of your returns will be gained by tuning the function.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
Received on Wed Jul 12 2006 - 13:15:38 CDT