Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The Oracle Database produces reports that are called AWR at specific periods and that share the general status of the database. You can use the following script to examine the related AWRs in more detail.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | SELECT I.INSTANCE_NAME INSTANCE_NAME_PRINT, S.SNAP_ID SNAP_ID, TO_CHAR (S.STARTUP_TIME, 'mm/dd/yyyy HH24:MI:SS') STARTUP_TIME, TO_CHAR (S.BEGIN_INTERVAL_TIME, 'mm/dd/yyyy HH24:MI:SS') BEGIN_INTERVAL_TIME, TO_CHAR (S.END_INTERVAL_TIME, 'mm/dd/yyyy HH24:MI:SS') END_INTERVAL_TIME, ROUND ( EXTRACT (DAY FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) * 1440 + EXTRACT (HOUR FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) * 60 + EXTRACT (MINUTE FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) + EXTRACT ( SECOND FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) / 60, 2) ELAPSED_TIME, ROUND ( (E.VALUE - B.VALUE) / 1000000 / 60, 2) DB_TIME, ROUND ( ( ( ( (E.VALUE - B.VALUE) / 1000000 / 60) / ( EXTRACT ( DAY FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME)* 1440 + EXTRACT ( HOUR FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) * 60 + EXTRACT ( MINUTE FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) + EXTRACT ( SECOND FROM S.END_INTERVAL_TIME - S.BEGIN_INTERVAL_TIME) / 60)) * 100), 2) PCT_DB_TIME FROM DBA_HIST_SNAPSHOT S, GV$INSTANCE I, DBA_HIST_SYS_TIME_MODEL E, DBA_HIST_SYS_TIME_MODEL B WHERE I.INSTANCE_NUMBER = S.INSTANCE_NUMBER AND E.SNAP_ID = S.SNAP_ID AND B.SNAP_ID = S.SNAP_ID - 1 AND E.STAT_ID = B.STAT_ID AND E.INSTANCE_NUMBER = B.INSTANCE_NUMBER AND E.INSTANCE_NUMBER = S.INSTANCE_NUMBER AND E.STAT_NAME = 'DB time' ORDER BY I.INSTANCE_NAME, S.SNAP_ID DESC; |
This script was tested in 11gR1 and 11gR2. Please test the scripts on your test servers before running them in the production environment.
