On 10/04/2010 04:22 AM, Greg Smith wrote:
> I had a brain-storming session on this subject with a few of the hackers in the community in this area a while back I
haven'thad a chance to do something with yet (it exists only as a pile of scribbled notes so far). There's a couple of
waysto collect data on what's in the database and OS cache, and a couple of ways to then expose that data to the
optimizer.But that needs to be done very carefully, almost certainly as only a manual process at first, because
somethingthat's producing cache feedback all of the time will cause plans to change all the time, too. Where I suspect
thisis going is that we may end up tracking various statistics over time, then periodically providing a way to export a
massof "typical % cached" data back to the optimizer for use in plan cost estimation purposes. But the idea of
monitoringcontinuously and always planning based on the most recent data available has some stability issues, both from
a"too many unpredictable plan changes" and a "ba
d
> short-term feedback loop" perspective, as mentioned by Tom and Kevin already.
Why not monitor the distribution of response times, rather than "cached" vs. not?
That a) avoids the issue of discovering what was a cache hit b) deals neatly with
multilevel caching c) feeds directly into cost estimation.
Cheers,
Jeremy