Session Statistics

Onur ARDAHANLI

1 month ago

In today’s article, I will be giving you information about active, inactive and other Session Statistics in the database.

Active Session Statistics

1. We can collect active session statistics in the database as follows.

	WITH sessiondata AS
	(SELECT snaptime, dbtime / 1000000 dbtime,
	        (EXTRACT(DAY FROM duration) * 86400) + (EXTRACT(HOUR FROM duration) * 3600) + (EXTRACT(MINUTE FROM duration) * 60) + EXTRACT(SECOND FROM duration) duration
	   FROM
	       (SELECT s.begin_interval_time snaptime,
	               s.begin_interval_time - LAG(s.begin_interval_time) OVER (ORDER BY s.begin_interval_time) duration,
	               tm.value - LAG(tm.value) OVER (ORDER BY s.begin_interval_time) dbtime
	          FROM dba_hist_snapshot s,
	               dba_hist_sys_time_model tm
	         WHERE s.snap_id = tm.snap_id
	           AND s.instance_number = tm.instance_number
	           AND s.dbid = tm.dbid
	           AND s.instance_number = (select instance_number from v$instance)
	           AND s.dbid = (select dbid from v$database)
	           AND tm.stat_name = 'DB time'
	           AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE)-21
	       )
	)
	SELECT * FROM (
	SELECT TO_CHAR(snaptime,'YYYY-MM-DD') "Day", ROUND(SUM(duration)) "Duration(sn) between 09-18", ROUND(SUM(dbtime)) "Db Time(sn)", ROUND(SUM(dbtime) / SUM(duration), 2) "Active Sessions/sn"
	  FROM sessiondata
	 WHERE dbtime IS NOT NULL AND dbtime>0
	   AND TO_CHAR(snaptime,'HH24:MI') >= '09:00' AND TO_CHAR(snaptime,'HH24:MI') <= '18:00'
	GROUP BY TO_CHAR(snaptime,'YYYY-MM-DD')
	ORDER BY 1 DESC
	) WHERE ROWNUM < 21;

WITH sessiondata AS

(SELECT snaptime, dbtime / 1000000 dbtime,

(EXTRACT(DAY FROM duration) * 86400) + (EXTRACT(HOUR FROM duration) * 3600) + (EXTRACT(MINUTE FROM duration) * 60) + EXTRACT(SECOND FROM duration) duration

FROM

(SELECT s.begin_interval_time snaptime,

s.begin_interval_time - LAG(s.begin_interval_time) OVER (ORDER BY s.begin_interval_time) duration,

tm.value - LAG(tm.value) OVER (ORDER BY s.begin_interval_time) dbtime

FROM dba_hist_snapshot s,

dba_hist_sys_time_model tm

WHERE s.snap_id = tm.snap_id

AND s.instance_number = tm.instance_number

AND s.dbid = tm.dbid

AND s.instance_number = (select instance_number from v$instance)

AND s.dbid = (select dbid from v$database)

AND tm.stat_name = 'DB time'

AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE)-21

)

SELECT * FROM (

SELECT TO_CHAR(snaptime,'YYYY-MM-DD') "Day", ROUND(SUM(duration)) "Duration(sn) between 09-18", ROUND(SUM(dbtime)) "Db Time(sn)", ROUND(SUM(dbtime) / SUM(duration), 2) "Active Sessions/sn"

FROM sessiondata

WHERE dbtime IS NOT NULL AND dbtime>0

AND TO_CHAR(snaptime,'HH24:MI') >= '09:00' AND TO_CHAR(snaptime,'HH24:MI') <= '18:00'

GROUP BY TO_CHAR(snaptime,'YYYY-MM-DD')

ORDER BY 1 DESC

) WHERE ROWNUM < 21;

Active – Inactive Session Statistics (Hourly)

1. We can obtain hourly MAX and AVG Session statistics in the database with the query below.

	SQL>
	SELECT   TO_CHAR(begin_interval_time,'YYYYMMDD HH24') hourly, MAX (VALUE) max_sessions, TRUNC (AVG (VALUE)) avg_sessions
	    FROM   dba_hist_sysstat ss, dba_hist_snapshot sn
	   WHERE   ss.stat_name LIKE '%logons curr%' AND ss.snap_id = sn.snap_id
	GROUP BY   TO_CHAR(begin_interval_time,'YYYYMMDD HH24') 
	ORDER BY   1 DESC;

SQL>

