LIBRARY CACHE HIT RATIO AND EXECUTION MISSES

The following SQL statement help you to calculate the library cache hit ratio:

SELECT
SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM
V$LIBRARYCACHE;

If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.

SHARED POOL TUNING-CACHE HIT RATIO.

We can simply find out cache hit ratio.Put this below query in to your sql window.

SELECT 'Buffer Cache' NAME,
ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)
* 100
/ (congets.VALUE + dbgets.VALUE),
2
) VALUE
FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
WHERE congets.NAME = 'consistent gets'
AND dbgets.NAME = 'db block gets'
AND physreads.NAME = 'physical reads'
UNION ALL
SELECT 'Execute/NoParse',
DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
),
-1, 0,
ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
)
FROM v$sysstat ec, v$sysstat pc
WHERE ec.NAME = 'execute count'
AND pc.NAME IN ('parse count', 'parse count (total)')
UNION ALL
SELECT 'Memory Sort',
ROUND ( ms.VALUE
/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
* 100,
2
)
FROM v$sysstat ds, v$sysstat ms
WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
UNION ALL
SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
FROM v$librarycache
WHERE namespace = 'SQL AREA'
UNION ALL
SELECT 'Avg Latch Hit (No Miss)',
ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
UNION ALL
SELECT 'Avg Latch Hit (No Sleep)',
ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
FROM v$latch;

ORA-00020: maximum number of processes (150) exceeded.

Maximum number of default process is 150.when that abouve error is happened..we can increase our max number of process by

alter system set processes=160 scope=spfile;