| Funtion Tuning [message #414987] |
Fri, 24 July 2009 05:36  |
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 1187 times)
|
|
|
|
| Re: Funtion Tuning [message #415020 is a reply to message #414987] |
Fri, 24 July 2009 07:35  |
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')
|
|
|
|
| Re: Funtion Tuning [message #415042 is a reply to message #415020] |
Fri, 24 July 2009 08:27 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
