How does Spotlight calculate SGA size and Library Cache hit rate? (42705)

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>

We calculate different way than in the above example.

1. The SGA size is calculated from v$sgastat not v$sga. The query we used is below.

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,
CASE SIGN (bytes - max_sga_size)
WHEN 1 THEN NULL
ELSE bytes END bytes,
0 shared_pool
FROM v$sgastat,(select value max_sga_size from v$parameter where name like sga_max_size)
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;

The total SGA size is calculated by the sum of the result from the v$sgastat(kb)/1024.


2. Library Cache Miss Ratio Alarm.

To get our calculation, we do a point-in-time calculation.

The alarm and value that appears is for the last 15 seconds (or whatever the refresh rate is for your system). To get our value, we run the below statement twice then compare 2 runs.

SELECT namespace, gets, gethits, pins, pinhits, reloads
FROM v$librarycache
WHERE namespace = SQL AREA
ORDER BY namespace;

We calculate out
 100 * (?gets - ?gethits) / ?gets

Please note ? is the delta. The way we calculate delta is to run the query twice at the the interval of 15 seconds, then take the difference of the two Eg on my database

The result of the first run :
Namespace Gets Gethits Pins Pinhits Reloads
SQL AREA 22171 15467 6159331 6107804 25431

The result of the 2nd run :
SQL AREA 22180 15475 6167264 6115736 25431

Hence based of the equation above
100 * (?gets - ?gethits) / ?gets
100*( {22180-22171} - {15475-15467} ) / (22180-22171)
100* (9-8)/9 = 11.11

Spotlight does not use v$sgainfo to calculate the sga size