set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 130
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listWaitEventsInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This utility will display all collected stats about wait events.
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named waitEventsInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column tablespace_name format a15          heading "Tablespace"
column owner           format a8           heading "Owner"
column segment_name    format a6           heading "Segnam"
column latch_name      format a40          heading "Latch Name"
column gets            format 999999999
column misses          format 9999999
column im_gets         format 999999999
column event           format a30          heading "Events by Time Waited"
column total_waits     format 99999999     heading "T_waits"
column total_timeouts                      heading "T_timeouts"
column time_waited     format 99999999     heading "Time_waited"
column average_wait                        heading "Average_wait"
column event           format a30          heading "Events by Average Wait"
column lock_type       format a15          heading "Lock Type"

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set heading on
set feedback off

spool waitEventsInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "List of Database Locks by Type in "  &s_dbname " on " &s_machine skip 2

-- =============
-- Locks by Type
-- =============

select type lock_type,
       count(type) "Count"
from v$lock
group by type;

ttitle left &s_curDate -
       center "Latch Statistics in "  &s_dbname " on " &s_machine skip 2

-- ===============
-- Latch Statstics
-- ===============

select name latch_name,
       sum(gets) "Gets",
       sum(misses) "Misses",
       sum(immediate_gets) "IM_Gets",
       sum(immediate_misses) "IM_Misses"
from v$latch
where gets > 0
group by name
order by sum(misses) desc, sum(gets) desc;

ttitle off

select name latch_name,
       sum(waiters_woken) "Woken",
       sum(waits_holding_latch) "HoldLat",
       sum(spin_gets) "SpinGets",
       sum(sleep1)
from v$latch
where spin_gets > 0
group by name
order by sum(spin_gets) desc;

set heading off

select 'Count from v$locked_object:  ',
       count(*)
from v$locked_object;

set heading on

ttitle left &s_curDate -
       center "System Events in "  &s_dbname " on " &s_machine skip 2

-- =============
-- System Events
-- =============
-- Some *wait* events are not really bad. The ones listed below
-- do not actually cause a user or server process to wait. 

select event,
       time_waited,
       average_wait,
       total_waits,
       total_timeouts
from v$system_event
where event not like 'SQL*Net message from client'
  and event not like 'rdbms ipc message'
  and event not like 'pmon timer' 
  and event not like 'smon timer'
order by time_waited desc;

select event,
       average_wait,
       time_waited,
       total_waits,
       total_timeouts
from v$system_event
where event not like 'SQL*Net message from client'
  and event not like 'rdbms ipc message'
  and event not like 'pmon timer'
  and event not like 'smon timer'
order by average_wait desc;


ttitle left &s_curDate -
       center "Wait Statistics in "  &s_dbname " on " &s_machine skip 2

-- ===============
-- Wait Statistics
-- ===============

select * 
from v$waitstat
order by count desc;

ttitle off

select (sum(pins - reloads)) / sum(pins) "Library Cache"
from v$librarycache;

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache"
from v$rowcache;

select pool,
       name sga_name,
       bytes 
from v$sgastat
where name = 'free memory';

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no Wait Events information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file waitEventsInfo_&s_dbname._&s_machine..LST produced
PROMPT

@sqlplus_settings