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.

An AWR/ statspack report would give you the amount of CPU that was consumed by each query over a period of time. That seems more useful than the instantaneous CPU consumption-- are you sure you need/ want instantaneous CPU consumption? If you really want to match the current CPU load, you could get the process from `v$session` and map that to the results from `top` via an external table. — Justin Cave, Nov 12 '19 at 15:01
@Justin cave, Thanks for the update. yes, we can get the overall consumption using AWR reports. But here i am looking for the CPU utilization of each query at any given point of time. As suggested by you, we can get the information by mapping the process from "v$session" and "top". But in our case, the OS is managed by a different team, and as a DBA i don't have access to OS. Is there any query to get the needed information from database itself? — srinivas, Nov 12 '19 at 15:20
Surely you can coordinate with the team that administers the OS though (just as users coordinate with your team to get access to the database without needing to be a DBA). You can call out to the operating system using an external table definition, a Java stored procedure, or a `dbms_scheduler` job-- those will run as whatever user the Oracle database runs as which will have access to the operating system. I don't see a way to do this without making an operating system call since Oracle doesn't update its data dictionary tables while a query is being executed. — Justin Cave, Nov 12 '19 at 15:28
@Justin Cave, Thank you. do you have any reusable script to create an external table to fetch "top" information?? It will be very helpful if you can share the script if you already have it handy. — srinivas, Nov 13 '19 at 08:26

score 0 · Accepted Answer · answered Jan 14 '20 at 17:13

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
;

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

1 Answers1

Linked