advertisements
_____________________________________________________________________________________________________________________
When a user submits a query to the oracle session, oracle will parse the query and check out for the result set blocks in the memory (SGA). If the server process can find out the required block from the SGA, then that is called a memory HIT. In case it is not readily available in the memory, it has to get it from the datafiles to the SGA and this is called a MISS.
How To Find out HIT Ratios?
v$sysstat is the data dictionary view to get the system statistics.
V$sysstat
Column Datatype Description
STATISTIC# NUMBER Statistic number
Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.
NAME VARCHAR2(64) Statistic name. You can get a complete listing of statistic names by
querying the V$STATNAME view.
CLASS NUMBER A number representing one or more statistics class.
The following class
numbers are additive:
• 1 - User
• 2 - Redo
• 4 - Enqueue
• 8 - Cache
• 16 - OS
• 32 - Real Application Clusters
• 64 - SQL
• 128 - Debug
VALUE NUMBER Statistic value
STAT_ID NUMBER Identifier of the statistic
Buffer Cache HIT Ratio
The formula for finding out the buffer cache hit ratio is (1-physical reads)/(consistent gets+ db block gets) *100
SQL> l
1 select name, value from v$sysstat
2* where name in ('consistent gets','db block gets','physical reads')
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
db block gets 84007264
consistent gets 279903811
physical reads 10771598
SQL> select (1-10771586/(84001586+279876315))*100 from dual;
(1-10771586/(84001586+279876315))*100
-------------------------------------
97.0397801
SQL> select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
2 sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
3 sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
4 round((sum(decode(name, 'consistent gets',value, 0)) +
5 sum(decode(name, 'db block gets',value, 0)) -
6 sum(decode(name, 'physical reads',value, 0))) /
7 (sum(decode(name, 'consistent gets',value, 0)) +
8 sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
9 from v$sysstat
10
SQL> /
Consistent Gets DB Block Gets Physical Reads Hit Ratio
--------------- ------------- -------------- ----------
279883293 84002564 10771586 97.04
Formula If this value consistently below 80% you have to increase the SGA size by increasing db_cache_size or db_block_buffers depends on the oracle version which you work
The consistent gets Oracle metric is the number of times a consistent read (a logical RAM buffer I/O) was requested to get data from a data block. Or number of reads made to the block buffer to get the data in consistent mode.
DB Block gets: The number of blocks accessed via single block gets(not through the consistent gets mechanism)
Physical Reads: cumulative number of the physical reads from the disk
Library Cache Hit Ratio
SQL> select sum(reloads) / sum(pins) * 100
2 from v$librarycache;
SUM(RELOADS)/SUM(PINS)*100
--------------------------
.117875987
If above ratio exceeds a value above 1, you need to increase the shared pool size.
Data dictionary HIT Ratio
Gets - Total number of requests for information on the data object
Cache Misses - Number of data requests resulting in cache misses
1 select sum(GETS),
2 sum(GETMISSES),
3 round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2) DictHITRatio
4* from v$rowcache
SQL> /
SUM(GETS) SUM(GETMISSES) DICTHITRATIO
---------- -------------- ------------
75544229 195940 99.74
The data dictionary hit ratio ideally should be above 90% and if it goes below that you need to consider increasing the value of SHARE_POOL_SIZE
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment