The pseudo-code in Page 241 of Expert Oracle Database Architecture(3rd Edition) - Section: Latch "Spinning" shows that each latch sleep occurs after 2000 Get Requests. According to that program logic, query:
select snap_id, latch_name, gets, misses, sleeps, spin_gets, wait_time
,round(gets/sleeps) gets_per_sleep
,((sleeps + spin_gets) - misses) delta
,round(wait_time/sleeps) wait_time_per_sleep
from
(select snap_id, latch_name
,gets - lag(gets) over (partition by latch_name order by snap_id) gets
,misses - lag(misses) over (partition by latch_name order by snap_id) misses
,sleeps - lag(sleeps) over (partition by latch_name order by snap_id) sleeps
,spin_gets - lag(spin_gets) over (partition by latch_name order by snap_id) spin_gets
,wait_time - lag(wait_time) over (partition by latch_name order by snap_id) wait_time
from dba_hist_latch
where latch_name = 'shared pool'
)
where sleeps > 0
order by snap_id, latch_name;
should return gets_per_sleep >= 2000.
(Column delta is discussed in Blog: Is latch misses statistic gathered or deduced ?)
However running the test code "instest.java.NOBIND" provided by the Book, we observe rows whose value is around 200, much less than 2000 for "latch: shared pool", and also rows with value bigger than 20,000.
Latch Name Requests Misses Sleeps Gets (Spin Gets)
-------------- -------- ------- ------- ----------------
shared pool 2,296,041 75,240 15,267 60,165
confirms the above observation:
gets_per_sleep = 2,296,041 / 15,267 = 150
delta = (15,267 + 60,165) - 75,240 = 192
The pattern seems that gets_per_sleep decreases when hard parsing increases.
It looks like that Oracle tends to make performance more and more adaptive, in my humble opinion, called system self-tuning or self-learning. Probably it uses some Adaptive Control method, which adapts "_SPIN_COUNT" based on feedback of latches' workload.
MOS Note (Doc ID 1970450.1) on "_SPIN_COUNT" (amount to spin waiting for a latch) said:
The default value of the parameter is automatically adjusted when the machine's CPU count changes provided that the default was used. If the parameter was explicitly set, then there is no change. It is not usually recommended to change the default value for this parameter.
If after this entire loop, the latch is still not available, the process must yield the CPU and go to sleep. Initially, it sleeps for one centisecond. This time is doubled in every subsequent sleep.
gets_per_sleep < 2000 could also indicate that internally Oracle halves (or reduce) "_SPIN_COUNT" when it doubles sleep time.
2000 is the default value of "_SPIN_COUNT".
Changing it by:
alter system set "_SPIN_COUNT"=4000;
seems no influence on the above latch statistics.
