Cheeky Cockatoo

set feedback off
set pages 100
alter session set nls_date_format = 'dd-Mon-YYYY hh24:mi:ss';

select sysdate from dual;

prompt
prompt Contention Statistics...
prompt

prompt Rollback Contention Stats (each class should be < 1% of total)...
prompt
select class, count
from v$waitstat
where class in ('system undo header','system undo block',
'undo header', 'under block');
select sum(value) total
from v$sysstat
where name in ('db block gets','consistent gets');

prompt
prompt Redo Log Stats...
select name, value
from v$sysstat
where name = 'redo log space requests';

column name for a20 heading 'Name'
column ratio for 999.9 heading 'Ratio'
select ln.name name, gets, misses, 
       immediate_gets, immediate_misses
from v$latch l, v$latchname ln
where ln.name in ('redo allocation', 'redo copy')
and ln.latch# = l.latch#;

prompt
prompt Sorting Stats...
prompt
select name, value
from v$sysstat 
where name like 'sorts%';

prompt
prompt Free List Contention...
prompt
select class, count from v$waitstat where class = 'free list';

exit