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@xxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx>
*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
