how much CPU a session consuming at a given time in oracle

Question

I am looking for a script/process to check the amount of CPU (in terms of percentage) each query consumes at any point of time in my oracle database.

My Oracle version is : 12.1.0.2 Database is hosted on Exadata X7_2 It is a shared server, and many databases also running on the same server.

SELECT AVG(value/vp.cpu_count) FROM v$sysmetric_history,(SELECT value cpu_count FROM v$parameter WHERE name LIKE 'cpu_count') vp WHERE metric_name = 'CPU Usage Per Sec' AND group_id = 2 AND end_time > SYSDATE - 10/(60*24) AND end_time <= SYSDATE;

I am using this script to calculate the instance CPU utilization at a given time. Now, i would like to find out how much each SQL query is consuming in given CPU.

Example: at a given time if my CPU utilization is 80%, i would like to see how much CPU each sql query is contributing in that 80% of instance CPU.

Answer 1

To answer this question, you need historic performance metrics. The easiest way to get these is from the AWR view DBA_HIST_SQLSTAT [license required] (can also use StatsPack). As far as getting a percentage you’d need to calculate that from the total available CPU, but probably just ordering on CPU consumption would be enough. The query I use from the AWR tables:

select ord ord
,  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then 'elapsed_sec' 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then 'cpu_sec'
       when nvl(:order_by, 'GET') in ('IO'   , '3') then 'iowait'
       when nvl(:order_by, 'GET') in ('GET'  , '4') then 'buffer_gets'
       when nvl(:order_by, 'GET') in ('READ' , '5') then 'disk_reads'
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then 'executions'
       when nvl(:order_by, 'GET') in ('PARSE', '7') then 'parse_calls'
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then 'sharable_mem'
       when nvl(:order_by, 'GET') in ('VERS' , '9') then 'version_count' 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then 'time_per_exec' 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then 'snap_id' 
       else 'buffer_gets'
  end order_by
