Home » RDBMS Server » Performance Tuning » Funtion Tuning (Oracle 9204, XP)
Funtion Tuning [message #414987] Fri, 24 July 2009 05:36 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi Experts,

I have a Query that uses an In Built Function..
I had applied a patch of Code to that function.
Now everytime i run the Query it takes longer time than usual...

I have checked with the indexes..
they are working fine..

What all could be the causes for this..

Do i need to check with the
1)Initialization parameters,
2)How to calculate the SORT_AREA_SIZE required for the instance..


i am new to Performance Tuning..
Need guidance as to where to start investigating the problem..

I am attaching the "Function.SQL" file that has the code..
  • Attachment: Funtion.sql
    (Size: 4.22KB, Downloaded 145 times)
Re: Funtion Tuning [message #415020 is a reply to message #414987] Fri, 24 July 2009 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have a query that calls that function?
You're a braver man than me.

DO not mess about with any paraeters - the cause of your query running slowly is that horrendous piece of SQL inside the function - you're running that query for every single row that your query returns.

You need to tune that query before you try anything else.

One thing I noticed was that you run subqueries of this form a lot in the query:
SELECT 1 FROM TBLRESMGMT WHERE PREVIDKEY = res.IDKEY AND TCOL5 IN ('EarMarked','Removed EarMarking')
. If the range of values in PREVIDKEY is fairly small, you might want to try loading all the values of TBLRESMGMT into a pl/sql table, and then just check to see that there is a value for that ID.
Re: Funtion Tuning [message #415042 is a reply to message #415020] Fri, 24 July 2009 08:27 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thanks JROWBOTTOM,

The Range for column is PREVIDKEY is 0 - 84604.
Should i go for a PLSQL TABLE..

Are Corsors an option for this..
Please Suggest how to go about it

Re: Funtion Tuning [message #415148 is a reply to message #415042] Sat, 25 July 2009 06:23 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Posting TKPROF report may be helpful.

Previous Topic: Index with OR predicates
Next Topic: Response time is high in 10g
Goto Forum:
  


Current Time: Mon Dec 05 10:49:10 CST 2016

Total time taken to generate the page: 0.09473 seconds