Re: Multiple Concurrent DML operation contention

From: Powell, Mark <>
Date: Fri, 22 Dec 2017 19:11:10 +0000
Message-ID: <DF4PR8401MB1276B266EB66313898095AA8CC020_at_DF4PR8401MB1276.NAMPRD84.PROD.OUTLOOK.COM>

Sanjay, generally nothing has to be done for a database to support a 100 concurrent sessions performing DML, that is, unless your application sessions are experiencing lock contention in which case process design changes may be required.

If you have lock contention then you need to look at the session SQL involved and try to determine why the contention exists. For example one relatively long running update was written with a single commit at the end. While this process runs you determine that about 20 updates by PK will be issued and have to wait on the process. If the wait time is interfering with application customer response time then the update process should be changed to commit every N rows where N is a reasonable number based on how long the changed row will be locked and the likelihood of another session needing to update the row. This is, transaction size should be set based on concurrent demand for the target rows, but never less than one logical unit of work.

Mark Powell
Database Administration
(313) 592-5148

From: <> on behalf of Sanjay Mishra <> Sent: Friday, December 22, 2017 1:51:38 PM To: Oracle-L Freelists
Subject: Multiple Concurrent DML operation contention


Can someone tell what are the changes to be done on Object level including tablespace for table where more than 100 session are concurrently doing DML on RAC environmen ? It is Oracle 12c R1


Received on Fri Dec 22 2017 - 20:11:10 CET

Original text of this message