, schema  
, sql_id
, plan_hash_value "Plan Hash Value"
,      (select 
        max(to_number(extractvalue(
        xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]'))) plan_hash_2
        from   dba_hist_sql_plan hp
        where  hp.sql_id          = main_query.sql_id
        and    hp.plan_hash_value = main_query.plan_hash_value
        and    hp.other_xml is not null) plan_hash_2
, (select max(last_refresh_time) from gv$sql_monitor sm where sm.sql_id = main_query.sql_id and sm.sql_plan_hash_value = main_query.plan_hash_value) monitor_last_refresh_time
, time_per_exec "Time Per Exec"
, executions "Exec-utions"
, clock_time "Clock Time"
, px_servers_execs "px servers execs"
, sql_text
, buffer_gets "Buffer Gets"
, fetches
, rows_processed "rows processed"
, round(rows_processed / nullif(fetches, 0)) "rows per fetch" 
, end_of_fetch_count "end of fetch count"
, sorts
, disk_reads "disk reads"
, tot_wait "Tot Wait"
, iowait
, clwait
, apwait
, ccwait
, direct_writes "direct writes"
, elapsed_sec "Elap-sed (Sec)"
, cpu_sec "CPU Sec"
, plsql_sec "PL/SQL sec"
, plsexec_time "pls exec time"
, javexec_time "java exec time"
, sharable_mem "shar-able mem"
-- per exec calculations
, case when executions > 0 then buffer_gets/executions else 0 end "Buffer Gets per exec"
, case when executions > 0 then fetches/executions else 0 end  "Fetches Gets per exec"
, case when executions > 0 then rows_processed/executions else 0 end  "rows per exec"
, case when executions > 0 then sorts/executions else 0 end  "sorts per exec"
, case when executions > 0 then disk_reads/executions else 0 end  "disk reads per exec"
, case when executions > 0 then tot_wait/executions else 0 end  "Tot Wait per exec"
, case when executions > 0 then iowait/executions else 0 end  "iowait per exec"
, case when executions > 0 then clwait/executions else 0 end  "clwait  per exec"
, case when executions > 0 then apwait/executions else 0 end  "apwait per exec"
, case when executions > 0 then ccwait/executions else 0 end  "ccwait  per exec"
, case when executions > 0 then direct_writes/executions else 0 end  "direct writes  per exec"
, case when executions > 0 then elapsed_sec/executions else 0 end  "Elap-sed (Sec)  per exec"
, case when executions > 0 then cpu_sec/executions else 0 end  "CPU Sec per exec"
, case when executions > 0 then plsql_sec/executions else 0 end  "PL/SQL sec  per exec"
, case when executions > 0 then plsexec_time/executions else 0 end  "pls exec time  per exec"
, case when executions > 0 then javexec_time/executions else 0 end  "java exec time per exec"
, case when executions > 0 then sharable_mem/executions else 0 end  "shar-able mem per exec"
-- per row calculations
, case when rows_processed > 0 then buffer_gets/rows_processed else 0 end "Buffer Gets per row"
, case when rows_processed > 0 then fetches/rows_processed else 0 end  "Fetches Gets per row"
, case when rows_processed > 0 then rows_processed/rows_processed else 0 end  "rows per row"
, case when rows_processed > 0 then sorts/rows_processed else 0 end  "sorts per row"
, case when rows_processed > 0 then disk_reads/rows_processed else 0 end  "disk reads per row"
, case when rows_processed > 0 then tot_wait/rows_processed else 0 end  "Tot Wait per row"
, case when rows_processed > 0 then iowait/rows_processed else 0 end  "iowait per row"
, case when rows_processed > 0 then clwait/rows_processed else 0 end  "clwait  per row"
, case when rows_processed > 0 then apwait/rows_processed else 0 end  "apwait per row"
, case when rows_processed > 0 then ccwait/rows_processed else 0 end  "ccwait  per row"
, case when rows_processed > 0 then direct_writes/rows_processed else 0 end  "direct writes  per row"
, case when rows_processed > 0 then elapsed_sec/rows_processed else 0 end  "Elap-sed (Sec)  per row"
, case when rows_processed > 0 then cpu_sec/rows_processed else 0 end  "CPU Sec per row"
, case when rows_processed > 0 then plsql_sec/rows_processed else 0 end  "PL/SQL sec  per row"
, case when rows_processed > 0 then plsexec_time/rows_processed else 0 end  "pls exec time  per row"
, case when rows_processed > 0 then javexec_time/rows_processed else 0 end  "java exec time per row"
, case when rows_processed > 0 then sharable_mem/rows_processed else 0 end  "shar-able mem per row"
, loaded_versions "loaded vers-ions" 
, version_count "ver-sion count"
, loads
, invalidations "invalid-ations"
, parse_calls "parse calls"
, module 
, command_type_name
, to_char(min_time, 'mm/dd/yyyy HH24:MI:SS') min_time
, to_char(max_time ,'mm/dd/yyyy HH24:MI:SS') max_time
, min_snap_id "Min Snap Id"
, max_snap_id "Max Snap Id"
, sql_profile
, Baseline_plan_name -- does not work for 10g
from
(
select schema  
   , plan_hash_value
   , sql_id
   , rownum ord
   , sub.elapsed_sec
   , CASE 
     WHEN elapsed_sec > 86399
          THEN elapsed_sec || ' sec' 
     WHEN elapsed_sec <= 86399
          THEN to_char(to_date(round(elapsed_sec) ,'SSSSS'), 'HH24:MI:SS') 
     END as clock_time
   , case when executions <> 0
     then CASE 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) > 86399
          THEN round(elapsed_sec/(executions)*decode(px_servers_execs, 0, 1, px_servers_execs)) || ' sec' 
     WHEN round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) <= 86399
          THEN to_char(to_date(round(elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs))) ,'SSSSS'), 'HH24:MI:SS') 
     END 
     end as time_per_exec
   , cpu_sec
   , plsql_sec
   , executions
   , buffer_gets
   , sharable_mem
   , loaded_versions
   , version_count
   , module 
   , fetches
   , end_of_fetch_count
   , sorts
   , px_servers_execs
   , loads
   , invalidations
   , parse_calls
   , disk_reads
   , rows_processed
   , iowait
   , clwait
   , apwait
   , ccwait
   , tot_wait
   , direct_writes
   , plsexec_time
   , javexec_time
   , (select max(DBMS_LOB.SUBSTR(sql_text, 3800)) from dba_hist_sqltext st where st.sql_id = sub.sql_id) sql_text
   , (select max(name) from dba_hist_sqltext st, audit_actions aa where st.sql_id = sub.sql_id and aa.action = st.command_type)   command_type_name
   , min_time
   , max_time
   , min_snap_id
   , max_snap_id
   , sql_profile
   , (select nvl(min(sql_plan_baseline), 'none') from v$sql sql where sql.sql_id  = sub.sql_id and sql.plan_hash_value = sub.plan_hash_value) Baseline_plan_name -- does not work for 10g
