OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Procedure tunning

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Procedure tunning [message #138723]

Fri, 23 September 2005 03:56

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

Attachment: Procedureneed tunning.txt
(Size: 129.16KB, Downloaded 2390 times)

Report message to a moderator

Re: Procedure tunning [message #138750 is a reply to message #138723]

Fri, 23 September 2005 07:41

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.

Report message to a moderator

Re: Procedure tunning [message #138764 is a reply to message #138750]

Fri, 23 September 2005 08:24

Mahesh Rajendran
Messages: 10708
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).

Report message to a moderator

Re: Procedure tunning [message #138772 is a reply to message #138723]

Fri, 23 September 2005 08:54

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.

Report message to a moderator

Re: Procedure tunning [message #138968 is a reply to message #138772]

Mon, 26 September 2005 01:57

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

Report message to a moderator

Previous Topic:	Solaris 9, 10g hard parse/soft parse what does it mean?
Next Topic:	Poor Performance in Accessing Remote Database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 11:00:26 CST 2024