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

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

You May Also Like

Clone a database using dbca command in oracle 19c -New feature
COLS & COLSEXCEPT FILTER in goldengate
How to move spfile from file system to ASM in RAC
How to install postgres database on mac os/linux
ORA-01536: space quota exceeded for tablespace
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
How to get the execution plan for a SQL between two AWR snapshots
DEFERRED_SEGMENT_CREATION parameter in oracle
What is colored sql_id in dba_hist_colored_sql
Important views in For Oracle DBA in daily usage

From This Website

Steps for upgrading oracle database to 19c using DBUA
How to move controlfile to a new location in oracle
DEFERRED_SEGMENT_CREATION parameter in oracle
_use_adaptive_log_file_sync parameter in oracle
Oracle database Security Assessment Tool
How to recreate MGMT database in ORACLE 12C GRID
ORA-32773: operation not supported for smallfile tablespace
how to change archivelog destination in oracle
How to create user in MySQL Database
How to flashback a Pluggable database ( PDB) in oracle 12.2