318 lines
12 KiB
SQL
318 lines
12 KiB
SQL
----------------------------------------------------------------------------------------
|
|
--
|
|
-- File name: cs_sessions_hist.sql
|
|
--
|
|
-- Purpose: Simple list all historical Sessions (all types and all statuses)
|
|
--
|
|
-- Author: Carlos Sierra
|
|
--
|
|
-- Version: 2023/02/06
|
|
--
|
|
-- Usage: Execute connected to CDB or PDB.
|
|
--
|
|
-- Example: $ sqlplus / as sysdba
|
|
-- SQL> @cs_sessions_hist.sql
|
|
--
|
|
-- Notes: Developed and tested on 12.1.0.2 and 19c.
|
|
--
|
|
---------------------------------------------------------------------------------------
|
|
--
|
|
@@cs_internal/cs_primary.sql
|
|
@@cs_internal/cs_cdb_warn.sql
|
|
@@cs_internal/cs_set.sql
|
|
@@cs_internal/cs_def.sql
|
|
@@cs_internal/cs_file_prefix.sql
|
|
--
|
|
DEF cs_script_name = 'cs_sessions_hist';
|
|
--
|
|
PRO
|
|
PRO Specify the number of days of snapshots to choose from
|
|
PRO
|
|
PRO Enter number of days: [{1}|0-365]
|
|
DEF num_days = '&1.'
|
|
UNDEF 1;
|
|
COL num_days NEW_V num_days NOPRI;
|
|
SELECT NVL('&&num_days.', '1') AS num_days FROM DUAL
|
|
/
|
|
--
|
|
@@cs_internal/&&cs_set_container_to_cdb_root.
|
|
COL sessions FOR 999,990;
|
|
COL snap_time NEW_V snap_time;
|
|
SELECT TO_CHAR(snap_time, '&&cs_datetime_full_format.') AS snap_time, COUNT(*) sessions
|
|
FROM &&cs_tools_schema..iod_session
|
|
WHERE &&cs_con_id. IN (1, con_id) AND snap_time > SYSDATE - TO_NUMBER('&&num_days.')
|
|
GROUP BY
|
|
snap_time
|
|
ORDER BY
|
|
snap_time
|
|
/
|
|
@@cs_internal/&&cs_set_container_to_curr_pdb.
|
|
--
|
|
PRO
|
|
PRO Enter Snap Time: [{&&snap_time.}]
|
|
DEF cs_snap_time = '&2.'
|
|
UNDEF 2;
|
|
COL cs_snap_time NEW_V cs_snap_time NOPRI;
|
|
SELECT NVL('&&cs_snap_time.', '&&snap_time.') AS cs_snap_time FROM DUAL
|
|
/
|
|
--
|
|
SELECT '&&cs_file_prefix._&&cs_script_name.' cs_file_name FROM DUAL;
|
|
--
|
|
@@cs_internal/&&cs_set_container_to_cdb_root.
|
|
--
|
|
@@cs_internal/cs_spool_head.sql
|
|
PRO SQL> @&&cs_script_name..sql "&&num_days." "&&cs_snap_time."
|
|
@@cs_internal/cs_spool_id.sql
|
|
--
|
|
PRO NUM_DAYS : "&&num_days." [{1}|0-365]
|
|
PRO SNAP_TIME : "&&cs_snap_time." [{&&snap_time.}]
|
|
--
|
|
COL status FOR A8 PRI;
|
|
COL last_call_et FOR 999,999,999,990 HEA 'LAST_CALL|ET_SECS';
|
|
COL logon_age FOR 999,999,990 HEA 'LOGON|AGE_SECS';
|
|
COL sid_serial FOR A12;
|
|
COL blocker FOR 9999990;
|
|
COL module_action_program FOR A50 TRUNC;
|
|
COL sql_text FOR A50 TRUNC;
|
|
COL pdb_name FOR A35 TRUNC;
|
|
COL timed_event FOR A60 HEA 'TIMED EVENT' TRUNC;
|
|
COL type FOR A10 TRUNC;
|
|
COL username FOR A20 TRUNC;
|
|
COL txn FOR A3;
|
|
COL last_call_time FOR A19;
|
|
COL logon_time FOR A19;
|
|
--
|
|
SET FEED ON;
|
|
PRO
|
|
PRO SESSIONS (as of &&cs_snap_time.)
|
|
PRO ~~~~~~~~
|
|
WITH
|
|
v_session AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */ * FROM &&cs_tools_schema..iod_session WHERE &&cs_con_id. IN (1, con_id) AND snap_time = TO_DATE('&&cs_snap_time.', '&&cs_datetime_full_format.')
|
|
),
|
|
sessions AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
con_id,
|
|
sid,
|
|
serial#,
|
|
CASE WHEN final_blocking_session_status = 'VALID' THEN final_blocking_session END AS blocker,
|
|
type,
|
|
status,
|
|
username,
|
|
paddr,
|
|
taddr,
|
|
logon_time,
|
|
last_call_et,
|
|
(TO_DATE('&&cs_snap_time.', '&&cs_datetime_full_format.') - logon_time) * 24 * 3600 AS logon_age,
|
|
COALESCE(sql_id, prev_sql_id) sql_id,
|
|
machine,
|
|
's:'||state||
|
|
CASE WHEN wait_class IS NOT NULL THEN ' w:'||wait_class END||
|
|
CASE WHEN event IS NOT NULL THEN ' - '||event END AS
|
|
timed_event,
|
|
CASE WHEN TRIM(module) IS NOT NULL THEN 'm:'||TRIM(module)||' ' END||
|
|
CASE WHEN TRIM(action) IS NOT NULL THEN 'a:'||TRIM(action)||' ' END||
|
|
CASE WHEN TRIM(program) IS NOT NULL THEN 'p:'||TRIM(program) END AS
|
|
module_action_program
|
|
FROM v_session
|
|
)
|
|
SELECT se.last_call_et,
|
|
(TO_DATE('&&cs_snap_time.', '&&cs_datetime_full_format.') - (se.last_call_et / 3600 / 24)) AS last_call_time,
|
|
se.logon_age,
|
|
se.logon_time,
|
|
se.sid||','||se.serial# sid_serial,
|
|
se.blocker,
|
|
se.type,
|
|
se.status,
|
|
se.username,
|
|
CASE WHEN se.taddr IS NOT NULL THEN 'TXN' END AS txn,
|
|
se.timed_event,
|
|
se.sql_id,
|
|
(SELECT sql_text FROM v$sql sq WHERE sq.sql_id = se.sql_id AND ROWNUM = 1) sql_text,
|
|
se.machine,
|
|
se.module_action_program,
|
|
c.name||'('||se.con_id||')' AS pdb_name
|
|
FROM sessions se,
|
|
v$containers c
|
|
WHERE c.con_id(+) = se.con_id
|
|
AND c.open_mode(+) = 'READ WRITE'
|
|
ORDER BY
|
|
se.last_call_et,
|
|
se.logon_age,
|
|
se.sid,
|
|
se.serial#
|
|
/
|
|
SET FEED OFF;
|
|
--
|
|
COL pdb FOR A35;
|
|
COL total_sessions FOR 99999999 HEA 'TOTAL|SESSIONS';
|
|
COL status_active FOR 999999 HEA 'STATUS|ACTIVE';
|
|
COL status_inactive FOR 99999999 HEA 'STATUS|INACTIVE';
|
|
COL type_user FOR 999999 HEA 'TYPE|USER';
|
|
COL type_background FOR 9999999999 HEA 'TYPE|BACKGROUND';
|
|
COL type_recursive FOR 9999999999 HEA 'TYPE|RECURSIVE';
|
|
COL user_active FOR 999999 HEA 'USER|ACTIVE';
|
|
COL user_inactive FOR 99999999 HEA 'USER|INACTIVE';
|
|
COL user_active_cpu FOR 999999 HEA 'USER|ACTIVE|ON_CPU';
|
|
COL user_active_waiting FOR 9999999 HEA 'USER|ACTIVE|WAITING';
|
|
COL user_scheduler FOR 999999999 HEA 'USER|ACTIVE|WAITING|SCHEDULER';
|
|
COL user_io FOR 99999999 HEA 'USER|ACTIVE|WAITING|USER_I/O';
|
|
COL user_application FOR 99999999999 HEA 'USER|ACTIVE|WAITING|APPLICATION';
|
|
COL user_concurency FOR 99999999999 HEA 'USER|ACTIVE|WAITING|CONCURRENCY';
|
|
COL user_commit FOR 9999999 HEA 'USER|ACTIVE|WAITING|COMMIT';
|
|
COL last_call_secs FOR 999,999,990 HEA 'LAST_CALL|SECONDS';
|
|
COL avg_last_call_secs FOR 999,999,990 HEA 'AVG_LAST_CALL|SECONDS';
|
|
--
|
|
COL pdb FOR A35;
|
|
COL total_sessions FOR 99999999 HEA 'TOTAL|SESSIONS';
|
|
COL status_active FOR 999999 HEA 'STATUS|ACTIVE';
|
|
COL status_inactive FOR 99999999 HEA 'STATUS|INACTIVE';
|
|
COL type_user FOR 999999 HEA 'TYPE|USER';
|
|
COL type_background FOR 9999999999 HEA 'TYPE|BACKGROUND';
|
|
COL type_recursive FOR 9999999999 HEA 'TYPE|RECURSIVE';
|
|
COL user_active FOR 999999 HEA 'USER|ACTIVE';
|
|
COL user_inactive FOR 99999999 HEA 'USER|INACTIVE';
|
|
COL user_active_cpu FOR 999999 HEA 'USER|ACTIVE|ON_CPU';
|
|
COL user_active_txn FOR 999999 HEA 'USER|ACTIVE|TXN';
|
|
COL user_inactive_txn FOR 999999 HEA 'USER|INACTIVE|TXN';
|
|
COL user_active_waiting FOR 9999999 HEA 'USER|ACTIVE|WAITING';
|
|
COL user_scheduler FOR 999999999 HEA 'USER|ACTIVE|WAITING|SCHEDULER';
|
|
COL user_io FOR 99999999 HEA 'USER|ACTIVE|WAITING|USER_I/O';
|
|
COL user_application FOR 99999999999 HEA 'USER|ACTIVE|WAITING|APPLICATION';
|
|
COL user_concurency FOR 99999999999 HEA 'USER|ACTIVE|WAITING|CONCURRENCY';
|
|
COL user_commit FOR 9999999 HEA 'USER|ACTIVE|WAITING|COMMIT';
|
|
COL last_call_secs FOR 999,999,990 HEA 'LAST_CALL|SECONDS';
|
|
COL avg_last_call_secs FOR 999,999,990 HEA 'AVG_LAST_CALL|SECONDS';
|
|
--
|
|
BREAK ON REPORT;
|
|
COMPUTE SUM LABEL 'TOTAL' OF total_sessions status_active status_inactive type_user type_background type_recursive user_active user_inactive user_active_txn user_inactive_txn user_active_cpu user_active_waiting user_scheduler user_io user_application user_concurency user_commit ON REPORT;
|
|
--
|
|
PRO
|
|
PRO MACHINE SUMMARY (as of &&cs_snap_time.)
|
|
PRO ~~~~~~~~~~~~~~~
|
|
WITH
|
|
all_sessions AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */ * FROM &&cs_tools_schema..iod_session WHERE &&cs_con_id. IN (1, con_id) AND snap_time = TO_DATE('&&cs_snap_time.', '&&cs_datetime_full_format.')
|
|
),
|
|
sessions
|
|
AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
machine,
|
|
COUNT(*) total_sessions,
|
|
SUM(CASE WHEN type = 'USER' THEN 1 ELSE 0 END) type_user,
|
|
SUM(CASE WHEN type = 'BACKGROUND' THEN 1 ELSE 0 END) type_background,
|
|
--SUM(CASE WHEN type = 'RECURSIVE' THEN 1 ELSE 0 END) type_recursive,
|
|
SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) status_active,
|
|
SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) status_inactive,
|
|
SUM(CASE WHEN status = 'ACTIVE' AND type = 'USER' THEN 1 ELSE 0 END) user_active,
|
|
SUM(CASE WHEN status = 'INACTIVE' AND type = 'USER' THEN 1 ELSE 0 END) user_inactive,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state <> 'WAITING' THEN 1 ELSE 0 END) user_active_cpu,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' THEN 1 ELSE 0 END) user_active_waiting,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND taddr IS NOT NULL THEN 1 ELSE 0 END) user_active_txn,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'INACTIVE' AND taddr IS NOT NULL THEN 1 ELSE 0 END) user_inactive_txn,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Scheduler' THEN 1 ELSE 0 END) user_scheduler,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'User I/O' THEN 1 ELSE 0 END) user_io,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Application' THEN 1 ELSE 0 END) user_application,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Concurrency' THEN 1 ELSE 0 END) user_concurency,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Commit' THEN 1 ELSE 0 END) user_commit,
|
|
MIN(last_call_et) last_call_secs,
|
|
ROUND(AVG(last_call_et)) avg_last_call_secs
|
|
FROM all_sessions
|
|
GROUP BY
|
|
machine
|
|
)
|
|
SELECT s.machine,
|
|
s.total_sessions,
|
|
s.type_user,
|
|
s.type_background,
|
|
--s.type_recursive,
|
|
s.status_active,
|
|
s.status_inactive,
|
|
s.user_active,
|
|
s.user_inactive,
|
|
s.user_active_cpu,
|
|
s.user_active_waiting,
|
|
s.user_active_txn,
|
|
s.user_inactive_txn,
|
|
s.user_scheduler,
|
|
s.user_io,
|
|
s.user_application,
|
|
s.user_concurency,
|
|
s.user_commit,
|
|
s.last_call_secs,
|
|
s.avg_last_call_secs
|
|
FROM sessions s
|
|
ORDER BY
|
|
s.machine
|
|
/
|
|
PRO
|
|
PRO PDB SUMMARY (as of &&cs_snap_time.)
|
|
PRO ~~~~~~~~~~~
|
|
WITH
|
|
all_sessions AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */ * FROM &&cs_tools_schema..iod_session WHERE &&cs_con_id. IN (1, con_id) AND snap_time = TO_DATE('&&cs_snap_time.', '&&cs_datetime_full_format.')
|
|
),
|
|
sessions
|
|
AS (
|
|
SELECT /*+ MATERIALIZE NO_MERGE */
|
|
con_id,
|
|
COUNT(*) total_sessions,
|
|
SUM(CASE WHEN type = 'USER' THEN 1 ELSE 0 END) type_user,
|
|
SUM(CASE WHEN type = 'BACKGROUND' THEN 1 ELSE 0 END) type_background,
|
|
--SUM(CASE WHEN type = 'RECURSIVE' THEN 1 ELSE 0 END) type_recursive,
|
|
SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) status_active,
|
|
SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) status_inactive,
|
|
SUM(CASE WHEN status = 'ACTIVE' AND type = 'USER' THEN 1 ELSE 0 END) user_active,
|
|
SUM(CASE WHEN status = 'INACTIVE' AND type = 'USER' THEN 1 ELSE 0 END) user_inactive,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state <> 'WAITING' THEN 1 ELSE 0 END) user_active_cpu,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' THEN 1 ELSE 0 END) user_active_waiting,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND taddr IS NOT NULL THEN 1 ELSE 0 END) user_active_txn,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'INACTIVE' AND taddr IS NOT NULL THEN 1 ELSE 0 END) user_inactive_txn,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Scheduler' THEN 1 ELSE 0 END) user_scheduler,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'User I/O' THEN 1 ELSE 0 END) user_io,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Application' THEN 1 ELSE 0 END) user_application,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Concurrency' THEN 1 ELSE 0 END) user_concurency,
|
|
SUM(CASE WHEN type = 'USER' AND status = 'ACTIVE' AND state = 'WAITING' AND wait_class = 'Commit' THEN 1 ELSE 0 END) user_commit,
|
|
MIN(last_call_et) last_call_secs,
|
|
ROUND(AVG(last_call_et)) avg_last_call_secs
|
|
FROM all_sessions
|
|
GROUP BY
|
|
con_id
|
|
)
|
|
SELECT CASE WHEN c.name IS NULL THEN 'CDB' ELSE c.name END||'('||s.con_id||')' pdb,
|
|
s.total_sessions,
|
|
s.type_user,
|
|
s.type_background,
|
|
--s.type_recursive,
|
|
s.status_active,
|
|
s.status_inactive,
|
|
s.user_active,
|
|
s.user_inactive,
|
|
s.user_active_cpu,
|
|
s.user_active_waiting,
|
|
s.user_active_txn,
|
|
s.user_inactive_txn,
|
|
s.user_scheduler,
|
|
s.user_io,
|
|
s.user_application,
|
|
s.user_concurency,
|
|
s.user_commit,
|
|
s.last_call_secs,
|
|
s.avg_last_call_secs
|
|
FROM sessions s,
|
|
v$containers c
|
|
WHERE c.con_id(+) = s.con_id
|
|
AND c.open_mode(+) = 'READ WRITE'
|
|
ORDER BY
|
|
CASE WHEN c.name IS NULL THEN 'CDB' ELSE c.name END
|
|
/
|
|
PRO
|
|
PRO SQL> @&&cs_script_name..sql "&&num_days." "&&cs_snap_time."
|
|
--
|
|
@@cs_internal/cs_spool_tail.sql
|
|
--
|
|
@@cs_internal/&&cs_set_container_to_curr_pdb.
|
|
--
|
|
@@cs_internal/cs_undef.sql
|
|
@@cs_internal/cs_reset.sql
|
|
-- |