Tablas accedidas por FULL | ExpoDBA

Tablas accedidas por FULL

Tablas accedidas por FULL

select distinct s.HASH_VALUE, s.OBJECT_OWNER, s.object_name, executions,
round(sa.DISK_READS/sa.executions,2) PIO, round(sa.buffer_gets/sa.executions,2) LIO, 
decode (
	round(sa.DISK_READS/sa.executions,2),0,-1,
		decode(
		round(sa.buffer_gets/sa.executions,2),0,-2,
		round(( sa.buffer_gets/sa.executions )/(sa.DISK_READS/sa.executions ),2) 
		)
	) "cache_hit" 
	 -- -1: Leido todo de buffer
	 -- -2: leyó todo de disco
	 -- entre 0 y 1 leyó más  disco que buffer
	 -- >1 Leyó más buffer que disco
from v$sql_plan s,  v$sqlarea sa
where s.address=sa.address
and s.hash_value=s.hash_value
and sa.executions >0
and OPTIONS = 'FULL'
and object_owner <> 'SYS'
and object_owner <> 'SYSTEM'
and OPERATION = 'TABLE ACCESS'
and OBJECT_NAME like '%&TABLE_NAME'
order by 7 desc;