How to get cumulative logons and sessions highwater mark for an Oracle database?

It is always good to have an RDA, Statspack or AWR report to let a database to introduce itself to you :)

Of course usually you will be needing much more information than this, especially related to the developed applications on that database.

This morning one of my colleagues asked how he can get cumulative logins and sessions high water mark for an Oracle database, so below may help;

set linesize 1000
column name format a25
column num_cpus format a10
column db_block_size format a15
column requests_for_data format 999999999999999999

SELECT to_char(open_time,
               'DD-MON-YYYY HH24:MI:SS') last_startup,
       SYSDATE - open_time days_passed_from_last_startup,
       vp1.VALUE num_cpus,
       vp2.VALUE db_block_size
  FROM v$thread, v$system_parameter vp1, v$system_parameter vp2
 WHERE instance = 'bscsdb1'
   AND vp1.NAME = 'cpu_count'
   AND vp2.NAME = 'db_block_size';

LAST_STARTUP         DAYS_PASSED_FROM_LAST_STARTUP NUM_CPUS   DB_BLOCK_SIZE
-------------------- ----------------------------- ---------- ---------------
25-MAR-2008 03:24:20                    ,459479167 64         8192

SELECT MAX(d.NAME) db_name,
       SUM(s.VALUE / (24 * 60 * 60 * (SYSDATE - t.open_time)) + 0.001) tps
  FROM v$database d, v$sysstat s, v$thread t
 WHERE s.NAME IN ('user commits', 'transaction rollbacks');

DB_NAME          TPS
--------- ----------
BSCS      151,125446

SELECT SUM(s.VALUE) requests_for_data
  FROM v$sysstat s
 WHERE s.NAME IN ('db block gets', 'consistent gets');

  REQUESTS_FOR_DATA
-------------------
        13120866284

SELECT SUM(s.VALUE) db_time FROM v$sysstat s WHERE s.NAME IN ('DB time');

   DB_TIME
----------
 639913103

SELECT sessions_highwater FROM v$license;

SESSIONS_HIGHWATER
------------------
              5581

SELECT NAME, VALUE FROM v$sysstat WHERE NAME LIKE '%logon%';

NAME                           VALUE
------------------------- ----------
logons cumulative            1102893
logons current                  5434

Advertisements