Below script is useful in getting user commit statistics information in the oracle database. user commits is the number of commits happening the database. It will be helpful in tracking the number of transactions in the database.
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;
OUTPUT:
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME STAT_NAME VALUE VALUE_DIFF STAT_PER_MIN
---------- ------------------------------ ------------------------------ -------------------- ---------- ------------- -------------
6626 11-NOV-17 05.00.13.272 PM 11-NOV-17 06.00.29.527 PM user commits 350001525 1,147,017 19,022
6627 11-NOV-17 06.00.29.527 PM 11-NOV-17 07.00.14.759 PM user commits 351130223 1,128,698 18,875
6628 11-NOV-17 07.00.14.759 PM 11-NOV-17 08.00.02.845 PM user commits 351987886 857,663 14,342
6629 11-NOV-17 08.00.02.845 PM 11-NOV-17 09.00.22.109 PM user commits 352829839 841,953 13,963
6630 11-NOV-17 09.00.22.109 PM 11-NOV-17 10.00.07.076 PM user commits 353478483 648,644 10,865
6631 11-NOV-17 10.00.07.076 PM 11-NOV-17 11.00.24.303 PM user commits 353939928 461,445 7,652
6632 11-NOV-17 11.00.24.303 PM 12-NOV-17 12.00.11.904 AM user commits 354335275 395,347 6,611
6633 12-NOV-17 12.00.11.904 AM 12-NOV-17 01.00.29.406 AM user commits 354604745 269,470 4,469
6634 12-NOV-17 01.00.29.406 AM 12-NOV-17 02.00.17.332 AM user commits 354955934 351,189 5,873
6635 12-NOV-17 02.00.17.332 AM 12-NOV-17 03.00.03.228 AM user commits 356918293 1,962,359 32,815
6636 12-NOV-17 03.00.03.228 AM 12-NOV-17 04.00.20.577 AM user commits 357821672 903,379 14,981
6637 12-NOV-17 04.00.20.577 AM 12-NOV-17 05.00.09.204 AM user commits 358154880 333,208 5,572
6638 12-NOV-17 05.00.09.204 AM 12-NOV-17 06.00.25.507 AM user commits 358296694 141,814 2,352
6639 12-NOV-17 06.00.25.507 AM 12-NOV-17 07.00.09.734 AM user commits 358692156 395,462 6,624
6640 12-NOV-17 07.00.09.734 AM 12-NOV-17 08.00.01.047 AM user commits 359373748 681,592 11,379
6641 12-NOV-17 08.00.01.047 AM 12-NOV-17 09.00.17.981 AM user commits 360418586 1,044,838 17,327
6642 12-NOV-17 09.00.17.981 AM 12-NOV-17 10.00.04.542 AM user commits 362476024 2,057,438 34,405
6643 12-NOV-17 10.00.04.542 AM 12-NOV-17 11.00.22.732 AM user commits 364469092 1,993,068 33,053
6644 12-NOV-17 11.00.22.732 AM 12-NOV-17 12.00.09.693 PM user commits 365611444 1,142,352 19,103
6645 12-NOV-17 12.00.09.693 PM 12-NOV-17 01.00.27.672 PM user commits 366866479 1,255,035 20,813
6646 12-NOV-17 01.00.27.672 PM 12-NOV-17 02.00.14.537 PM user commits 368466462 1,599,983 26,756
STAT_PER_MIN -Number of commits per minutes, during that snap time
