-
Title
The SGA current size is not always correct in Spotlight on Oracle -
Description
How does Spotlight calculate SGA size and Library Cache hit rate?
This is what I see in my cluster environment:
1) The sum of SGA size is incorrect. Here is how I calculated the SGA usage in GB. What I got is 7.06GB which is about right since we have sga_target=7216M. But the current SGA usage section on spotlight consistently shows that SGA current size is 9.21 GB.
1* select sum(value)/1024/1024/1024 from v$sga
SQL> /
SUM(VALUE)/1024/1024/1024
-------------------------
7.046875
2) Library Cache Miss Ratio Alarm sends out false alert.
The spotlights library cache hit ratio is very different from the following query we used to calculate library cache hit ratio.
SQL> select sum(pinhits)/sum(pins) from v$librarycache;
SUM(PINHITS)/SUM(PINS)
----------------------
.989544175
SQL> -
Cause
Spotlight does not use v$sgainfo to calculate the sga size
-
Resolution
The SGA Current Size is calculated by issuing the following SQL; then adding the values in the kb column of the query, then divide by 1024.
SELECT name,
ROUND(SUM(bytes)/1024) kb,
SUM(shared_pool)/1024 shared_pool_mb
FROM (SELECT DECODE(name, 'db_block_buffers', 'buffer cache',
'buffer_cache', 'buffer cache',
'sql area', 'sql area',
'library cache', 'library cache',
'log_buffer', 'log buffer',
'fixed_sga', 'fixed sga',
'free memory', DECODE(pool, 'shared pool', 'free memory',
'large pool', 'large pool free memory',
'non-shr pool free'),
'sessions', 'session memory',
'session heap', 'session memory',
'dictionary cache', 'dictionary cache',
'other shared pool') name,
bytes,
0 shared_pool
FROM v$sgastat
UNION ALL
SELECT 'Shared Pool Size',
0,
ROUND(TO_NUMBER(quest_soo_pkg.translate_parameter(value))/1024) kb
FROM v$parameter
WHERE name='shared_pool_size')
GROUP BY name