Summary of the hit rate query statements of the Oracle database

Summary of the hit rate query statements of the Oracle database

1) hit rate of Library Cache
Formula: Library Cache Hit Ratio = SUM (PINHITS)/SUM (PINS)
Select sum (PINHITS)/SUM (PINS) from v $ LIBRARYCACHE;

The hit rate cannot be lower than 99%. Otherwise, you need to consider whether it is affected by factors such as the size of the shared pool, the bound variable, and cursor_sharing.
Select name, value from v $ parameter where name = 'cursor _ sharing'

2) Shared Pool usage
Formula: Shared Pool Hit Ratio = (100-Free memory/shared_pool_size * 100) %
If the database uses AMM to manage memory, use the following statement to obtain the Shared Pool size:

Select pool, ROUND (SUM (BYTES)/1024/1024, 2) from v $ SGASTAT where POOL = 'shared pooled 'group by pool;

If you use Manual to manage the memory:
Select name, value from v $ parameter where name = 'shared _ pool_size ';

Query 1:

SELECT 100-ROUND (select round (SUM (BYTES)/1024/1024, 2) BYTES
From v $ SGASTAT
Where name = 'free memory'
And pool = 'shared pool'
Group by pool)/ROUND (SUM (BYTES)/1024/1024, 2 ),
(4) * 100 | '%' RATIOS
From v $ SGASTAT
Where POOL = 'shared pool'
GROUP BY POOL

Query 2:
SELECT 100-ROUND (B. BYTES/ROUND (SUM (A. BYTES)/1024/1024, 2), 4) * 100 | '%' RATIOS
From v $ sgastat,
(Select pool, ROUND (SUM (BYTES)/1024/1024, 2) BYTES
From v $ SGASTAT
Where name = 'free memory 'and pool = 'shared pooled' group by pool) B
Where A. POOL = 'shared pooled 'group by B. BYTES, A. pool

The usage of the shared pool should be stable between 75% and 90%. If it is too small, it will be wasted. If it is too large, the memory is insufficient or the statement reusability is not high.