Oracle cumulative high water mark sessions Oracle Tips by Burleson Consulting

Question:

How to I find the high water mark of sessions since database startup time?  I have Oracle 7.3.

Answer:

In later releases of Oracle, cumulative login statistics reside in the v$sesstat and v$sysstat views. 

SQL> select name, value from v$sysstat where name like '%logon%'

NAME VALUE
---------------------------------------------------------------- ----------
logons cumulative 29
logons current 20

SQL > select sessions_current, sessions_highwater from v$license;

Here is a good script by Steve Adams to display the high water mark for connected sessions in Oracle7, and there may be changes required for later releases.  It references these Oracle7 x$ structures:

x$ktadm
x$ksqrs
x$kturd
x$ksupr
x$ksuse
x$kdnssf
x$ktcxb

----------------------------------------------------------------
--
-- Script: fixed_table_hwms.sql
-- Purpose: to find the high water marks for the fixed tables
-- For: 7.3 only
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-----------------------------------------------------------------

column table_name heading "TABLE|NAME"
column parameter heading "PARAMETER|NAME"
column setting heading "CURRENT|SETTING"
column hwm heading "HIGH WATER|MARK"
column usage heading "PERCENT|   USED" format a7

select
  'x$ktadm' table_name,
  'dml_locks' parameter,
  count(*) setting,
  count(decode(ksqlkctim, 0, null, 0)) hwm,
  to_char(
    100 * count(decode(ksqlkctim, 0, null, 0)) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$ktadm
union all
select
  'x$ksqrs' table_name,
  'enqueue_resources' parameter,
  count(*) setting,
  count(decode(ascii(ksqrsidt), 0, null, 0)) hwm,
  to_char(
    100 * count(decode(ascii(ksqrsidt), 0, null, 0)) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$ksqrs
union all
select
  'x$kturd' table_name,
  'max_rollback_segments' parameter,
  count(*) setting,
  count(decode(kturdext, 0, null, 0)) hwm,
  to_char(
    100 * count(decode(kturdext, 0, null, 0)) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$kturd
union all
select
  'x$ksupr' table_name,
  'processes' parameter,
  count(*) setting,
  count(ksuprpid) hwm,
  to_char(
    100 * count(ksuprpid) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$ksupr
union all
select
  'x$ksuse' table_name,
  'sessions' parameter,
  count(*) setting,
  count(decode(ksuseflg, 0, null, 0)) hwm,
  to_char(
    100 * count(decode(ksuseflg, 0, null, 0)) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$ksuse
union all
select
  'x$kdnssf' table_name,
  'temporary_table_locks' parameter,
  count(*) setting,
  count(decode(ksqlkctim, 0, null, 0)) hwm,
  to_char(
    100 * count(decode(ksqlkctim, 0, null, 0)) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$kdnssf
union all
select
  'x$ktcxb' table_name,
  'transactions' parameter,
  count(*) setting,
  count(decode(ktcxbflg, 0, null, 0)) hwm,
  to_char(
    100 * count(decode(ktcxbflg, 0, null, 0)) / count(*),
    '99999'
  ) || '%' usage
from
  sys.x_$ktcxb
/