PROMPT Snapshots session statistics and reports difference between 2 snapshots.

PROMPT Requires 'GRANT SELECT ON v_$sesstat TO <tascha_schema>;'
PROMPT Requires 'GRANT SELECT ON v_$statname TO <tascha_schema>;'
PROMPT Requires 'GRANT SELECT ON v_$session TO <tascha_schema>;'

CONN &&tascha_schema/&&tascha_password

CREATE TABLE sesstat_snap AS
SELECT  TO_NUMBER(1)	snap_no
,	s.sid
,	s.statistic#
,	n.name		stat_name
,	s.value
FROM	v$sesstat s
,	v$statname n
WHERE	1 = 2
AND	s.statistic# = n.statistic#;

CREATE OR REPLACE PROCEDURE sesstats(	I_stat_name	IN VARCHAR2
				,	I_which 	IN NUMBER DEFAULT 1)
AS
	PROCEDURE write_row(	I_col1	IN VARCHAR2
			,	I_col2	IN VARCHAR2
			,	I_col3	IN VARCHAR2
			,	I_col4	IN VARCHAR2)
	IS
	BEGIN
		tascha_logger.info(	RPAD(I_col1,10)
				||' '||	RPAD(I_col2,15)
				||' '||	RPAD(I_col3,25)
				||' '||	LPAD(I_col4,10));
	END write_row;
BEGIN
	IF I_which = 1 THEN
		DELETE	sesstat_snap
		WHERE	stat_name = I_stat_name;
	END IF;

	INSERT INTO sesstat_snap
		(snap_no
	,	sid
	,	statistic#
	,	stat_name
	,	value)
	SELECT	I_which
	, 	s.sid
	,	s.statistic#
	,	n.name
	,	s.value
	FROM	v$sesstat s
	,	v$statname n
	WHERE	s.value <> 0
	AND	s.statistic# = n.statistic#
	AND	n.name = I_stat_name;

	IF I_which = 2 THEN
		tascha_logger.info('Top sessions for statistic "'||I_stat_name||'"');
		tascha_logger.info(' ');
		write_row(	'SID'
			,	'OS USER'
			,	'PROGRAM'
			,	'VALUE');
		write_row(	RPAD('=',10,'=')
			,	RPAD('=',15,'=')
			,	RPAD('=',25,'=')
			,	RPAD('=',10,'='));

		FOR R_stats IN (SELECT	sid
				,	program
				,       osuser
				,	snap_delta
				FROM	(
					SELECT  s.sid
					,       u.program
					,       u.osuser
					,       NVL(DECODE(s.snap_no,1,s.value,0),0)		first_snap
					,       NVL(DECODE(s.snap_no,2,s.value,0),0)		second_snap
					,       NVL(DECODE(s.snap_no,2,s.value,0),0)
					-       NVL(DECODE(s.snap_no,1,s.value,0),0)		snap_delta
					,       u.client_info
					FROM    sesstat_snap s
					,       v$session u
					WHERE   s.stat_name = I_stat_name
					AND     s.value > 0
					AND     u.sid = s.sid
				)
				WHERE	snap_delta > 0
				ORDER BY snap_delta DESC
			)
		LOOP
			write_row(	R_stats.sid
				,	R_stats.osuser
				,	R_stats.program
				,	TO_CHAR(R_stats.snap_delta));
		END LOOP;
	END IF;
END sesstats;
/