Not exhaustive<\/p>\n
\r\n\r\nselect * from v$sga_target_advice\r\n\r\nselect * from v$pga_target_advice\r\n\r\nselect * from v$shared_pool_advice\r\n\r\n\r\n-- Dictionary Cache Hit Ratio\r\nSELECT (1 - (Sum(getmisses)\/(Sum(gets) + Sum(getmisses)))) * 100\r\n FROM v$rowcache;\r\n\r\n -- -----------------------\r\n -- Library Cache Hit Ratio\r\n -- -----------------------\r\n SELECT (1 -(Sum(reloads)\/(Sum(pins) + Sum(reloads)))) * 100\r\n FROM v$librarycache;\r\n\r\n -- -------------------------------\r\n -- DB Block Buffer Cache Hit Ratio\r\n -- -------------------------------\r\n SELECT (1 - (phys.value \/ (db.value + cons.value))) * 100\r\n FROM v$sysstat phys,\r\n v$sysstat db,\r\n v$sysstat cons\r\n WHERE phys.name = 'physical reads'\r\n AND db.name = 'db block gets'\r\n AND cons.name = 'consistent gets';\r\n\r\n-- ---------------\r\n -- Latch Hit Ratio\r\n -- ---------------\r\n SELECT (1 - (Sum(misses) \/ Sum(gets))) * 100\r\n FROM v$latch;\r\n\r\n-- -----------------------\r\n -- Disk Sort Ratio\r\n -- -----------------------\r\n SELECT (disk.value\/mem.value) * 100\r\n FROM v$sysstat disk,\r\n v$sysstat mem\r\n WHERE disk.name = 'sorts (disk)'\r\n AND mem.name = 'sorts (memory)';\r\n\r\n-- Locks\r\nSELECT a.serial# as serial,a.sid,a.username,b.type,b.ctime,lmode,a.osuser,c.sql_text\r\nFROM v$session a,v$lock b, v$sqlarea c WHERE b.type in ('TM','TX','UL') and a.sid=b.sid and \r\n lmode > 0 and ((a.PREV_HASH_VALUE = c.hash_value and a.prev_sql_addr = c.address and a.sql_hash_value = 0) or \r\n(c.hash_value=a.sql_hash_value and c.address = a.sql_address))\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Not exhaustive select * from v$sga_target_advice select * from v$pga_target_advice select * from v$shared_pool_advice — Dictionary Cache Hit Ratio SELECT (1 – (Sum(getmisses)\/(Sum(gets) + Sum(getmisses)))) * 100 FROM v$rowcache; — ———————– — Library Cache Hit Ratio — ———————– SELECT (1 … Continue reading