CPU Utilization
Follow below queries to find CPU utilization:
1)
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,14) session_machine
, sstat.value cpu_value
FROM
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat.sid
AND statname.statistic# = sstat.statistic#
AND statname.name = 'CPU used by this session'
ORDER BY cpu_value DESC
/
2)
With SID from above run below query:
select sid,serial#,program,sql_hash_value,logon_time,status from v$session where sid in (2576,3205,2889);
3)
With sql_hash_value find out the query:
set long 999999999
select sql_text from v$sqltext where hash_value='2690999600' order by piece;
==============================================
set lines 375 pages 375
col sql_text for a108
select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 21
/
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
1)
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,14) session_machine
, sstat.value cpu_value
FROM
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat.sid
AND statname.statistic# = sstat.statistic#
AND statname.name = 'CPU used by this session'
ORDER BY cpu_value DESC
/
2)
With SID from above run below query:
select sid,serial#,program,sql_hash_value,logon_time,status from v$session where sid in (2576,3205,2889);
3)
With sql_hash_value find out the query:
set long 999999999
select sql_text from v$sqltext where hash_value='2690999600' order by piece;
==============================================
set lines 375 pages 375
col sql_text for a108
select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 21
/
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
Comments
Post a Comment