Hit Ratio in Oracle 10g

From Oracle 10g, there is no need to run huge scripts to get the hit ratios. You can query the v$sysmetric view to get the hit ratios.

SQL> select * from gv$sysmetric
where metric_name like '%Ratio%'
and intsize_csec in (Select max(intsize_csec) from gv$sysmetric where inst_id =&id)
and inst_id = &&id;

Thanks

Rollback Segment Wait to Get Ratio

The ratio of sum of WAITS to the sum of GETS in v$rollstat should be less than 5%.

The V$WAITSTAT view is used to find the contention in rollback segment header block.
A value over zero in the column UNDO_HEADER means there is contention.

Run hitratio script to get the ratio.

Thanks

Database Wait Time to CPU Time Ratio

This ratio is available from Oracle 10g.

CPU Time Ratio - Percentage of total time used in performing user requests.
Wait Time Ratio- Percentage of total time used in waiting for resources.

When Wait time ratio is higher than the CPU time ratio, perform the Wait event analysis.

Run hitratio script to get the ratio.

Thanks

Chained Row Ratio

Chained rows occurs as a result of row migration or row chaining.

Row chaining When the size of a row is more than the size of the data block, then the row is spread across multiple blocks. This is called Row Chaining.

Row migration When an update increases the row size and there is no free space available in the data block to accommodate the row, the entire row will be moved to a new block leaving only a pointer in the original block. A row migration can also lead to a row chain if the row becomes large enough.

Row chaining affects index reads and full table scans. You cannot do much to avoid row chaining, one way is to calculate the length of the row and put it in a tablespace that has large block size.

Row migration does not affect full table scans but affects index reads. Increase the PCTFREE to allow more space for future updates. Re-organize or recreate the tables and indexes to avoid row migration.

Run hitratio script to get the ratio.

Thanks

Latch Hit Ratio

It gives the ratio of the total number of latch misses to the number of latch gets for all latches. If the latch hit ratio is below 99 percent, it indicates a latching problem. A latch free wait event in the Top 5 wait events section of the statspack or AWR report confirms that. You must refer to the Latch section of the report for further analysis.

Run the hitratio script to get the ratio.

Thanks

Execute to Parse Ratio

This is an important ratio. Formula for finding this ratio:

Execute to Parse ratio = 100 - (Parse count/ Executions * 100)

If you parse once and execute more, then the ratio will be near to 100%.
If you parse every time you execute, then the ratio will be near to 1%.

When the value is too low, it means the SQL is not sharable.

You may need to set CURSOR_SHARING to FORCE. Set SESSION_CACHED_CURSORS init parameter. Increase OPEN_CURSORS init parameter.

Find out which SQL statement has parse count equal to execute count. These statements are contributing to inefficient cursor sharing.

Run hitratio script to get the ratio

Thanks

Parse CPU to Parse Elapsed

Parse CPU means amount of CPU time used for parsing.
Parse Elapsed means amount of clock time used for parsing.

Parse Elapsed = Parse CPU + Wait time.

Parse CPU to Parse Elapsed ratio = 100 * parse CPU/ parse Elapsed.

Ideally Parse Elapsed must be equal to Parse CPU, i.e., only CPU time is used for parsing. In that case the ratio is 100%. If wait time is more then the ratio will be less.

Low Value for this ratio is an indicator of latching problem. Investigate the latch sections in AWR and Statspack report for contention on library cache and shared pool latches.

Run hitratio script to get the ratio.

Thanks

Soft Parse Ratio

This ratio indicates the percentage of parses that were soft.

Soft-Parse Ratio = 100 * Soft Parse/(Hard + Soft Parse)

If the ratio is low, that is less than 80%, then set CURSOR_SHARING init parameter to FORCE.

Run hitratio script to get the ratio.

Thanks

Pin Hit Ratio

The Pin Hit Ratio indicates how frequently the executed statements found the associated parsed SQL already in the Library Cache.

The value should be greater than 95%. Else increase shared pool size.

Run hitratio script to get the ratio.

Get Hit Ratio

Get Hit Ratio = GETS/GETHITS

GETS = Statements that require parsing
GETHITS = Parsed statements already in the memory.

The value should be greater than 95. In 10g the get hit ratio will be too low, this is a bug.

Run hitratio script to get the ratio

Thanks

Dictionary Cache Hit Ratio

It indicates the percentage of time the data dictionary objects are read from memory rather than on disk.

The value should be greater than 95%. If less then increase the shared pool (SHARED_POOL_SIZE init parameter).

Run hitratio script to get dictionary cache hit ratio.

A cache miss on Dictionary and Library cache is more expensive than a miss on buffer cache.

Thanks

Library Cache Reload Ratio

When the Shared pool is not large enough, it cannot hold the parsed statement for a long time. When new statements are parsed the header of the old parsed statement is kept in the shared pool and the body is pushed out of the memory. Reloads occur when the body of previously parsed statements had to be loaded again.

If the value of this ratio is greater than 1% then increase the shared pool by increasing the SHARED_POOL_SIZE init parameter.

