Home » RDBMS Server » Performance Tuning » Package Tuning using Bulk Excpetion (oracle10g)
Package Tuning using Bulk Excpetion [message #389626] Mon, 02 March 2009 23:25 Go to next message
Messages: 82
Registered: January 2009
Location: mumbai

1.How to tune this package?
2.I have handle excpetion in all BULK COLLECT queries how to handle these all things.
Check the code in the attached file.
  • Attachment: t1.sql
    (Size: 6.28KB, Downloaded 205 times)
Re: Package Tuning using Bulk Excpetion [message #389629 is a reply to message #389626] Mon, 02 March 2009 23:36 Go to previous messageGo to next message
Messages: 25546
Registered: January 2009
Location: SoCal
Senior Member
>How to tune this package?
Is this our job or yours?

First what evidence exists that it needs to be tuned?
Re: Package Tuning using Bulk Excpetion [message #389717 is a reply to message #389626] Tue, 03 March 2009 06:45 Go to previous message
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

Previous Topic: Package Tuning (merged 4)
Next Topic: Indexes (merged 3)
Goto Forum:

Current Time: Fri Jul 28 08:27:09 CDT 2017

Total time taken to generate the page: 0.10602 seconds