TX - row lock contention

we saw this in one of our databases.while investigating we found this as the event for most of the application schema sessions


 SID           SERIAL# USERNAME                       SQL_HASH_VALUE EVENT
---------- ---------- ------------------------------ -------------- ----------------------------------------
      1563       4259 APPLOGPRODPRO                      3190491100 latch: cache buffers chains
      1564      44504 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1565      32328 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1566       4817 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1569      37523 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1570      19923 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1573      64233 APPLOGPRODPRO                       782409889 SQL*Net message from client
      1575      13060 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1576      16088 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1578      15137 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention
      1579      12304 APPLOGPRODPRO                      2432335213 enq: TX - row lock contention


all the sessions were having same sql_hash_value and event

following queries can be used to investigate further
For which SQL is currently waiting on:

select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in

('WAITING') and wait_class != 'Idle'and event='enq: TX - row lock contention'and 

      (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is

select

    blocking_session,

    sid,    serial#,

    wait_class,

    seconds_in_wait 

from

    v$session 

where

    blocking_session is not NULL

order by

    blocking_session;

other way to check is 

 v$wait_chains - only in 11g

otherwise we can take hang analysis also to see the hanging session

oradebug setmypid

oradebug unlimit

oradebug hanganalyze 3

------------wait for 2 mins

oradebug hanganalyze 3

oradebug tracefile_name

HANG ANALYSIS:

==============

Found 31 objects waiting for

    <1 data-blogger-escaped-color="#ff0000" data-blogger-escaped-font="font">1573/64233/0x5c902070/1554/SQL*Net message from client>

Open chains found:

Chain 1 : :

    <1 data-blogger-escaped-client="client" data-blogger-escaped-et="et" data-blogger-escaped-from="from" data-blogger-escaped-message="message" data-blogger-escaped-x5c902070="x5c902070">

 -- <1 data-blogger-escaped--="-" data-blogger-escaped-contention="contention" data-blogger-escaped-enq:="enq:" data-blogger-escaped-lock="lock" data-blogger-escaped-row="row" data-blogger-escaped-tx="tx" data-blogger-escaped-x5f903a70="x5f903a70">

Other chains found:

Chain 2 : :

    <1 data-blogger-escaped-jobq="jobq" data-blogger-escaped-slave="slave" data-blogger-escaped-wait="wait" data-blogger-escaped-x5f909218="x5f909218">

Chain 3 : :

    <1 data-blogger-escaped-jobq="jobq" data-blogger-escaped-slave="slave" data-blogger-escaped-wait="wait" data-blogger-escaped-x5c911778="x5c911778">

Chain 4 : :

    <1 data-blogger-escaped-o="o" data-blogger-escaped-wait="wait" data-blogger-escaped-x5c914748="x5c914748">

Chain 5 : :

    <1 data-blogger-escaped-jobq="jobq" data-blogger-escaped-slave="slave" data-blogger-escaped-wait="wait" data-blogger-escaped-x5c912768="x5c912768">

Chain 6 : :

    <1 data-blogger-escaped-o="o" data-blogger-escaped-wait="wait" data-blogger-escaped-x5f905a50="x5f905a50">

Session 1573 was the one blocking all the session,Kill the session and you are done!!!!

Archive log generated per day or per hour

This is to find the historical archive log generation report.

Simple query to find the archive generated
===============================================================
select trunc(completion_time),count(1),round(count(1)*200/1024) from v$archived_log group by  trunc(completion_time) order by  trunc(completion_time);



Query to give size of archive generated perday in GB
============================================================

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_GB
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;


Query to give archive generation on an hourly basis
==================================================================

set pagesize 120;
set linesize 200;
col day for a8;
select
  to_char(first_time,'YY-MM-DD') day,
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
  COUNT(*)
from v$log_history
group by to_char(first_time,'YY-MM-DD')
order by day ;




For sizing of archive log destination, 
1.it should be twice the size of maximum archive generated per day.
2.compressed backup of archive logs comes to 30 % of actual archive log size.