Correction (2017-Sep-23):
One very experienced Oracle specialist pointed out my error in this Blog. The formula given by Book: Oracle Core: Essential Internals for DBAs and Developers (Page 72–73) is:
sleeps + spin_gets – misses
= recurrent_sleeps
Blog Latch, mutex and beyond
has a deep research of above formula.If you run the first query and see majority of rows with "delta_perc" column less than 1 (1 percent), continue to read this Blog.
select name, misses, sleeps, spin_gets
,sign((sleeps + spin_gets) - misses) miss_deduced_sign
,((sleeps + spin_gets) - misses) delta
,trunc((abs((sleeps + spin_gets) - misses) / nullif(misses, 0)) * 100 ,2)*100 delta_perc
from v$latch where misses > 1000
order by misses desc;
latch misses statistic is deduced, and not gathered in Oracle kernel code.
By observing very small "perc" value, I assume the equation:
misses = Sleeps + Spin_gets
approximately holds for most of latches.
If this is a fact, Oracle would not record all the 3 statistics by sacrificing high performance latch algorithm just to verify the above equation.
Further perceiving "miss_deduced_sign" mostly of 0 or 1 (positive), "misses" statistics is probably deduced from (sleeps + spin_gets), not really gathered in the kernel code (so efficiently implemented by the Oracle developers). Probably Oracle records only "sleeps" and "spin_gets" because both signify "get" not succeeded, and will take longer time, so the time to record both would less expensive than the "get" itself (the time to both statistics can be negligible. A disciplined programmer would not let the auxiliary actions take more time than real actions). The not popular "miss_deduced_sign" of 1 could be explained due to update latency on "misses" by (sleeps + spin_gets). As observed, the not popular "miss_deduced_sign" of -1 or 1 would appear when system is heavily loaded, once system is stable, "miss_deduced_sign" would tentatively return back to 0. This is also a good optimization, function first, statistics later.
A large minus "delta" (miss_deduced_sign = -1) could be a residual from DB start up since they stay almost constant.
Some books contain pseudo-codes to explain the Oracle latch algorithm, I hope they can match the aforementioned equation (see Book Expert Oracle Database Architecture Page 222, and Oracle Performance Firefighting Page 79).