Run hitratio script for library cache reload ratio.

Thanks

Library Cache Hit Ratio

This gives the percentage of Pin requests that result in pin hits.

PINS: No. of times an item in the library cache was executed.
PINHITS: No. of times an item was executed without reloads.

The value should be greater than 95%. If less then,

• Increase the SHARED_POOL_SIZE init parameter.
• CURSOR_SHARING may need to be set to FORCE.
• SHARED_POOL_RESERVED_SIZE may be too small.
• Inefficient sharing of SQL, PLSQL or JAVA code.
• Insufficient use of bind variables.

Run hitratio scrip to get the Library hit ratio.

Thanks

In (PGA) Memory Sort Ratio

Sorts occur either in memory or in disk.

Memory - PGA (Faster no I/O involved)
Disk - Temporary Tablespace (Slower I/O involved)

This ratio indicates the amount of sorts performed in the Memory rather than on Disk.

OLTP database should have value greater than 95%.

If the value is less than 95%, then

• When not using Automatic PGA memory, then increase SORT_AREA_SIZE init parameter.
• When using Automatic PGA memory, then increase PGA_AGGREGATE_TARGET init parameter.

Run hitratio script to get the memory-sort ratio.

Thanks

Buffer and Redo Nowait Ratio

Buffer Nowait Ratio

If this ratio is greater than 99%, then it indicates that the server process is able to obtain the buffer immediately.

If this ratio is less than 99%, then it indicates that the server process has to wait for something before obtaining the buffer. You must investigate the Buffer Waits Section of Statspack/ AWR report.


Redo Nowait Ratio

It indicates the percentage of time the redo space is immediately available without having to wait. A low value indicates small redo log file and frequent redo switch. The value should be greater than 99%. If not then increase the redo log file size or use faster disks for redo log files.

Run the hitratio script to get the ratios.

Thanks

Buffer Cache Hit Ratio

When the hit ratio is

Too Low (<50): Check for indexing. Consider increasing the buffer cache.

Too High (>99): Check for bad SQL or index that is causing a surge in the logical reads (High Buffer_gets SQL).

A high hit ratio is not an indication of good performance, but a low ratio is a sign of performance that can be improved.

Run the script hitratio script to get the buffer cache hit ratio.

Thanks

Hit Ratio Script

Execute the following script for the various hit ratio

column Ratio format a30
column Value format a5
TTITLE '******Hit Ratio Report*****'
BTITLE '*****End of Report*****'

SELECT cur.inst_id, 'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Value"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=1
and cur.inst_id=1
and con.inst_id=1
union all
SELECT cur.inst_id,'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Buffer Cache Hit Ratio"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=2
and cur.inst_id=2
and con.inst_id=2
union
SELECT inst_id, 'Library Cache Hit Ratio ' "Ratio", to_char(Round(sum(pins) / (sum(pins)+sum(reloads)) * 100,2)) "Library Cache Hit Ratio"
FROM gv$librarycache group by inst_id
union
SELECT inst_id,'Dictionary Cache Hit Ratio ' "Ratio", to_char(ROUND ((1 - (SUM (getmisses) / SUM (gets))) * 100, 2)) "Percentage"
FROM gv$rowcache group by inst_id
union
Select inst_id, 'Get Hit Ratio ' "Ratio",to_char(round((sum(GETHITRATIO))*100,2)) "Get Hit"--, round((sum(PINHITRATIO))*100,2)"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
Select inst_id, 'Pin Hit Ratio ' "Ratio", to_char(round((sum(PINHITRATIO))*100,2))"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
select a.inst_id,'Soft-Parse Ratio ' "Ratio", to_char(round(100 * ((a.value - b.value) / a.value ),2)) "Soft-Parse Ratio"
from (select inst_id,value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'parse count (hard)') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Execute Parse Ratio ' "Ratio", to_char(round(100 - ((a.value / b.value)* 100),2)) "Execute Parse Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'execute count') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Parse CPU to Elapsed Ratio ' "Ratio", to_char(round((a.value / b.value)* 100,2)) "Parse CPU to Elapsed Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse time cpu') a,
(select inst_id, value from gv$sysstat where name like 'parse time elapsed') b
where a.inst_id = b.inst_id
union
Select a.inst_id,'Chained Row Ratio ' "Ratio", to_char(round((a.val/b.val)*100,2)) "Chained Row Ratio"
from (SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name = 'table fetch continued row' group by inst_id) a,
(SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid') group by inst_id) b
where a.inst_id = b.inst_id
union
Select inst_id,'Latch Hit Ratio ' "Ratio", to_char(round(((sum(gets) - sum(misses))/sum(gets))*100,2)) "Latch Hit Ratio"
from gv$latch
group by inst_id
/* Available from 10g
union
select inst_id, metric_name, to_char(value)
from gv$sysmetric
where metric_name in ( 'Database Wait Time Ratio', 'Database CPU Time Ratio')
and intsize_csec = (select max(intsize_csec) from gv$sysmetric)
order by inst_id
*/
/

Thanks