February 1, 2007 1 Comment
The usage of Stored Outlines offers a number of benefits to my current client.
Firstly, there is a perceived sensitivity regarding the database. It seems, historically, that any changes to the database have led to significant performance degradation. I can’t argue with this, not having been here very long nor having witnessed any such deployments. These people are not at all daft and so I believe them.
There are plenty of changes to be done, however.
So, my theory is that Stored Outlines will allow us to have some Optimizer Plan Stability, even if those captured plans are not as good as they might be.
Which brings me on to the second benefit. I believe that some of the dodgy performance on the database is due to there being incomplete stats – some old, some objects have no stats, etc. Unfortunately, a deployment to save and calculate stats was rolled back due to performance degradation issues. Therefore, I figure that we can use stored outlines to preserve the existing plans whilst introducing some proper stats gathering.
By dint of using stored outlines, we will have captured much of the SQL that various applications issue – so we will have a better understanding of what is connecting and using the DB.
Then we will be able to review much of this SQL and review how it is executing, review any missing indexes and having gathered stats we can start to turn off certain outlines.
One thing is key to this and that is CURSOR_SHARING. Some of the connecting applications do not use bind variables and many of the bind variables that they do use are the sequence number generated ids of news articles. Therefore everyday, the actual values being used change. So, for stored outlines to work at all we need to implement cursor_sharing and hope that that that in itself does not lead to performance degradation – it shouldn’t but who knows. You can’t assume anything.
One of the challenges will be capturing the Stored Outlines. It would not be recommended practice to gather these in production. However, there is no non-production system here that can generate realistic usage to get the sort of coverage that we would need. So, it will have to be production if at all. I’m very concerned about the overhead of enabling the capture in prod.