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

Find pending distributed pending transactions 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
Script to get cpu usage and wait event information in oracle database
Get sid from ospid
Find sessions consuming lot of CPU
Find user commits per minute in oracle database
How to get tablespace quota details of an user in oracle

You May Also Like

Apply database proactive bundle patch in RAC using manual process
How to get tablespace quota details of an user in oracle
Apply patch on oracle 12.2 database ( Release update)
SET ROWLIMIT in oracle 18C - New feature
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Shell script to monitor goldengate process
Ansible script to run script on remote server and fetch the output
Schema replication using oracle goldengate
Find sessions consuming lot of CPU
Get sid from ospid

From This Website

Flashback primary database in dataguard environment
How to get tablespace quota details of an user in oracle
ENABLE_PARALLEL_DML hint in oracle 12c
Schema replication using oracle goldengate
Shell script to monitor asm diskgroup usage
Apply database proactive bundle patch in RAC using manual process
How to restart MySQL on Linux and Windows
Rollback database patch in oracle 12c
How to move spfile from file system to ASM in RAC
DEFERRED_SEGMENT_CREATION parameter in oracle