Home » RDBMS Server » Performance Tuning » Procedure Tuning
Procedure Tuning [message #265435] Thu, 06 September 2007 05:39 Go to next message
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
Re: Procedure Tuning [message #265437 is a reply to message #265435] Thu, 06 September 2007 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Procedure Tuning [message #265438 is a reply to message #265435] Thu, 06 September 2007 05:44 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Yes..It is row by row pushing the data into temp table
Re: Procedure Tuning [message #265440 is a reply to message #265438] Thu, 06 September 2007 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this is the root of your bad performances.

Regards
Michel
Re: Procedure Tuning [message #265441 is a reply to message #265435] Thu, 06 September 2007 05:48 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
And Michel, can you please suggest me how to improve my procedure?

Regards
Yashora
Re: Procedure Tuning [message #265447 is a reply to message #265441] Thu, 06 September 2007 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Modify your code to not use a row by row process.

Regards
Michel
Re: Procedure Tuning [message #265672 is a reply to message #265447] Thu, 06 September 2007 22:01 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Oracle10g 10.0.2.0.1 Performance Problems
Next Topic: Instance Efficiency
Goto Forum:
  


Current Time: Thu Dec 08 16:22:06 CST 2016

Total time taken to generate the page: 0.19600 seconds