Skip to main content
oracleScriptsUncategorized

CPU Usage Queries

By June 20, 2012October 7th, 2016No Comments2 min read

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

Leave a Reply