Tutorials

select 'Ratio Should be < 15%' from dual; select sum(getmisses), sum(gets) , (sum(getmisses) * 100)/sum(gets) "Misses To Gets Ratio" from v$rowcache select parameter, getmisses, gets from v$rowcache; select sum(pins), sum(reloads) , sum(reloads) / sum(pins) * 100 "Reload Ratio" from v$librarycache select 'GETHITRATIO should be high > 90%' from dual

select sum(pinhits)/sum(pins) "lib cache hit ratio" from v$librarycache

select namespace
, gethitratio
, pinhitratio
, reloads
, invalidations
from v$librarycache

select pool, name, bytes/(1024*1024) from v$sgastat where pool = 'shared pool' and name = 'free memory'
select * from v$sqlarea where parse_calls != executions
select * from v$sqlarea where sql_text like '%v$sqlarea%'
select * from v$sysstat where name like '%parse%'
select * from v$sysstat where name in ('parse count (total)','execute count')
select * from v$sysstat where name in ('parse time cpu','CPU used by this session')
select * from v$sysstat where name like '%sort%'

select * from v$statname

select * from v$sgastat

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS

select owner, object_name, count(*) from v$bh ,dba_objects where objd = object_id and
owner not in ('SYS','SYSTEM')
and owner = 'HCLT43_PERF'
group by cube(owner, object_name)
order by 1,3,2 desc