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

 

 

DATABASE SCRIPTS