-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Show Library Cache Hit %
--
-- Environment: Needs Oracle DBA Access
--
-- Der Library Cache ist Teil des Shared Pools.
-- Cache Misses im Library Cache sind «sehr teuer», da das SQL-Statement
-- geladen, geparst und ausgeführt werden muss. Hier gilt die Regel, dass
-- 99 % aller SQL-Statements in geparster Form im Memory vorliegen müssen.
-- Ist dies nicht der Fall so muss der Wert SHARED_POOL_SIZE erhöht werden.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Show Library Cache Hit %' -
skip 2

select (1-(sum(reloads)/sum(pins))) *100 "Library Cache Hit %" 
  from v$librarycache;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Show Data Dictionary Cache Hit %
--
-- Environment: Needs Oracle DBA Access
--
-- Der Data Dictionary Cache ist Teil des Shared Pools. Nach dem Instance
-- Startup werden die Data Dictionary Informationen ins Memory geladen.
-- Nach einer gewissen Betriebszeit sollte sich ein stabiler Zustand
-- einstellen. Der Data Dictionary Cache Miss sollte kleiner als 10 % sein.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Show Data Dictionary Cache Hit %' -
skip 2

select (1- (sum(getmisses)/sum(gets))) * 100 "Data Dictionary Cache Hit %"
  from v$rowcache;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Show Memory Sort Hit %
--
-- Environment: Needs Oracle DBA Access
--
-- Anteil der Sort Operationen im Memory:
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Show Memory Sort Hit %' -
skip 2

select (sum(decode(name, 'sorts (memory)', value, 0)) /
       (sum(decode(name, 'sorts (memory)', value, 0)) + 
        sum(decode(name, 'sorts (disk)', value, 0)))) * 100 "Memory Sort Hit %"
  from v$sysstat;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Rollback Nowait Hit %
--
-- Rollback Nowait Hit % zeigt die Hits und Misses für die Online Rollback
-- Segmente. Ist dieser Wert zu gross, so müssen mehr Rollbacksegmente
-- erstellt werden.
--
--              Rollback Segment Waits
--
-- Rollback Segment Waits können einfach aus v$waitstat gelesen werden.

-- Waits auf «undo header» werden häufig verringert, indem man weitere
-- Rollback Segmente erstellt.
-- Waits auf «undo block» werden verringert, indem man Rollback Segmente
-- mit mehr Extents erstellt (10 - 20 Extents).
--
--              Rollback Segments Shrinks
--
-- Rollbacksegmente sollten nicht dauernd wachsen und wieder kleiner werden,
-- um den OPTIMAL Parameter einzuhalten. Dies kann mit dem folgenden Query
-- kontrolliert werden. EXTENTS und SHRINKS sollten keine auftreten, sonst
-- muss der Parameter OPTIMAL angepasst werden.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Rollback Nowait Hit %' -
skip 2

select ((sum(gets)-sum(waits)) / sum(gets)) * 100 
  from v$rollstat;

ttitle left 'Rollback Segment Waits' -
skip 2

SELECT * from v$waitstat;

ttitle left 'Rollback Segments Shrinks' -
skip 2

SELECT name, extents, rssize, writes, xacts,
       gets, waits, extends, shrinks, aveactive
  FROM v$rollstat stat, v$rollname name
 WHERE stat.usn = name.usn
   AND status = 'ONLINE';

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     File I/O Rate
--
-- Das File I/O Rate Diagramm zeigt die Anzahl physical reads
-- und writes pro Sekunde der Oracle Datenbankfiles der gesamten Instance.
--
--              System I/O Rate
--
-- Das System I/O Rate Diagramm zeigt die Anzahl logischen und physischen
-- Reads sowie die Anzahl Blockänderungen pro Sekunde.
--
--              Throughput
--
-- Das Diagramm zeigt die Anzahl User Calls und Transaktionen pro
-- Sekunde der gesamten Instance.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'File I/O Rate' -
skip 2

select sum(phyrds), sum(phywrts) from v$filestat;

ttitle left 'System I/O Rate' -
skip 2

