Find user commits per minute in oracle DB

 How to find the maximum user doing commit operation or transaction in Oracle DB

Script helpful in tracking the number of transactions in the database.

Note: It used the AWR tables like dba_hist_sysstats or dba_hist_snapshot. So, need the license for access.

col STAT_NAME for a20
col VALUE_DIFF for 9999,999,999
col STAT_PER_MIN for 9999,999,999
set lines 200 pages 1500 long 99999999
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
set pagesize 40
set pause on
select hsys.SNAP_ID,
 hsnap.BEGIN_INTERVAL_TIME,
 hsnap.END_INTERVAL_TIME,
 hsys.STAT_NAME,
 hsys.VALUE,
 hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS
"VALUE_DIFF",
 round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY
hsys.SNAP_ID)) /
 round(abs(extract(hour from (hsnap.END_INTERVAL_TIME -
hsnap.BEGIN_INTERVAL_TIME))*60 +
 extract(minute from (hsnap.END_INTERVAL_TIME -
hsnap.BEGIN_INTERVAL_TIME)) +
 extract(second from (hsnap.END_INTERVAL_TIME -
hsnap.BEGIN_INTERVAL_TIME))/60),1)) "STAT_PER_MIN"
from dba_hist_sysstat hsys, dba_hist_snapshot hsnap
where hsys.snap_id = hsnap.snap_id
and hsnap.instance_number in (select instance_number from v$instance)
and hsnap.instance_number = hsys.instance_number
and hsys.STAT_NAME='user commits'
order by 1;

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle