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;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply