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.DISK_READS/sa.executions )/( sa.buffer_gets/sa.executions ),2) 
	)
	) "disk/mem"
	-- -1: Leido todo de buffer
	-- -2: leyó todo de disco
	-- entre 0 y 1 leyó más buffer que disco
	-- >1 Leyó más disco que buffer
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;