CPU Usage Queries

CPU usage Queries

Historical CPU Usage

From the below query we can modify the END_INTERVAL_TIME as required by adjusting from where clause.

set linesize 200
set pagesize 120
col module for a60
SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait 
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-1
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;

Query sample output is below

Module                                                         CPU Time  Wait
Time Total Time
------------------------------------------------------------ ---------- ---------- ----------
nqsserver@ckpt0197 (TNS V1-V3)                               9662092.78 37773013.8 47435106.6
nqsserver@ckpt0198 (TNS V1-V3)                               7186342.38 51757370.9 58943713.2
pmdtm@ckpt0004 (TNS V1-V3)                                    926908.77  102606856  103533765
pmdtm@ckpt0003 (TNS V1-V3)                                    811510.04 86777389.2 87588899.3
pmdtm@ckpt0002 (TNS V1-V3)                                    600916.26  179646844  180247760
pmdtm@ckpt0001 (TNS V1-V3)                                    564285.86 82893819.9 83458105.8
SQL Developer                                                  70812.84 2379277.28 2450090.12
SQL*Plus                                                       55273.84 5617194.35 5672468.19

a