Re: Connection between library cache latches and statistics?

Dusan Bolek wrote:
> 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

When the statistics are gathered for database objects, the plans for parsed SQL statements that access those database objects must be invalidated, since the optimal plan may change for future executions of the same SQL statement. If the statistics are gathered for all objects, it would seem that this would caused the shared pool to be flushed, and would initially require hard parses for all SQL statements. Is it possible that too large of a shared pool could be causing this problem? What about manually flushing the shared pool?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Oct 11 2006 - 06:37:15 CDT