Improve the hit rate and Related Optimization of Oracle databases (1)

Oracle DatabaseIt can be said that the database industry occupies a very important position and leads the development of the database. This article is about Oracle Database debugging andOptimizationThis article mainly introduces Oracle DatabaseHit rateRelated issues, including performance comparison between different algorithms.

Computing of various hit rates in Oracle and related Tuning

1) hit rate of Library Cache:

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

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

Generally, it is more than 98%. Otherwise, you need to consider increasing the sharing pool, binding variables, and modifying cursor_sharing and other parameters.

2) Calculate the memory usage of the Shared Pool:

SQL> 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 usage of the shared pool should be stable between 75% and 90%, which is too small to waste memory. If it is too large, the memory is insufficient.

Query idle Shared Pool memory:

SQL> SELECT * FROM V $ SGASTAT WHERE NAME = 'free memory 'AND POOL = 'shared pool ';

3) db buffer cache hit rate:

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

SQL> SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1-(PHYSICAL_READS/(DB_BLOCK_GETS + CONSISTENT_GETS) "Hit Ratio" FROM V $ comment WHERE NAME = 'default ';

Generally, it should be above 90%. Otherwise, you need to adjust it to increase DB_CACHE_SIZE.

Another method for calculating the hit rate (from the official ORACLE documentation <database performance optimization> ):

The hit rate is calculated as follows:

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

The Buffer cache hit rate is obtained by inserting the result values in the previous query.

SQL> 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 ');

4) data buffer hit rate:

SQL> select value from v $ sysstat where name = 'physical reads'; SQL> select value from v $ sysstat where name = 'physical reads direct '; SQL> select value from v $ sysstat where name = 'physical reads direct (lob) '; SQL> select value from v $ sysstat where name = 'consistent gets '; SQL> select value from v $ sysstat where name = 'db block gets ';

The hit rate is calculated as follows:

Make x = physical reads direct + physical reads direct (lob)

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

If the hit rate is lower than 90%, you should adjust the application to determine whether to increase the data buffer.

5) hit rate of the Shared Pool:

SQL> select sum (pinhits-reloads)/sum (pins) * 100 "hit radio" from v $ librarycache;

If the hit rate of the Shared Pool is lower than 95%, you should consider adjusting the application (usually not using bind var) or increasing the memory.