select (sum(decode(name,'db block gets', value,0)) +
        sum(decode(name,'consistent gets', value,0))),
        sum(decode(name,'db block changes', value,0)),
        sum(decode(name,'physical reads', value,0))
  from v$sysstat;

ttitle left 'Throughput' -
skip 2

select sum(decode(name,'user commits', value,0)),
       sum(decode(name,'user calls', value,0))
  from v$sysstat;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Circuits trough Dispatcher to Shared Serverprozess
--
-- Das Diagramm «Circuit» zeigt die virtuellen Verbindung zur Datenbank
-- Instance via Dispatcher und Shared Serverprozess, welcher den User
-- Process verarbeitet.
--
--              Dispatcher Statistics
--
-- Das Diagramm «Dispatcher» zeigt die Statistiken der Dispatcher Prozesse
-- der Datenbank Instance.
--
--              Actual MTS-Parameters
--
--	SELECT 	NAME, VALUE
--	FROM   	V$PARAMETER
--	WHERE	NAME LIKE '%mts%' OR NAME LIKE '%MTS%';
--
--              Max. Number of Server-Processes
--
--	SELECT * FROM V$MTS

--              Queue
--
-- Das Diagramm «Queue» zeigt die Aktivitäten des Multi-Threaded Servers.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Circuits trough Dispatcher to Shared Serverprozess' -
skip 2

select rawtohex(c.circuit),d.name,s.sid,s.serial#,
       c.status, c.queue,c.bytes 
  from v$circuit c,v$dispatcher d, v$shared_server s1,v$session s 
 where c.dispatcher = d.paddr(+) 
   and c.server = s1.paddr(+) 
   and c.saddr = s.saddr(+) 
 order by c.circuit;

ttitle left 'Dispatcher Statistics' -
skip 2

select name, status, accept, messages, bytes, idle, busy
  from v$dispatcher
 order by name;

ttitle left 'Waits of Dispatchers' -
skip 2

select 	network, 
	    decode(sum(totalq),0,'not activ',
	    round(sum(wait)/sum(totalq),2))
	    avg waits (1/100 s)
from 	v$dispatcher d, v$queue q
where 	q.typE = 'DISPATCHER'
and	d.paddr = q.paddr
group by network;

ttitle left 'Waits of Shared-Server' -
skip 2

SELECT 	DECODE(TOTALQ,0,'not activ',
	   ROUND(WAIT/TOTALQ,2))
	   AVG Waits (1/100 s)
  FROM 	V$QUEUE
WHERE	TYPE = 'COMMON'

ttitle left 'Processes actually waiting for a shared server' -
skip 2

SELECT 	QUEUED
  FROM 	V$QUEUE
WHERE  TYPE = 'COMMON'

ttitle left 'Activity of MTS' -
skip 2

select rawtohex(paddr), type, queued, totalq, 
       decode(totalq, 0, 0, wait/totalq/100) 
  from v$queue order by paddr;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     File I/O Rate Details
--
-- Das Diagramm «File I/O Rate Details» zeigt die Anzahl der physischen
-- Reads und Writes pro Sekunde für jedes einzelne Datenbank File.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'File I/O Rate Details' -
skip 2

select substr(NAME,1,30),PHYRDS,PHYWRTS,PHYBLKRD,PHYBLKWRT
  from V$DBFILE DF, V$FILESTAT FS
 where DF.FILE#=FS.FILE#
 order by NAME;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Free List Hit%
--
-- Das Diagramm «Free List Hit %» zeigt Informationen zur Datenblock
-- Contention. Für jedes Segment unterhält Oracle ein oder mehrere Freelists.
-- Freelists enthalten allozierte Datenblocks für diesen Segment- Extent mit
-- freiem Platz für INSERTS. Bei vielen concurrent INSERTS sind unter
-- Umständen mehrere Freelists zu erstellen.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Free List Hit%' -
skip 2

select (sum(value)-(sum(count)/2))/sum(value)*100
  from v$waitstat w, v$sysstat s 
 where w.class='free list' 
   and s.name in ('db block gets', 'consistent gets');

