Funtion Tuning [message #414987] |
Fri, 24 July 2009 05:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 1155 times)
|
|
|
Re: Funtion Tuning [message #415020 is a reply to message #414987] |
Fri, 24 July 2009 07:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|