from
   ( -- sub to sort before rownum
     select
        sql_id
        , plan_hash_value
        , round(sum(elapsed_time_delta)/1000000) as elapsed_sec
        , round(sum(cpu_time_delta)    /1000000) as cpu_sec 
        , round(sum(plsexec_time_delta)/1000000) as plsql_sec 
        , sum(executions_delta) as executions
        , sum(buffer_gets_delta) as buffer_gets      
        , sum(sharable_mem) as sharable_mem
        , sum(loaded_versions) as loaded_versions
        , sum(version_count) as version_count
        , max(module) as module 
        , sum(fetches_delta) as fetches
        , sum(end_of_fetch_count_delta) as end_of_fetch_count
        , sum(sorts_delta) as sorts
        , sum(px_servers_execs_delta) as px_servers_execs
        , sum(loads_delta) as loads
        , sum(invalidations_delta) as invalidations
        , sum(parse_calls_delta) as parse_calls
        , sum(disk_reads_delta) as disk_reads
        , sum(rows_processed_delta) as rows_processed
        , sum(iowait_delta) as iowait
        , sum(clwait_delta) as clwait
        , sum(apwait_delta) as apwait
        , sum(ccwait_delta) as ccwait
        , sum(iowait_delta) + sum(clwait_delta) + sum(apwait_delta) + sum(ccwait_delta) as tot_wait
        , sum(direct_writes_delta) as direct_writes
        , sum(plsexec_time_delta) as plsexec_time
        , sum(javexec_time_delta) as javexec_time
        , max(parsing_schema_name) as schema
        , max(snap.end_INTERVAL_TIME) max_time
        , min(snap.end_INTERVAL_TIME) min_time
        , min(stat.snap_id) min_snap_id
        , max(stat.snap_id) max_snap_id
        , min(nvl(sql_profile, 'none')) sql_profile     
     from
        dba_hist_snapshot snap
        , dba_hist_sqlstat stat
     where 1=1
          and nvl(:order_by, 'GET') like '%' 
          and snap.dbid = stat.dbid
          and snap.instance_number = stat.instance_number
          and snap.snap_id = stat.snap_id
          and snap.snap_id between nvl(:start_snap_id, snap.snap_id) and nvl(:end_snap_id, snap.snap_id)
          and nvl(parsing_schema_name,'%') like nvl(upper(:username), nvl(parsing_schema_name,'%')  )
          and sql_id = nvl(:sql_id, sql_id)
          and nvl(plan_hash_value,0) = nvl(:plan_hash_value, nvl(plan_hash_value,0))
          and nvl(module,'x') like nvl(:module, nvl(module,'x'))
          and stat.instance_number = nvl(:inst_id, stat.instance_number)
          and decode(:days_back_only_Y_N,'Y', end_INTERVAL_TIME, trunc(sysdate-:days_back) ) >= trunc(sysdate-:days_back)
          and (trunc(begin_INTERVAL_TIME, 'MI') >=  to_date(nvl(:sam_tm_str_MM_DD_YYYY_HH24_MI, to_char(begin_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI') 
              and trunc(end_interval_time, 'MI') <= to_date(nvl(:sam_tm_end_MM_DD_YYYY_HH24_MI, to_char(end_interval_time, 'MM_DD_YYYY_HH24_MI')),'MM_DD_YYYY_HH24_MI'))
          and (to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour, 0) and  nvl(:end_hour, 24) 
            or to_number(to_char(begin_INTERVAL_TIME, 'HH24')) between nvl(:begin_hour2, nvl(:begin_hour, 0)) and  nvl(:end_hour2, nvl(:end_hour, 24)))
group by sql_id, plan_hash_value --, force_matching_signature  -- , stat.instance_number
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets 
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then case when executions <> 0 then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
   ) sub
where 1=1
  and rownum <= :top_n
) main_query
where 1=1
  and nvl(upper(sql_text), '%') like nvl(upper(:sql_text), '%')
  and nvl(command_type_name, 'x') like nvl(:command_type_name, nvl(command_type_name, 'x'))
order by 
  case 
       when nvl(:order_by, 'GET') in ('ELAP' , '1') then elapsed_sec 
       when nvl(:order_by, 'GET') in ('CPU'  , '2') then cpu_sec 
       when nvl(:order_by, 'GET') in ('IO'   , '3') then iowait 
       when nvl(:order_by, 'GET') in ('GET'  , '4') then buffer_gets  -- essentially an overall workload ordering
       when nvl(:order_by, 'GET') in ('READ' , '5') then disk_reads 
       when nvl(:order_by, 'GET') in ('EXEC' , '6') then executions 
       when nvl(:order_by, 'GET') in ('PARSE', '7') then parse_calls 
       when nvl(:order_by, 'GET') in ('MEM'  , '8') then sharable_mem 
       when nvl(:order_by, 'GET') in ('VERS' , '9') then version_count 
       when nvl(:order_by, 'GET') in ('ELAP_EXEC' , '10') then case when executions <> 0 then elapsed_sec/(executions*decode(px_servers_execs, 0, 1, px_servers_execs)) else elapsed_sec end 
       when nvl(:order_by, 'GET') in ('SNAP' , '11') then min_snap_id 
       else buffer_gets
  end desc
;