SELECT TO_CHAR(begin_interval_time,'YYYYMMDD HH24') hourly, MAX (VALUE) max_sessions, TRUNC (AVG (VALUE)) avg_sessions

FROM dba_hist_sysstat ss, dba_hist_snapshot sn

WHERE ss.stat_name LIKE '%logons curr%' AND ss.snap_id = sn.snap_id

GROUP BY TO_CHAR(begin_interval_time,'YYYYMMDD HH24')

ORDER BY 1 DESC;

Number of Sessions that are Inactive for a Long Time (1 hour)

1. The number of INACTIVE sessions longer than one hour and the program they came from can be found below.

	SQL>
	col program for a45
	col INACTIVE_PROGRAMS FOR A40
	select s.program,count(s.program) Inactive_Sessions_from_1Hour
	from gv$session s,v$process p
	where     p.addr=s.paddr  AND
	s.status='INACTIVE'
	and s.last_call_et > (3600)
	group by s.program
	order by 2 desc;

SQL>

col program for a45

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where p.addr=s.paddr AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

6 rows selected.

2. INACTIVE sessions detailis more than 1 hour.

	SQL>
	set pagesize 40
	col INST_ID for 99
	col spid for a10
	set linesize 150
	col PROGRAM for a10
	col action format a10
	col logon_time format a16
	col module format a13
	col cli_process format a7
	col cli_mach for a15
	col status format a10
	col username format a10
	col last_call_et_Hrs for 9999.99
	col sql_hash_value for 9999999999999col username for a10
	set linesize 152
	set pagesize 80
	col "Last SQL" for a60
	col elapsed_time for 999999999999
	select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
	from gv$session s, gv$sqlarea t,gv$process p
	where s.sql_address =t.address and
	s.sql_hash_value =t.hash_value and
	p.addr=s.paddr and
	s.status='INACTIVE'
	and s.last_call_et > (3600)
	order by last_call_et;

SQL>

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

Long-running database operations

1. We can list the long-running operations in the database as follows.

	SQL>
	set linesize 1000
	set pagesize 40
	col USERNAME for a10
	col OPERATION for a20
	col OBJECT for a32
	col ELAPSED SECOND for a10
	col START_TIME for a17
	SELECT a.sid,
	         a.serial#,
	         b.username,
	         opname OPERATION,
	         target OBJECT,
	         TRUNC (elapsed_seconds, 5) "Elap_Sec(s)",
	         TO_CHAR (start_time, 'DD/MM/YY HH24:MI:SS') start_time,
	         ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
	    FROM v$session_longops a, v$session b
	   WHERE     a.sid = b.sid
	         AND b.username NOT IN ('SYSTEM')
	         AND totalwork > 0
ORDER BY elapsed_seconds;

SQL>

set linesize 1000

set pagesize 40

col USERNAME for a10

col OPERATION for a20

col OBJECT for a32

col ELAPSED SECOND for a10

col START_TIME for a17

SELECT a.sid,

a.serial#,

b.username,

opname OPERATION,

target OBJECT,

TRUNC (elapsed_seconds, 5) "Elap_Sec(s)",

TO_CHAR (start_time, 'DD/MM/YY HH24:MI:SS') start_time,

ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"

FROM v$session_longops a, v$session b

WHERE a.sid = b.sid

AND b.username NOT IN ('SYSTEM')

AND totalwork > 0

ORDER BY elapsed_seconds;

2. We can query the estimated time when the running queries will finish by adding another column to the query above.

For example, for RMAN, this query could be as follows.

	SQL>
	SELECT s.sid, ROUND(sl.elapsed_seconds/60) gecen_dak, ROUND(sl.time_remaining/60) kalan_dak, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
	FROM v$session s, v$session_longops sl
	WHERE s.sid = sl.sid
	AND s.serial# = sl.serial#
	and sl.opname like '%RMAN%'
	and totalwork <> 0;

SQL>

SELECT s.sid, ROUND(sl.elapsed_seconds/60) gecen_dak, ROUND(sl.time_remaining/60) kalan_dak, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct

FROM v$session s, v$session_longops sl

WHERE s.sid = sl.sid

AND s.serial# = sl.serial#

and sl.opname like '%RMAN%'

and totalwork <> 0;

CPU Usage Of The User

1. Total CPU usage of ACTIVE users in the database can be displayed as follows.

	SQL>
	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;

SQL>

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;

12 rows selected.