Home » RDBMS Server » Performance Tuning » Procedure tunning
Procedure tunning [message #138723] Fri, 23 September 2005 03:56 Go to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

I just want to know how to tune procedure containing lot of sql in it.
This procedure is taking nearly 10hr to complete the execution but the data on which it is working are millions of record.

I dont have an idea about procedure tunning .... i know i can go through the performance tunning guide of Performance Tunning but this requirement is very urgent .... and i have not tuned Procedure so i dont know from where to start...

Can someone give me breify idea about Procedure tunning i will be thankfull to him/her....

Note:-[1] You can get that procedure in the attachement.
[2] I know the procedure is to lengthy so i am not expecting Tunning of this procedure but expecting how to think, what are the way and steps for tunning any procedure.

Thank You

Always Friend Sunilkumar
Re: Procedure tunning [message #138750 is a reply to message #138723] Fri, 23 September 2005 07:41 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
In short, tuning plsql that contains lots of sql statements boils down to just tuning the individual sql statements.

Also try to do as much as possible in your sql statements, rather than using multiplle cursors and looping through them one row at a time going back and forth (context switching) between plsql and sql.

Just do one large sql statement. Then another. Then another.

If you do need to process the data a row at a time in plsql then use the bulk collect feature to operate on chunks of rows. Bulk them into a collection, then loop through that array, process, then bulk collect some more.

I don't have time to go through the entire attachment, but from a couple minutes of looking it seems like there are lots of tiny sql statements that could be combined into a single statement. Like at the top there is a select statement followed by a separate insert statement. Looks like that could be turned into an insert into select from statement.

The guideline to follow is:

First, do it in a single sql statement.

If absolutely not possible (any many things are now with analytics and 9i and 10g sql advancements) do it in multiple sql statements.

Only as a last result, use plsql cursors and looping constructs, but do those in bulk whenever possible.
Re: Procedure tunning [message #138764 is a reply to message #138750] Fri, 23 September 2005 08:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just to add what Martin has said,
[mag@kalyani mag]$ wc -l Proce*
3131 Procedureneed tunning.txt
[mag@kalyani mag]$ cat Procedure* | grep COMMIT | wc -l
148

There atleast 150 COMMIT/commit/other variations of commit statments inside one stored procedure (~5% of your code?).
Many times following an execute immediate ('some DDL');
Is it really nessary?
Seems the procedure is doing lots of selects and inserts and DMLS.
If the volume of data chaanged is significant, you may want to gather the statistics within the procedure.
And i am amused to see
SELECT /*+ RULE */

a couple of times.
[mag@kalyani mag]$ cat Procedure* | grep "FROM briouser.updatelog;" |wc -l
55

THe procedure queries the same table atleast 55 times.
In many cases explicit cursors are used. You may want to simplify some operations as Martin has said (use sql as possible).


Re: Procedure tunning [message #138772 is a reply to message #138723] Fri, 23 September 2005 08:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Nice followup Mahesh. Wow, 150 commits. I doubt there are truly that many business transactions in there.
Re: Procedure tunning [message #138968 is a reply to message #138772] Mon, 26 September 2005 01:57 Go to previous message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi..

Thanks allot smartin & mahesh for your valuable information on tunning of procedure for better performance of database.

Thanks & Regards
Always Friend Sunilkumar
Previous Topic: Solaris 9, 10g hard parse/soft parse what does it mean?
Next Topic: Poor Performance in Accessing Remote Database
Goto Forum:
  


Current Time: Thu Mar 28 09:14:21 CDT 2024