How much CPU a session consuming at a given time in oracle....
https://stackoverflow.com/questions/58820965/how-much-cpu-a-session-consuming-at-a-given-time-in-oracle
Roger Cornejo
-- uncomment based on your requirement
set linesize 2000
var order_by varchar2(10);
-- begin :order_by := 'ELAP'; end;
--/
-- begin :order_by := 'IO'; end;
--/
--begin :order_by := 'CPU'; end;
-- /
--begin :order_by := 'GET'; end;
-- /
begin :order_by := 'READ'; end;
/
--begin :order_by := 'EXEC'; end;
-- /
--begin :order_by := 'PARSE'; end;
--/
--begin :order_by := 'MEM'; end;
/
--begin :order_by := 'VERS'; end;
-- /
--begin :order_by := 'ELAP_EXEC'; end;
--/
--begin :order_by := 'SNAP'; end;
-- /
set numf 9999999999999999999
col "Tot Wait" for 99999999999999999
col IOWAIT for 99999999999999999
col CLWAIT for 99999999999999999
col BASELINE_PLAN_NAME for a15
col SCHEMA for a20
col sql_text for a70 wrap
col "Time Per Exec" for a10
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, 'dd/mm/yyyy HH24:MI:SS') min_time
, to_char(max_time ,'dd/mm/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 <= 10
) 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
;
ORD ORDER_BY SCHEMA SQL_ID Plan Hash Value PLAN_HASH_2 MONITOR_L Time Per E Exec-utions Clock Time px servers execs SQL_TEXT Buffer Gets FETCHES rows processed rows per fetch end of fetch count SORTS disk reads Tot Wait IOWAIT CLWAIT APWAIT CCWAIT direct writes Elap-sed (Sec) CPU Sec PL/SQL sec pls exec time java exec time shar-able mem Buffer Gets per exec Fetches Gets per exec rows per exec sorts per exec disk reads per exec Tot Wait per exec iowait per exec clwait per exec apwait per exec ccwait per exec direct writes per exec Elap-sed (Sec) per exec CPU Sec per exec PL/SQL sec per exec pls exec time per exec java exec time per exec shar-able mem per exec Buffer Gets per row Fetches Gets per row rows per row sorts per row disk reads per row Tot Wait per row iowait per row clwait per row apwait per row ccwait per row direct writes per row Elap-sed (Sec) per row CPU Sec per row PL/SQL sec per row pls exec time per row java exec time per row shar-able mem per row loaded vers-ions ver-sion count LOADS invalid-ations parse calls MODULE COMMAND_TYPE_NAME MIN_TIME MAX_TIME Min Snap Id Max Snap Id SQL_PROFILE BASELINE_PLAN_N
-------------------- ------------- -------------------- ------------- -------------------- -------------------- --------- ---------- -------------------- -------------------------------------------- -------------------- ---------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------ ------------------ ------------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------- ------------------------ -------------------- -------------------- ----------------------- ----------------------- ---------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------------------- ----------------------- -------------------- -------------------- ---------------------- ---------------------- --------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------- ---------------------------- ------------------- ------------------- -------------------- -------------------- ---------------------------------------------------------------- ---------------
(:sig IS NULL AND
((:existingSQL IS NOT NULL AND :newSQL IS NOT NULL) OR
(:existingSQL IS NOT NULL AND
:newSQL IS NULL AND
