OraFAQ Forum: Performance Tuning » Procedure taking tomuch time in execution

Home » RDBMS Server » Performance Tuning » Procedure taking tomuch time in execution (Oracle 10g)

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

Procedure taking tomuch time in execution [message #487840]

Sun, 02 January 2011 13:14

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

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487841 is a reply to message #487840]

Sun, 02 January 2011 13:18

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I checked I took your code and modify it in the following way it now works very fast.

Regards
Michel

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487842 is a reply to message #487841]

Sun, 02 January 2011 13:35

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

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487843 is a reply to message #487842]

Sun, 02 January 2011 13:55

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The problem is in the loops.

Regards
Michel

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487844 is a reply to message #487843]

Sun, 02 January 2011 14:08

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

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487845 is a reply to message #487844]

Sun, 02 January 2011 14:20

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487847 is a reply to message #487846]

Sun, 02 January 2011 14:32

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

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #487916 is a reply to message #487846]

Mon, 03 January 2011 05:38

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Procedure taking tomuch time in execution [message #488027 is a reply to message #487840]

Tue, 04 January 2011 04:25

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

TKPROF?

Report message to a moderator

Previous Topic:	Oracle Coalesce vs Index Rebuild
Next Topic:	Procedure runs long time (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 01:24:54 CST 2024