OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Procedure Tuning

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

Procedure Tuning [message #265435]

Thu, 06 September 2007 05:39

yashora
Messages: 39
Registered: August 2006

Member

Hi,

I do have a procedure that accepts a cursor which is having an order by clause with it.

After opening the cursor and based on the values that are being generated i need to push those values into temporary table.

After the loop is over, i said commit.

But the question is, i do have millions of rows on hand and the performance has become too slow. Is that due to the "order by" clause i have with the cursor or with the "insert statement"?.

Please advice me to improve the performance.

Thanks in advance.

Yashora

Report message to a moderator

Re: Procedure Tuning [message #265437 is a reply to message #265435]

Thu, 06 September 2007 05:42

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is the PL/SQL slow by slow (sorry row by row) processing that slows down your procedure.

Regards
Michel

Report message to a moderator

Re: Procedure Tuning [message #265438 is a reply to message #265435]

Thu, 06 September 2007 05:44

yashora
Messages: 39
Registered: August 2006

Member

Yes..It is row by row pushing the data into temp table

Report message to a moderator

Re: Procedure Tuning [message #265440 is a reply to message #265438]

Thu, 06 September 2007 05:45

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And this is the root of your bad performances.

Regards
Michel

Report message to a moderator

Re: Procedure Tuning [message #265441 is a reply to message #265435]

Thu, 06 September 2007 05:48

yashora
Messages: 39
Registered: August 2006

Member

And Michel, can you please suggest me how to improve my procedure?

Regards
Yashora

Report message to a moderator

Re: Procedure Tuning [message #265447 is a reply to message #265441]

Thu, 06 September 2007 06:01

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Modify your code to not use a row by row process.

Regards
Michel

Report message to a moderator

Re: Procedure Tuning [message #265672 is a reply to message #265447]

Thu, 06 September 2007 22:01

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Either insert all of the rows into the temporary table in one statement, or use FORALL statement to insert by arrays.
See http://www.orafaq.com/node/1399

Ross Leishman

Report message to a moderator

Previous Topic:	Oracle10g 10.0.2.0.1 Performance Problems
Next Topic:	Instance Efficiency

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 05:51:30 CDT 2024