Last active
March 17, 2021 10:05
-
-
Save nielskrijger/5419516 to your computer and use it in GitHub Desktop.
This is an SQL query to fetch several load indicators for current session on Oracle DB.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
sess_time AS ( | |
SELECT sid, ROUND(SUM(value) / 1000, 0) AS milliseconds | |
FROM gv$sess_time_model | |
GROUP BY sid), | |
sess_io AS ( | |
SELECT sid, block_gets + consistent_gets + physical_reads + block_changes + consistent_changes + optimized_physical_reads AS io# | |
FROM gv$sess_io io) | |
SELECT | |
p.spid, -- The system process identifier | |
s.sid, -- The session identifier | |
s.serial#, | |
/* | |
Status: | |
- ACTIVE - Session currently executing SQL | |
- INACTIVE | |
- KILLED - Session marked to be killed | |
- CACHED - Session temporarily cached for use by Oracle*XA | |
- SNIPED - Session inactive, waiting on the client | |
*/ | |
s.status, | |
s.machine, | |
s.username, | |
s.osuser, | |
s.program, -- The program in process | |
/* | |
The blocking session status indicates whether there is a blocking session. Values are: | |
- VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns | |
- NO HOLDER - there is no session blocking this session | |
- NOT IN WAIT - this session is not in a wait | |
- UNKNOWN - the blocking session is unknown | |
*/ | |
s.blocking_session_status, | |
ROUND((SYSDATE - s.logon_time) * 24, 1) AS "HOURS LOGGED ON", | |
ROUND(sess_io.io# / (SYSDATE - s.logon_time) / 24, 0) AS "AVG HOURLY IO#", | |
ROUND(sess_time.milliseconds / (SYSDATE - s.logon_time) / 24, 0) AS "AVG HOURLY PROCESS TIME (ms)", | |
ROUND(p.pga_used_mem / 1024, 0) AS "PGA USED MEM (kB)" -- Program global area memory space usage in KB | |
FROM | |
gv$session s, | |
sess_time, | |
gv$process p, | |
sess_io | |
WHERE | |
s.paddr = p.addr | |
AND sess_time.sid = s.sid | |
AND sess_io.sid = s.sid | |
AND s.type != 'BACKGROUND' -- Exclude BACKGROUND processes, usually they just clutter the result | |
ORDER BY | |
s.status ASC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
ORA-01476: "divisor is equal to zero"