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


Related Topics

Script to get cpu usage and wait event information in oracle database
Find sessions consuming lot of CPU
Get sid from ospid
Find user commits per minute in oracle database
Find pending distributed pending transactions in oracle
How to get tablespace quota details of an user in oracle
Find the active transactions in oracle database
How to monitor parallel queries in oracle db
How to find execution history of an sql_id
How to find cpu and memory information of oracle database server

You May Also Like

Alert log rotation script in oracle
TNS-12542: TNS:address already in use
OGG-00665 OCI Error describe for query
Schema replication using oracle goldengate
ORA-16855: transport lag has exceeded specified threshold
Script to get cpu usage and wait event information in oracle database
SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp
How to install oracle client in silent mode using response file
Standby redologs in oracle dataguard
Install oracle enterprise manager cloud control 12c

From This Website

Shell script to monitor goldengate process
How to disable enable log shipping in standby using dgmgrl
Purge AUD$ table using DBMS_AUDIT_MGMT
Change dbsnmp password for target db in oem 12c
Find sessions consuming lot of CPU
sec_case_sensitive_logon parameter in oracle
OGG-01028 partial record at sequence extract abended
Open wallet automatically after starting the database
Steps for upgrading oracle database to 19c using DBUA
How to move controlfile to a new location in oracle