Re: Connection between library cache latches and statistics?

"Dusan Bolek" <spambin_at_seznam.cz> wrote in message news:1160485686.081819.59340_at_k70g2000cwa.googlegroups.com...
> I'm encountering a strange problem. We're suffering from terrible
> library cache latches (30-50% of waits in statspack reports) when
> table's stats being older than two business days. I do not understand
> why, because the affected tables are only slowly changing and I can't
> imagine any possible connection between stats too old and library latch
> waits.
>
> Any ideas?
>
> Database: 9.2.0.7 EE 64bit Itanium
> --
> Dusan Bolek
>

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.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html