Oracle Database Tuning

Check Memory Hit ratio for the Oracle 10g


Activity: Check the memory hit ratio for all database servers

Process: To Capture the Memory hit ratio

Login to database server with username and password

2) goto sqlplus connect to database with username and password

3) sql> SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets ) ) "HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE ( consistent_gets + db_block_gets ) !=0 column "logical_reads" FORMAT 99,999,999,999
column "phys_reads" FORMAT 999,999,999,999 column "phy_writes" FORMAT 999,999,999,999 PROMPT BUFFER HIT RATIO (>70) select A.value + B.value "logical_reads", C.value "phys_reads", D.value "phy_writes", round(100 * ((A.value+B.value)-C.value) / (A.value+B.value)) "Buffer Hit Ratio" from $SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D
where A.statistic# = 38
AND B.statistic# = 39
AND C.statistic# = 40
AND D.statistic# = 41;

Output: Displays memory hit ratio