Re: Connection between library cache latches and statistics?

On Oct 10, 9:08 am, "Dusan Bolek" <spam..._at_seznam.cz> 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

Dusan, are the library waits only happening in relation to a small set of tables? If so, do many of the queries on these tables query against values that are on the leading edge of the value creation pattern. That is, are the queries looking for values that did not exist two days ago but which currently exist possibly in significant quantities. I have seen posts about problem plans where there were values outside the range that existed when the statistics were generated. It wouldn't take but one or two heavily concurrently used queries that were subject to a plan shift perhaps from indexed to full scan access (maybe as part of hash or sort/merge join) to potentially result in library cache contention.

If the latch contention is resulting in a performance problem then if you do not have any real clues to the problem source I would just take a look for the heavy logical IO SQL's and I would check the parse statistics to make sure no one added an application that is issuing a lot of non-bind SQL. I trace a SQL Server based connection to our DB once where the task read 100,000 parts from our part master table then issued 100,000 selects against the part master using a constant obtained in the first query for the part number. Every nearly identical statement is hard parsed since no bind variable is used. What is worse is that the queries get only the part description which could have been retrieved on the first query. But the vendor told me the application could not be changed. So another thing to look for is nearly identical SQL but with constants where a bind variable should be.

Unfortunately I have no experience with 9.2.0.7 so I do not know if there are any .7 related issues that might be in play.

Good luck
-- Mark D Powell -- Received on Tue Oct 10 2006 - 09:41:27 CDT