Re: Performance issue in a function

Sujatha wrote:
> 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

Your cursors are SQL statements in their own right. Have you taken those SQL statements and tuned them to ensure that they are optimal?

Cheers,
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