set linesize 300 pagesize 200 column statname for a35 heading 'statistic' truncate column value for 999,999,999,999 heading 'value' col sid_serial for a20 col kill for a15 SELECT ''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,ss.username username, sn.name statname, st.value value ,sql_id FROM gv$sesstat st, v$statname sn, gv$session ss WHERE --st.sid = user_sid -- AND sn.name IN ('CPU used by this session', 'OS Wait-cpu (latency) time','session logical reads', 'physical reads') AND sn.statistic# = st.statistic# and ss.sid = st.sid and ss.inst_id = st.inst_id and ss.USERNAME is not null and ss.sql_id is not null ORDER BY value DESC ;
===
set pages 1000 lines 1000 col OSPID for a06 col SID for 99999 col SERIAL# for 999999 col SQL_ID for a14 col USERNAME for a15 col PROGRAM for a23 col MODULE for a18 col OSUSER for a10 col MACHINE for a25 col EVENT for a27 select * from ( select p.spid "ospid", (se.SID),ss.serial#,ss.SQL_ID,ss.con_id,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,ss.BLOCKING_SESSION blk_sid,ss.BLOCKING_SESSION_STATUS blk_status, se.VALUE/100 cpu_usage_sec,ss.event from v$session ss, v$sesstat se, v$statname sn, v$process p where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%' and se.SID = ss.SID --and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.VALUE desc);
SQL Wise CPU Usage
col cpu_usage_sec form 99990 heading "CPU in Seconds" select * from ( select (se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_sec from v$session ss,v$sesstat se,v$statname sn, v$process p, v$sql q where se.STATISTIC# = sn.STATISTIC# AND ss.sql_address = q.address AND ss.sql_hash_value = q.hash_value and NAME like '%CPU used by this session%' and se.SID = ss.SID and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.VALUE desc); set lines 1000 col OSPID for a06 col SID for 99999 col SERIAL# for 999999 col SQL_ID for a14 col USERNAME for a15 col PROGRAM for a20 col MODULE for a18 col OSUSER for a10 col MACHINE for a25 col kill for a17 select * from ( select --ss.inst_id, ''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill, p.spid "ospid", (se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status, se.VALUE/100 cpu_usage_seconds from gv$session ss, gv$sesstat se, gv$statname sn, gv$process p where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%' and se.SID = ss.SID --and ss.username !='SYS' --and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.VALUE desc) where rownum <16; set pages 1000 lines 1000 col ospid for a06 col sid for 99999 col serial# for 999999 col sql_id for a14 col username for a15 col program for a23 col module for a18 col osuser for a10 col machine for a25 select * from ( select p.spid "ospid", (se.sid),ss.serial#,ss.sql_id,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.machine,ss.status,ss.blocking_session blk_sid,ss.blocking_session_status blk_status, se.value/100 cpu_usage_sec,ss.event from v$session ss,v$sesstat se,v$statname sn,v$process p where 1=1 and se.statistic# = sn.statistic# and name like '%CPU used by this session%' and se.SID = ss.SID -- and ss.username !='SYS' --and ss.status='ACTIVE' --and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.value desc) ;
set pages 500 lines 500 col sid for a05 col serial for a06 col program for a15 col event for a28 col username for a15 col sql_text for a65 select substr(v2.sid,1,5) "SID",substr(v2.serial#,1,5) "SERIAL",substr(v2.username,1,15) "Username" ,substr(program,1,45) "Program",v2.event event,v1.sql_id,sql_text from gv$sqlarea v1,gv$session v2 where v1.address= v2.sql_address and v1.hash_value = v2.sql_hash_value and v1.inst_id = v2.inst_id order by 1 asc ;
set pages 100
set lines 1000
col SPID heading 'PID' for a06
col SID for 99999
col SERIAL# heading 'serial' for 999999
col SQL_ID for a14
col USERNAME for a10
col PROGRAM for a20
col MODULE for a18
col OSUSER for a07
col MACHINE for a20
select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event
from gv$session s, gv$process p
where s.paddr=p.addr
and p.spid=&spid;
====
| SET LINESIZE 200 | |
| SET PAGESIZE 200 | |
| COLUMN metric_name FORMAT a25 | |
| COLUMN metric_unit FORMAT a20 | |
| COLUMN awr_cpu_usage FORMAT a13 | |
| ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; | |
| SELECT * | |
| FROM ( | |
| SELECT instance_number | |
| , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id | |
| , snap_id second_snap_id | |
| , begin_time | |
| , end_time | |
| , metric_name | |
| , metric_unit | |
| , ROUND(average, 2) || '%' awr_cpu_usage | |
| FROM dba_hist_sysmetric_summary | |
| WHERE metric_name = 'Host CPU Utilization (%)' | |
| ORDER BY instance_number | |
| , first_snap_id | |
| ) | |
| WHERE first_snap_id <> 0 | |
| ; INSTANCE_NUMBER FIRST_SNAP_ID SECOND_SNAP_ID BEGIN_TIME END_TIME METRIC_NAME METRIC_UNIT AWR_CPU_USAGE --------------- ------------- -------------- ------------------- ------------------- ------------------------- -------------------- ------------- 1 2569 2570 2023-04-22 06:59:41 2023-04-22 07:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 5.66% 1 2570 2571 2023-04-22 07:59:40 2023-04-22 08:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 3.86% 1 2571 2572 2023-04-22 08:59:40 2023-04-22 10:00:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 3.99% 1 2572 2573 2023-04-22 10:00:41 2023-04-22 11:00:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.24% 1 2573 2574 2023-04-22 11:00:41 2023-04-22 11:59:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.36% 1 2574 2575 2023-04-22 11:59:41 2023-04-22 12:59:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.7% 1 2575 2576 2023-04-22 12:59:41 2023-04-22 13:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.93% 204 rows selected. |
=====
col kill for a15
col CLIENT_INFO for a20
col USERNAME for a20
select * from
(
select username,''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,sid,sql_id,prev_sql_id,
round((cpu_usage/(
select sum(value) total_cpu_usage
from gv$sesstat t
inner join gv$session s on ( t.sid = s.sid )
inner join gv$statname n on ( t.statistic# = n.statistic# )
where n.name like '%CPU used by this session%'
and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
))*100,2) cpu_usage_per_cent,
module_info,client_info
from
(
select nvl(s.username,'Oracle Internal Proc.') username,s.sid,s.serial#,s.inst_id,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info,
sql_id,prev_sql_id
from gv$sesstat t
inner join gv$session s on ( t.sid = s.sid )
inner join gv$statname n on ( t.statistic# = n.statistic# )
where n.name like '%CPU used by this session%'
and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
) s1
)
order by cpu_usage_per_cent desc;
===
set linesize 500 col sql_text for a80 col cpu_usage_sec form 99990 heading "CPU in Seconds" select * from ( select (se.SID),q.sql_id,substr(q.sql_text,80) sql_text,ss.module,ss.status,se.VALUE/100 cpu_usage_sec from v$session ss,v$sesstat se, v$statname sn, v$process p, v$sql q where se.STATISTIC# = sn.STATISTIC# AND ss.sql_address = q.address AND ss.sql_hash_value = q.hash_value and NAME like '%CPU used by this session%' and se.SID = ss.SID --and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 order by se.VALUE desc);
=======
set linesize 300
col day for a11
select
to_char(begin_time,'DD-MON-YY') Day,
round(max(decode(to_char(begin_time,'HH24'),'00',maxval,NULL)),2) "00",
round(max(decode(to_char(begin_time,'HH24'),'01',maxval,NULL)),2) "01",
round(max(decode(to_char(begin_time,'HH24'),'02',maxval,NULL)),2) "02",
round(max(decode(to_char(begin_time,'HH24'),'03',maxval,NULL)),2) "03",
round(max(decode(to_char(begin_time,'HH24'),'04',maxval,NULL)),2) "04",
round(max(decode(to_char(begin_time,'HH24'),'05',maxval,NULL)),2) "05",
round(max(decode(to_char(begin_time,'HH24'),'06',maxval,NULL)),2) "06",
round(max(decode(to_char(begin_time,'HH24'),'07',maxval,NULL)),2) "07",
round(max(decode(to_char(begin_time,'HH24'),'08',maxval,NULL)),2) "08",
round(max(decode(to_char(begin_time,'HH24'),'09',maxval,NULL)),2) "09",
round(max(decode(to_char(begin_time,'HH24'),'10',maxval,NULL)),2) "10",
round(max(decode(to_char(begin_time,'HH24'),'11',maxval,NULL)),2) "11",
round(max(decode(to_char(begin_time,'HH24'),'12',maxval,NULL)),2) "12",
round(max(decode(to_char(begin_time,'HH24'),'13',maxval,NULL)),2) "13",
round(max(decode(to_char(begin_time,'HH24'),'14',maxval,NULL)),2) "14",
round(max(decode(to_char(begin_time,'HH24'),'15',maxval,NULL)),2) "15",
round(max(decode(to_char(begin_time,'HH24'),'16',maxval,NULL)),2) "16",
round(max(decode(to_char(begin_time,'HH24'),'17',maxval,NULL)),2) "17",
round(max(decode(to_char(begin_time,'HH24'),'18',maxval,NULL)),2) "18",
round(max(decode(to_char(begin_time,'HH24'),'19',maxval,NULL)),2) "19",
round(max(decode(to_char(begin_time,'HH24'),'20',maxval,NULL)),2) "20",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "21",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "22",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "23"
from dba_hist_sysmetric_SUMMARY A
where BEGIN_TIME > sysdate - 7
and metric_name like 'Host CPU Utilization%'
--and A.METRIC_NAME in('Average Active Sessions')
group by to_char(begin_time,'DD-MON-YY')
order by 1
;
DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
07-JUL-23 33.5 40.53 47.96 45.15 43.84 43.65 41.18 39.09 38.59 38.64 31.5 47.73 49.27 54.92 54.92 54.92
08-JUL-23 25.24 16.35 17.24 19.12 18.15 100 20.54 19.42 23.73 25.46 31.01 31.27 29.57 33.55 30.2 30.01 26.34 28.52 29.12 40.59 53.57 43.98 43.98 43.98
https://github.com/khailey-zz/ashmasters/commit/2963230a03e8b8fbc8b288e8829c08066206907d
set linesize 400
with AASIO as (
select
class, sum(AAS) AAS, begin_time, end_time
from (
select
decode(n.wait_class,'User I/O','User I/O',
'Commit','Commit',
'Wait') CLASS,
sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS,
-- normalize the begin/end times between instances
-- not perfect but I think close enough for general trends
-- should never be more than 2 minutes variance assuming 3+ instances
-- 1 minute variance for 2 instances
min(BEGIN_TIME) over (partition by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')) begin_time ,
min(END_TIME) over (partition by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')) end_time
from gv$waitclassmetric m,
gv$system_wait_class n
where m.wait_class_id=n.wait_class_id
and n.wait_class != 'Idle'
and n.inst_id = m.inst_id
group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME
order by begin_time
)
group by class, begin_time, end_time
),
CORES as (
select cpu_core_count from dba_cpu_usage_statistics where timestamp= (select max(timestamp) from dba_cpu_usage_statistics)
),
AASSTAT as (
select class, aas, begin_time, end_time from aasio
union
select 'CPU_ORA_CONSUMED' CLASS,
round(sum(value)/100,3) AAS,
min(BEGIN_TIME) begin_time,
max(END_TIME) end_time
from gv$sysmetric
where metric_name='CPU Usage Per Sec'
and group_id=2
group by metric_name
union
select 'CPU_OS' CLASS ,
round((prcnt.busy*parameter.cpu_count)/100,3) AAS,
BEGIN_TIME ,
END_TIME
from
( select sum(value) busy, min(BEGIN_TIME) begin_time,max(END_TIME) end_time from gv$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
( select sum(value) cpu_count from gv$parameter where name='cpu_count' ) parameter
union
select
'CPU_ORA_DEMAND' CLASS,
nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,
cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,
cast(max(SAMPLE_TIME) as date) END_TIME
from gv$active_session_history ash
where SAMPLE_TIME >= (select min(BEGIN_TIME) begin_time from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )
and SAMPLE_TIME < (select max(END_TIME) end_time from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )
)
select
sysdate timestamp,
to_char(BEGIN_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_TIME,
to_char(END_TIME,'YYYY-MM-DD HH24:MI:SS') END_TIME,
cpu.cpu_core_count cores,
( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +
CPU_ORA_CONSUMED +
decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,
decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,
CPU_ORA_CONSUMED CPU_ORA,
decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,
COMMIT,
READIO,
WAIT
from (
select
min(BEGIN_TIME) BEGIN_TIME,
max(END_TIME) END_TIME,
sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,
sum(decode(CLASS,'CPU_ORA_DEMAND' ,AAS,0)) CPU_ORA_DEMAND,
sum(decode(CLASS,'CPU_OS' ,AAS,0)) CPU_OS,
sum(decode(CLASS,'Commit' ,AAS,0)) COMMIT,
sum(decode(CLASS,'User I/O' ,AAS,0)) READIO,
sum(decode(CLASS,'Wait' ,AAS,0)) WAIT
from AASSTAT
)
, cores cpu
/
No comments:
Post a Comment