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