|
|
|
|
| Re: Package Tuning using Bulk Excpetion [message #389717 is a reply to message #389626] |
Tue, 03 March 2009 06:45  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Where do I start?
1) Your exception handling is a disaster waiting to happen.
You have When Others traps that supress the raising of an exception and pass the error message back to the calling code, which then cheerfully ignores the error.
2) Your f_evalcase code is baroque - It looks like it's looping through a cursor, except it does a RETURN as soon as it's processed the first row, and regardless of whether or not it managed to get a value, it sets lv_return to 'F' and ln_worklistid to null.
3) p_worklist_definition makes the rest of the code look sane and well maintained.
i) You populate a table with a set of ids
ii) You loop through this table from first to last (calling f_evalcase, and performing lots of db reads)
iii) You then step through the first i rows of the table, performing deletes.
This means that you repeatedly try to delete the same rows from lct_worklist_employee again and again.
You've got a NOLOGGING clause in the delete despite the fact that it's meaningless in that context. This is a good thing, as otherwise you'd render your data immune to anything except an old fashioned cold backup : no point in time recovery or application of redo-logs for you - these changes simply wouldn't be in the log files.
After doing all these deletes, you call f_ins_worklist, and then set pv_out_msg to whatever it returns, ignoring whatever was in it before - so the return status of this procedure is actually 'Did the last call to this function error'
To be honest, I'd set fire to this code and start again.
If I had to tune it I would:
1) extract the functionality from f_evalcase and include it in the driving SQL in p_worklist_definition
2) delete f_evalcase and f_ins_worklist
3) In p_worklist_definition, have a single delete statement based on the current driving SQL that would delete records from Lct_worklist_employee, and follow this up with a single SQL (also based on the current driving sql) that would insert the new records. I would use the EXCEPTIONS INTO clause of the Insert syntax to handle any exceptions.
[correct typos and grammar]
[Updated on: Tue, 03 March 2009 06:47] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
