Re: Connection between library cache latches and statistics?

Jonathan Lewis wrote:
> This seems a somewhat unlikely effect, but here's a
> hypothetical explanation:
>
> When stats (particularly column low/high) are correct,
> the optimizer happens to discard a number of join orders
> very early:
> e.g. a -> b -> c -> d
> is discarded after a -> b because the stats make this step
> more expensive than the best so far.
>
> However, if the stats (particularly column low/high) are wrong,
> then a -> b is much lower because the query looks as if it is
> going outside the current range of data, so the optimizer carries
> on through all four tables.
>
> One effect of an increased optimization load would be to
> increase the CPU usage, and latch activity on library cache
> and shared pool latches.

We were working more on this and it looks like that it is not about statistics staling after few days, because tables are only slowly changing, but because statistics gathering works like a cleaner for SGA. The library cache latches are slowly bulding from none to significant during period of days and they drop down either after statistics gathering or after doing ALTER SYSTEM FLUSH SHARED_POOL. Our next move will be to implement one-off patch related to the library cache and also decrease size of our shared pool which looks a way too big (3.8GB).

--
Dusan Bolek