I will simulate “row lock contention” using following procedure( I will resue my test case mentioned in Test case 4:-Now I will run update in parallel 10 sessions of https://clouddba.co/test-case-when-index-size-can-grow-than-column-size-and-effect-of-rebuild-index/)
CREATE OR REPLACE procedure TEST.manyupdates as
for i in 1..100000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
update t set id=v_m/15*i where rownum<10;
CREATE OR REPLACE procedure TEST.manysessions_update as
FOR i in 1..10 LOOP
As we are running this procedure parallel with 10 sessions,it will definitely create row lock contention even in very short time.
Now application team will raise concern that system is very slow and you as DBA start to investigate.
You should check %CPU section to identify top CPU consuming processes
You will notice load average is almost 5.It means at least 5-6 processes are running at a time.You can check in TOP with column S where it shows ‘R’ means running and ‘S’ means sleeping.
Also note 72% idle and 22% CPU busy.It is just a indication that your system is bit busy with CPU load.
%Cpu(s): 22.6 us, 4.6 sy, 0.0 ni, 72.3 id, 0.1 wa, 0.0 hi, 0.5 si, 0.0 st
If you want to investigate which process takes more memory,you need to sort the output on basis of “RES”
sar -u 10 8
%user: This shows the total time that the processor is spending on different process .
%iowait: the name iowait itself suggests that its the time spend by processor waiting for devices(input and output)
%nice: Most of you guys must be knowing that a user can change the priority of a linux process. This table shows the time spend by CPU for process whose nice value has been changed.
%steal: This column shows the amount to time spend by a CPU (which is virtualized), for resources from the physical CPU
%idle: This suggests the idle time spend by the processor.
A low %idle time could point to which processes are using the most CPU . Use the ps or top command to find a CPU-intensive job. A poorly written query requiring a large amount of disk access can also cause a large amount of CPU usage as well.
In the above sar output, the cause for concern is the large values being returned for %user means application is using more cpu.%iowait (waiting for block I/O) is low here so we are not having disk contention.
We need to make below consideration while checking sar report of CPU
vmstat 1 10
r column is runnable processes.So 7 processes are currently running.IDLE percent is overall 70%.
Any non-zero value of “SO” column leads to probable memory leak in SWAP which needs to be analyzed immediately.
For more info about vmstat:-
sar -d 5 2
tps: tps stands for transfer per second, so it shows the transfer per second to that particular device
rd_sec/s: this shows you the total number of sectors on that device which is being read
wr_sec/s: if rd_sec/s is sectors being read per second then obviously wr_sec is sectors being written per second.
await: this shows the total number of time that the processor waited for requests regarding IO
%util on a device that is greater than 50 percent denotes disk bottleneck.In our case it is normal for highly utilized disk.
If await is greater than svctm due to Unbalanced disk I/O load.
Please note disk dev129-160 is most busiest disk.The tps is 100 almost.
The sar –b command reports on the system’s buffer cache activities (not the Oracle buffer cache). It gives you the number of transfers per second between system buffers and block devices.
./oratop -f -d -i 10 / as sysdba
ASW:-Active session waiting
Event:-Enq:TX – row lock contention is top wait event.AVG_MS is almost 0.It will be big concern if AVG_MS is very large which means your session is being blocked long time.
SQLID/BLOCKER:-You will get which session is blocking and which sessions are blocked.
Other statistics are also will be useful for more investigation.
Please refer for more info on oratop :-
Form OEM console you can check how many active sessions are waiting and RED means concurrency wait.You can also find SQL_ID responsible.
You may look below section for AWR.The most important top 10 foreground events .42% DB time has been contributed by enqTX – row lock contention.
This section segments by row lock waits show which objects are getting locked.
ASH report is very handy for generating which SQL are responsible for locking.
Please check top sessions with SID and SERIAL for deeper details.Please check %event column to check what are most eventful wait events.