Home » SQL & PL/SQL » SQL & PL/SQL » How to improve the performance of this SP? (ORACLE Version 9.2.0.1.0 - Production,32-bit Windows)
How to improve the performance of this SP? [message #443330] Mon, 15 February 2010 03:00 Go to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

I have attached a SP which does the following operation.Fetching records from ACANCEL,Processing those records and Inserting records into F_ENH_Audit_Trail.I have two indexes one for counter (as primary key), (CN_SEQU,counter) as indexes.It tooks more time when the total number of records are in millions.Let me know your valuable suggestions to improve its performance.

Thank you very much in advance.

Please let me know if i missed out any of the forum guidelines.

Baulraj.V
Re: How to improve the performance of this SP? [message #443348 is a reply to message #443330] Mon, 15 February 2010 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well it looks like your doing simple auditing, in which case I would personally drop this procedure and use oracles built in auditing instead.
The only way you're going to speed this up noticeably is to replace all the code with a single insert as select - which may or may not be possible.
Certainly looping through each row, doing all those checks and then an insert is never going to be fast.

Some general comments on the code:
1) those variables should be typed to the columns you're selecting the data from, otherwise if someone changes one of the column definitions that code is going to break horribly.
2) The exception handler at the end is a bug, remove it. With that there you'll only know something went wrong if you're running it from a tool that displays dbms_output and even then what happens if the error isn't no_data_found?
3) Don't use while loops to loop over cursors - use for loops, they're easier to code and maintain.
4) this:
IF cn_desc_v IS NULL THEN
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_desc_n
                         ||Chr(164);
      ELSE
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_desc_n
                         ||Chr(164)
                         ||cn_desc_v;
      END IF;
Is equivalent to this:
        lv_audit_text := lv_audit_text
                         ||Chr(164)
                         ||cn_desc_n
                         ||Chr(164)
                         ||cn_desc_v;
A lot of your if statements are not necessary.
5) The update of f_sequ should be done once after the loop not for each row as it is at the moment - moving that should increase the performance a bit.
Re: How to improve the performance of this SP? [message #443354 is a reply to message #443348] Mon, 15 February 2010 04:44 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,
Thank you very much for your detailed reply.

I will look into the unwanted if statements part and will remove the same.Can you please answer the following questions as i am new to this cursor stuff in ORACLE.We have READ ONLY option in SQL while declaring cursor which would avoid unnecessary locking, is there any options available in ORACLE?. Anyway I will try to redefine the SP as per your suggestions and test again.

Many Thanks Again.

Regards
Baulraj.V

Re: How to improve the performance of this SP? [message #443358 is a reply to message #443354] Mon, 15 February 2010 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
baulraj wrote on Mon, 15 February 2010 10:44
We have READ ONLY option in SQL while declaring cursor which would avoid unnecessary locking, is there any options available in ORACLE?.

I assume by SQL you mean SQLserver?

Selects don't cause locks in oracle so that option doesn't exist as there is no need for it.
Re: How to improve the performance of this SP? [message #443362 is a reply to message #443358] Mon, 15 February 2010 05:25 Go to previous message
baulraj
Messages: 48
Registered: January 2009
Member
Yes. In my previous update SQL means SQL Server.Hence i don't have to worry about locks.

Thank you.
Previous Topic: without using trunc how will you write this query excluding time
Next Topic: check user permissions
Goto Forum:
  


Current Time: Wed Dec 07 04:38:46 CST 2016

Total time taken to generate the page: 0.09325 seconds