Hit rates and Event waits graph from the Database Browser Overview is blank.
Hit rate graph uses a query against gv$buffer_pool_statistics. On some databases, this view has 2 'DEFAULT' rows and the query errors with the following when executed:
Select inst_id, 'Buffer Cache' name, round(100 * (1-(physical_reads/(db_block_gets+consistent_gets))), 2) valueFROM gv$buffer_pool_statisticsWHERE name = 'DEFAULT'
WORKAROUND
For buffer cache hit rate, run the following from editor to get the values manually:
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads';
For the rest of the hit rates, run the following:
SELECT ec.inst_id, 'EXECUTE/NoParse' name,
DECODE (SIGN (ROUND((SUM(ec.value)-SUM(pc.value))*100/DECODE(SUM(ec.value),0,1,SUM(ec.value)),2)),-1,0,
ROUND((SUM(ec.value)-SUM(pc.value))*100/DECODE(SUM(ec.value),0,1,SUM(ec.value)),2)) value
FROM gv$sysstat ec, gv$sysstat pc
WHERE ec.name='EXECUTE COUNT'
AND pc.name IN ('parse COUNT','parse COUNT (total)')
AND ec.inst_id = pc.inst_id
GROUP BY ec.inst_id, 2
UNION ALL
SELECT ms.inst_id, 'Memory SORT', ROUND(SUM(ms.VALUE) / DECODE ((SUM(ds.VALUE) + SUM(ms.VALUE)), 0, 1, (SUM(ds.VALUE) + SUM(ms.VALUE))) * 100,2)
FROM gv$sysstat ds, gv$sysstat ms
WHERE ms.name = 'sorts (memory)' AND ds.name = 'sorts (disk)'
AND ms.inst_id = ds.inst_id
GROUP BY ms.inst_id, 2
UNION ALL
SELECT inst_id, 'SQL Area get hitrate', ROUND(SUM(gethitratio) * 100,2)
FROM gv$librarycache
WHERE namespace = 'SQL AREA'
GROUP BY inst_id, 2
UNION ALL
SELECT inst_id, 'AVG Latch Hit (No Miss)', ROUND((SUM(gets)-SUM(misses))*100/SUM(gets),2)
FROM gv$latch
GROUP BY inst_id, 2
UNION ALL
SELECT inst_id, 'AVG Latch Hit (No Sleep)', ROUND((SUM(gets)-SUM(sleeps))*100/SUM(gets),2)
FROM gv$latch
GROUP BY inst_id, 2;
For Event Waits, run the following:
Select e.inst_id, e.event,
e.time_waited / 100 time_waited, e.total_waits
From gv$system_event e
Where event Not In
('Null event', 'client message', 'smon timer',
'rdbms ipc message', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client',
'parallel query dequeue wait', 'latch free', 'enqueue',
'write complete waits', 'free buffer waits', 'buffer busy waits',
'pipe gets', 'Null event', 'client message',
'smon timer', 'rdbms ipc message', 'pmon timer',
'WMON goes to sleep', 'virtual circuit status', 'dispatcher timer',
'SQL*Net message from client', 'parallel query dequeue wait', 'latch free',
'enqueue', 'write complete waits', 'free buffer waits',
'buffer busy waits', 'pipe gets', 'slave wait',
'PL/SQL lock timer', 'null event', 'Null event',
'rdbms ipc reply', 'Parallel Query Idle Wait - Slaves', 'KXFX: Execution Message Dequeue - Slave',
'slave wait');
STATUS
Waiting for fix in a future release of Toad for Oracle.
