Below script will give information about the CPU usage and wait events class information of every minute for last 2 hours. As this query uses gv$active_session_history, so make sure you have TUNING license pack of oracle, before using this.

set lines 288
col sample_time for a14
col CONFIGURATION head "CONFIG" for 99.99
col ADMINISTRATIVE head "ADMIN" for 99.99
col OTHER for 99.99

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '2'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY,
'Application' AS  APPLICATION,
'Commit' AS  COMMIT,
'Configuration' AS CONFIGURATION,
'Administrative' AS   ADMINISTRATIVE,
'Network' AS  NETWORK,
'Queueing' AS   QUEUEING,
'Cluster' AS   CLUST,
'Other' AS  OTHER))

OUTPUT WILL LOOK AS BELOW:

SAMPLE_TIME     OTHER      CLUST   QUEUEING    NETWORK  ADMIN CONFIG     COMMIT APPLICATION CONCURRENCY  SYSTEM_IO    USER_IO  SCHEDULER        CPU BACKGROUND_CPU
-------------- ------ ---------- ---------- ---------- ------ ------ ---------- ----------- ----------- ---------- ---------- ---------- ---------- --------------
10:13            1.60       .033          0          0    .00    .00        1.2           0        .017       .617       .017          0      2.567           .317
10:14            2.08       .033          0          0    .00    .00        1.9        .067           0       .783       .217          0      3.433           .417
10:15            2.83          0          0          0    .00    .00        1.6        .017        .033         .9         .5          0       4.05           .767
10:16            3.15        .05          0       .033    .00    .00      2.033           0        .033         .9       .617          0        4.7            .75
10:17            3.18          0          0       .017    .00    .00      1.883           0           0       .717       .467          0       4.65           .683
10:18            2.78          0          0          0    .00    .00      1.417           0           0        .65       .067          0      3.867            .65
10:19            3.30          0          0          0    .00    .00      1.233           0        .083       .533       .033          0      4.533             .9
10:20            4.18       .033          0       .033    .00    .00        1.3           0        .017       .933       .117          0        5.5           .817
10:21            2.85       .033          0          0    .00    .00       1.15           0           0       .783        .05          0        4.1            .75
10:22            3.32       .017          0       .017    .00    .00      4.133           0        .017         .9       .033          0      5.317          1.033
DATABASE SCRIPTS


Related Topics

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

You May Also Like

Change dbsnmp password for target db in oem 12c
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
shell script for file system alert
PDB Lockdown Profiles in Oracle 12.2
Find pending distributed pending transactions in oracle
How to install postgres database on mac os/linux
How to add a tempfile in primary database in dataguard
Standby redologs in oracle dataguard
Steps for changing public hostname for a standalone grid infrastructure
How to use expdp to export data from physical standby database

From This Website

SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp
COLS & COLSEXCEPT FILTER in goldengate
Perform Flashback in pluggable database(PDB) in oracle 12.2
Install oracle enterprise manager cloud control 12c
Shared Pool Advisory in oracle
How to run expdp in pluggable database(PDB)
how to change archivelog destination in oracle
How to change spfile in Oracle RAC.
How to create encrypted tablespace in PDB( oracle 12c)
How to change sysman password for oracle 12c cloud control