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