Top ten hotest objects by touch count

col owner 	format a20 trunc
col object_name	format a30
col touches 	format 9,999,999
select	*
from	(
	select	count(*)
	,	sum(tch) TOUCHES
	,	u.name OWNER
	,	o.name OBJECT_NAME
	from 	x$bh x
	,	obj$ o
	,	user$ u
	where	x.obj = o.obj#
	and	o.owner# = u.user#
	group 	by u.name, o.name
  	order by 2 desc
	)
where	rownum < 11
/

Waits by file

col name format a60
select	name
,	count 
from	x$kcbfwait
,	v$datafile
where	indx + 1 = file#
order	by 2
/

Segment Waits

select	object_name
,	obj#
,	statistic_name
,	value
from	v$segment_statistics
where	owner like '&owner'
and	statistic_name like '%waits%'
and	value > 0
order	by statistic_name
, 	value desc
/

Time waited for latches

col event format a30
select	event
,	time_waited
,	round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct
from	(
	select	event
	,	time_waited
	from	v$system_event
	where	event not in (
		'Null event'
		,'client message'
		,'rdbms ipc reply'
		,'smon timer'
		,'rdbms ipc message'
		,'PX Idle Wait'
		,'PL/SQL lock timer'
		,'file open'
		,'pmon timer'
		,'WMON goes to sleep'
		,'virtual circuit status'
		,'dispatcher timer'
		,'SQL*Net message from client'
		,'parallel query dequeue wait'
		,'pipe get')
	union
		(
		select	name
		,		value
		from	v$sysstat
		where name like 'CPU used when call started'
		)
	)
order by 2 desc
/

Identify hot blocks

col event format a30
select 	event
,	total_waits
,	time_waited
from	v$system_event 
where	event like '%wait%'
order 	by 2,3
/

select	*
from	v$waitstat
/

select	p1 "File #"
,	p2 "Block #"
,	p3 "Reason Code"
from	v$session_wait
where	event = 'buffer busy waits'
/

Look at the performance stats for the instance

select	n.name
,	s.value
from	v$statname n
,	v$sysstat s
where	n.statistic# = s.statistic#
order 	by n.class
,	n.name
/