Home » RDBMS Server » Performance Tuning » Procedure taking tomuch time in execution (Oracle 10g)
Procedure taking tomuch time in execution [message #487840] Sun, 02 January 2011 13:14 Go to next message
arpitpasari
Messages: 4
Registered: December 2010
Location: INDIA
Junior Member
In my procedure having 7 temporary (not global table) table .Each tables contain at least 6 lack records .I am fetching the data from one main table (out of 7 tables) through cursor. On the basis of this record I will select data from others 6 tables. After collect all the information I will update or insert the records in 5 main tables. But this is taking so much time more than 3 hours. Bulk insert and update is not possible because in some table I have to update/insert multiple records....I commented the insert and updated part than it took only 2 mins.Even I observe at staring process run so fast but after process of 10000 records it will slow down. please suggest me what could be the problem .any possibility of table locking or less memory of database buffer......How can I check
Thanks
Re: Procedure taking tomuch time in execution [message #487841 is a reply to message #487840] Sun, 02 January 2011 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I checked I took your code and modify it in the following way it now works very fast.

Regards
Michel
Re: Procedure taking tomuch time in execution [message #487842 is a reply to message #487841] Sun, 02 January 2011 13:35 Go to previous messageGo to next message
arpitpasari
Messages: 4
Registered: December 2010
Location: INDIA
Junior Member
Thanks Michel....sorry Due to some constraint I can not share the code by the way it is some big more then 3500 lines too much business logic .....But i tried to run the code after remove the update and insert part that time it took only 2 min it means there should be some problem in insert or update please suggest
Re: Procedure taking tomuch time in execution [message #487843 is a reply to message #487842] Sun, 02 January 2011 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is in the loops.

Regards
Michel
Re: Procedure taking tomuch time in execution [message #487844 is a reply to message #487843] Sun, 02 January 2011 14:08 Go to previous messageGo to next message
arpitpasari
Messages: 4
Registered: December 2010
Location: INDIA
Junior Member
In my code i am using for loop cursor.So i dont think so there could be any problem and why are u feeling this ,problem is in loop. can explain this so that i can check my code.
Thanks
Arpit Pasari
Re: Procedure taking tomuch time in execution [message #487845 is a reply to message #487844] Sun, 02 January 2011 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because most of the time the problem is in the loops, too much loops, useless loops... and above all COMMIT in the loops.

Regards
Michel

[Updated on: Sun, 02 January 2011 14:20]

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487846 is a reply to message #487845] Sun, 02 January 2011 14:27 Go to previous messageGo to next message
arpitpasari
Messages: 4
Registered: December 2010
Location: INDIA
Junior Member
Thanks Michel but In my code only one loop is there even i told u that I comment the all insert and update statement i found thta code get executed within 2 min in..... nad loop is also there and one more thing i observe that in starting processing is so fast but after some time it get slow but it is so.

Regards
Arpit
Re: Procedure taking tomuch time in execution [message #487847 is a reply to message #487846] Sun, 02 January 2011 14:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Procedure taking tomuch time in execution [message #487916 is a reply to message #487846] Mon, 03 January 2011 05:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
arpitpasari wrote on Sun, 02 January 2011 21:27
I comment the all insert and update statement i found that code get executed within 2 min in ... one more thing i observe that in starting processing is so fast but after some time it get slow but it is so.

Are those tables indexed? If so, what happens if you drop them (indexes, not the tables) before running the procedure?
Re: Procedure taking tomuch time in execution [message #488027 is a reply to message #487840] Tue, 04 January 2011 04:25 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
TKPROF?
Previous Topic: Oracle Coalesce vs Index Rebuild
Next Topic: Procedure runs long time (merged)
Goto Forum:
  


Current Time: Fri Mar 29 02:07:41 CDT 2024