| URGENT:-Tips for Tuning Stored Procedures [message #65726] |
Wed, 15 December 2004 21:29  |
Milind Deshpande
Messages: 93
Registered: May 2004
|
Member |
|
|
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.
Miilind.
|
|
|
|
| Re: URGENT:-Tips for Tuning Stored Procedures [message #65729 is a reply to message #65726] |
Thu, 16 December 2004 00:23  |
Frank
Messages: 7901
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.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
