Shulou(Shulou.com)05/31 Report--

Editor to share with you how Oracle optimizes the related hit rate and utilization rate. I hope you will get something after reading this article. Let's discuss it together.

Summary of various hit rates and utilization rates related to Oracle tuning

Http://www.ecdoer.com/post/oracle-radio.html

The hit rate of Library Cache

Calculation formula: Library Cache Hit Ratio = sum (pinhits) / sum (pins)

SELECT SUM (pinhits) / sum (pins) FROM V$LIBRARYCACHE

It is usually above 98%. Otherwise, you need to consider increasing the shared pool, binding variables, modifying cursor_sharing and other parameters.

Shared pool memory utilization

SELECT (1-ROUND (BYTES / (& TSP_IN_M * 1024 * 1024), 2)) * 100 | |'%'

FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool'

Where: & TSP_IN_M is the SIZE (M) of your total shared pool

The memory utilization of the shared pool should be stable between 75% and 90%. If it is too small, it will waste memory, and if it is too large, there will be insufficient memory.

Query free shared pool memory:

SELECT * FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool'

Db buffer cache hit rate

Calculation formula: Hit ratio = 1-[physical reads/ (block gets + consistent gets)]

SELECT NAME

PHYSICAL_READS

DB_BLOCK_GETS

CONSISTENT_GETS

1-(PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS

WHERE NAME = 'DEFAULT'

Usually should be more than 90%, otherwise, need to adjust, increase the DB_CACHE_SIZE.

Another way to calculate the hit rate:

The formula for calculating the hit rate is:

Hit Ratio = 1-(physical reads-physical reads direct-physical reads direct (lob)) / (db block gets+ consistent gets-physical reads direct-physical reads direct (lob))

By substituting the result values in the previous query, the hit rate of Buffer cache is obtained.

SELECT NAME, VALUE

FROM V$SYSSTAT

WHERE NAME IN ('session logical reads'

'physical reads'

'physical reads direct'

'physical reads direct (lob)'

'db block gets'

'consistent gets')

Data buffer hit ratio

Select value from v$sysstat where name = 'physical reads'

Select value from v$sysstat where name = 'physical reads direct'

Select value from v$sysstat where name = 'physical reads direct (lob)'

Select value from v$sysstat where name = 'consistent gets'

Select value from v$sysstat where name ='db block gets'

Here the hit rate should be calculated as x = physical reads direct + physical reads direct (lob)

Hit ratio = 100-(physical reads-x) / (consistent gets + db block gets-x) * 100

In general, if the hit rate is found to be less than 90%, the application should be adjusted to consider whether to increase the data buffer.

Hit rate of shared pool

Select sum (pinhits-reloads) / sum (pins) * 100 "hit radio" from v$librarycache

If the hit rate of the shared pool is less than 95%, consider adjusting the application (usually without using bind var) or increasing memory

CPU consumption

To calculate the CPU consumption, the first thing to do is to find the session with high CPU usage and the PID with high occupancy with the help of Linux's TOP command. Then use the addr field of v$process and the paddr of v$session to find sql_id and hash_value, then combine v$sqltext to find the corresponding SQL statement, and then analyze the execution plan and tune accordingly.

After reading this article, I believe you have a certain understanding of "how to optimize the hit rate and utilization rate of Oracle". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.