select (sum(count) / (sum(value))) * 100
from v$waitstat w, v$sysstat s
where w.class='free list' 
and s.name in ('db block gets', 'consistent gets');

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Latch Statistics
--
-- Das Diagramm «Latch» statistics zeigt Low-level Locks an shared
-- internen Memorystrukturen.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Latch Statistics' -
skip 2

select substr(ln.name,1,30) "Name",l.gets,l.misses,l.sleeps,
       l.immediate_gets "ImmGets",l.immediate_misses "ImmMiss"
  from v$latch l, v$latchname ln, v$latchholder lh
 where l.latch#=ln.latch# 
   and l.addr=lh.laddr(+)
 order by l.level#, l.latch#;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Library Cache Details
-- 
-- Das Diagramm «Library Cache Details» zeigt Detailinformationen
-- des Library Cache im Shared Pool der Instance. Der Library Cache
-- enthält SQL und PL/SQL Code in geparster Form. Es ist wichtig, dass
-- die Ratio für diese Bereiche nahezu 100% beträgt.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Library Cache Details' -
skip 2

select namespace,gets,gethits,
  round(gethitratio*100) "RATIO%",
  pins,pinhits,round(pinhitratio*100) "RATIO%"
from v$librarycache
 order by namespace;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Waiting Sessions
-- 
-- Die View ROW_LOCK_WAITS zeigt die wartenden Sessions. Dieses Statement
-- ist als View implementiert, es darf keine Rows zurückbringen, da sonst
-- ein User warten muss.
-- 
-- create or replace view row_lock_waits
-- (username, sid, object_owner,
--  object_name, sql_text, file_nr, block_nr, record_nr)
-- as
-- select	s.username, s.sid,
-- 	o.owner,
--	o.object_name,
--	a.sql_text,
--	s.row_wait_file#,
--	s.row_wait_block#,
--	s.row_wait_row#
-- from 	v$session s, v$sqlarea a, dba_objects o
-- where 	o.object_id = s.row_wait_obj#
-- and   	s.sql_address = a.address
-- and   	s.row_wait_obj# > 0;
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Waiting Sessions' -
skip 2

select * from row_lock_waits;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Memory Statistics
--
-- Das Diagramm zeigt das allozierte Memory in Bytes für die gesamte
-- Instance (alle Sessions).
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Memory Allocated' -
skip 2

select /*+ use_nl(n,s) */ sum(value) 
  from v$statname n, v$sesstat s 
 where s.statistic# = n.statistic# 
   and name = 'session uga memory';

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Network Bytes Rate
--
-- Das Diagramm «Network Bytes Rate» zeigt die Anzahl Bytes / Sekunde an
-- Daten, die vom Datenbank Server und seinen Clients über SQL*Net
-- ausgetauscht werden.
--
--              Network I/O Rate
--
-- Das Diagramm «Network I/O Rate» zeigt die Anzahl Message 
-- Packete / Sekunde die vom Datenbank Server und seinen Clients
-- über SQL*Net ausgetauscht werden.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Network Bytes Rate' -
skip 2

select sum(value) from v$sysstat
 where name like 'bytes%SQL*Net%';

ttitle left 'Network I/O Rate' -
skip 2

select sum(total_waits) 
  from v$system_event
 where event like 'SQL*Net%';

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Session Events
--
-- Die V$SESSION_WAIT View enthält sämtliche Events, welche die User- 
-- und System Sessions in den Wartezustand versetzen. Diese View kann
-- verwendet werden, um rasch einen Performance Engpass herauszufinden.
--
-- Eine Waiting Time von 0 zeigt an, dass die Session
-- gerade auf einen Event wartet. Grosse Wait Times weisen auf ein
-- Performance Problem hin (siehe Oracle Tuning Guide Seite A-4).
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Session Events' -
skip 2

select w.Sid "Sid", nvl(substr(s.username,1,15),'Background') "User",
  substr(w.event,1,25) "Event", w.wait_time "Wait Time"
  from v$session_wait w, v$session s
where w.sid = s.sid
order by 2,4;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Redo Allocation Tuning
--
-- Das Diagramm «Redo Allocation Hit %» zeigt das Buffer Tuning der Redolog
-- File Aktivitäten. Die Misses dürfen nicht grösser als 1 % sein.
--
-- Das Diagramm «Redo Statistics» zeigt die Anzahl Redo Entries, Space 
-- Requests und Synch. Writes pro Sekunde für die Datenbank Instance.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Redo Alloc Hit%' -
skip 2

select ((gets+immediate_gets) / 
        (gets+immediate_gets+misses+immediate_misses)) *100    
  from v$latch
 where name = 'redo allocation';

ttitle left 'Redo Statistics' -
skip 2

select sum(decode(name,'redo entries', value,0)),
       sum(decode(name,'redo log space requests', value,0)),
       sum(decode(name,'redo synch writes', value,0))
  from v$sysstat;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     SQL of connected sessions
--
-- Das Diagramm «SQL Area» zeigt die shared Cusor Informationen
-- im Library Cache.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'SQL of connected sessions' -
skip 2

select distinct nvl(username,type) username,sid,sql_text
  from v$session, v$sqlarea
 where sql_hash_value = hash_value
   and sql_text is not null
 order by username;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     System Statistics
--
-- Das Diagramm «System Stats» zeigt alle Parameter der wichtigen System
-- Statistiktabelle v$sysstat.
--
-- Auswertung der System Statistik
--
-- Aus den Systemstatistiken können wichtige Informationen gewonnen werden.
-- Man beachte dass es sich bei diesen Angaben immer um kumulierte Werte seit
-- dem letzten Startup handelt.
--
-- Full Table Scans
--
-- table scan blocks gotten       61'900'307
-- table scan rows gotten       194'6840'695
-- table scans (long tables)          13'267
-- table scans (short tables)        307'195
--
-- Index Scans
--
-- table fetch by rowid           15'653'655
--
-- Redo Waits
--
-- redo log space requests              1018
-- redo log space wait time            21263
-- 
-- Bei grösseren Waits sind die RDO-Files zu vergrössern und er Parameter
-- LOG_BUFFER muss erhöht werden. Die Zeit ist in 1/100 Sekunden angegeben
-- (21263 = 212 Sekunden = 3,5 Minuten in etwa 5 Wochen).
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'System Statistics' -
skip 2

select s.name, s.value
  from v$sysstat s 
order by s.name, s.value;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     Who uses what objects
--
-- Das Diagramm «Table Access» zeigt alle Datenbankobjekte, welche zur
-- Zeit von welcher Session benutzt werden.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Who uses what objects' -
skip 2

select sid "Sid", substr(owner,1,15) "Owner",
  substr(object,1,20) "Object"
  from v$access
where owner != 'SYS'
order by owner;

-- #############################################################################
--
-- Autor:       Martin Zahn Akadia AG
--
-- Purpose:     User statistics
--
-- Das Diagramm «No. of Users Logged On» zeigt die Anzahl concurrent
-- Users Sessions, unabhänig davon ob sie nun aktiv sind oder nicht.
--
-- Das Diagramm «No. of Users Running» zeigt die Users Sessions,
-- welche eine Transaktion ausführen.
--
-- Das Diagramm «No. of Users Waiting» zeigt die User Sessions, die
-- auf einen Event (for whatever reason) warten müssen, um eine Aktion durchzuführen.
-- 
-- Das Diagramm «No. of Users Waiting For Lock» zeigt die User Sessions,
-- die auf die Freigabe eines Locks warten müssen.
--
-- #############################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Users logged on' -
skip 2

select sessions_current
  from v$license;

ttitle left 'Users running' -
skip 2

select count(*) 
  from v$session_wait
 where wait_time!=0;

ttitle left 'Users waiting' -
skip 2

select substr(w.sid,1,5) "Sid",
          substr(s.username,1,15) "User",
          substr(event,1,30) "Event",
          seconds_in_wait "Wait [s]"
from v$session_wait w, v$session s
where s.sid = w.sid
   and state = 'WAITING'
   and event not like 'SQL*Net%' 
   and event != 'client message'
   and event not like '%mon timer'
   and event != 'rdbms ipc message'
   and event != 'Null Event';

ttitle left 'Users waiting for locks' -
skip 2

select count(*) 
  from v$session
 where lockwait is not null;