Re: Performance issue in a function

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