Re: Connection between library cache latches and statistics?

Mark D Powell wrote:
> 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.

We were unable to identify particular statements having these kind of problems yet. Affected database is quite complicated having tens of thousands of different queries issued each day. Library latches are massive, but they do not last long, so it is difficult to drill them down.

> 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.

That's the problem. Everyone says: "Are you having problems with the library cache? Look for heavy parsing, use BIND variables". Our developers were trained to use BIND variables everytime for a long time and they are doing it well. We look for heavy parses already, we found one problematic SQL query that even with BINDs was not parsed. It was impossible to rewrite, but we have tweaked an application logic so it is being issued twenty times less than before and therefore it is causing only minor problems now. There is no other parse related problematic SQLs in our database now.
According to the parse calls stats, the database is perfect now and everything should run well now, but it is not. If we keep our stats to grow older, latch waits are back.

--
Dusan Bolek