Re: Multiple Concurrent DML operation contention

My Mistake as I had only shared few details What I am seeing is 50% Buffer Deadlock and 25% Gc buffer busy Acquire and 25% is USer I/O read by another session. All will be fine with only few concurrent session like 30 but as it increases, it degrades and after 60, saw several Buffer Deadlock and GC waits. All concurrent session are doing update against the same table. Query been seen high above event is simple select from table with bind variable and plan cost is only 3.  Sanjay

    On Friday, December 22, 2017 2:32 PM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:  

 Hi Sanjay,
You haven't provided enough information to analyze your hardest problem. Anyway, the best performance (in case of GC contentions) you can get if each node touches and changes own blocks only, but usually it's impossible, though you can minimize cluster waits with right partitioning design and splitting workload by nodes. The funniest and biggest improvement I got 3 or 4 years ago with one write-only non-partitioned table used for logging - there were huge of inserts into this table: I converted this table to partitioned table and added column Instance_ID with default value  "sys_context('userenv','instance')" :) On Fri, Dec 22, 2017 at 10:11 PM, Powell, Mark <mark.powell2_at_dxc.com> wrote:

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 PowellDatabase Administration(313) 592-5148

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org > on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org> Sent: Friday, December 22, 2017 1:51:38 PM To: Oracle-L Freelists
Subject: Multiple Concurrent DML operation contention Hi 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 Sanjay

--

Best regards,
Sayan MalakshinovOracle performance tuning engineer Oracle ACE Associate
http://orasql.org    

--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 22 2017 - 20:52:55 CET