Home » RDBMS Server » Performance Tuning » URGENT:-Tips for Tuning Stored Procedures
URGENT:-Tips for Tuning Stored Procedures [message #65726] Wed, 15 December 2004 21:29 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Hi Experts,

I have been assigned a task of Tuning Stored Procedures containing DML statements having Insert,Update and Delete statements. I need some tips from you all that where should I look for Potenital performance Issues which will Improve performance of my SP's.

Our main concern is when Sp is executed due to DML the table gets locked and if any other transaction tries to read the same table performance is degraded. So, for this issue is there is any way through which I can avoid locks on a Table.

Is there any mechanism which will take care of COMMIT / ROLLBACK of INSERT / UPDATE / DELETE SPs and avoid table locking.

My main concern is improve performance of SP's for my application.

Any suggestions from you experts is appreciated.

Please help.

Thanks in advance.

Re: URGENT:-Tips for Tuning Stored Procedures [message #65729 is a reply to message #65726] Thu, 16 December 2004 00:23 Go to previous message
Messages: 7897
Registered: March 2000
Senior Member
In Oracle, a lock from one session has little to no performance effects on a select from another session. (Apart from traversing through rollback-blocks)
Also, if you insert/update/delete rows in a table, it is NOT the table that gets locked; it is the row that gets locked.

Previous Topic: Urgent, SQL tuning
Next Topic: experiences in performance tuning
Goto Forum:

Current Time: Tue Jan 31 08:52:19 CST 2023