Home / Database Scripts / How to Find CPU Usage for a Session

How to Find CPU Usage for a Session

How to Find CPU Usage for a Session

SET linesize 145
SET pagesize 9999
COLUMN sid format 9999 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’
prompt +—————————————————-+
prompt | USER sessions ordered BY cpu |
prompt +—————————————————-+
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

About Syed Saad Ali

With 13 years of experience as a certified and skilled Oracle Database Administrator, I possess the expertise to handle various levels of database maintenance tasks and proficiently perform Oracle updates. Throughout my career, I have honed my analytical abilities, enabling me to swiftly diagnose and resolve issues as they arise. I excel in planning and executing special projects within time-sensitive environments, showcasing exceptional organizational and time management skills. My extensive knowledge encompasses directing, coordinating, and exercising authoritative control over all aspects of planning, organization, and successful project completions. Additionally, I have a strong aptitude for resolving customer relations matters by prioritizing understanding and effective communication. I am adept at interacting with customers, vendors, and management, ensuring seamless communication and fostering positive relationships.

Check Also

Application Report Queries


Leave a Reply