Re: Multiple Concurrent DML operation contention

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 Powell
> Database 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 Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l