Saw an interesting post related to work I am doing locking in plans and finding plans that have changed: Carlos Sierra post on queries with changing performance.
Our DBA team uses versions of my sqlstat.sql(updated) script to find queries whose elapsed time change and we use a script from Carlos Sierra’s SQLT collection to force the execution plan back to an efficient plan that the optimizer choose in the past. Carlos Sierra’s new post includes scripts that resemble my sqlstat script but with more functionality including an interesting way of helping people figure out which queries are now running slower. At this point we just look at the top queries that are running and run sqlstat.sql to see if they have run with less elapsed time in the past. We eyeball it and usually it is obvious because the bad plan takes 100 times longer than the good one. But the newly posted Carlos Sierra script seems to use some math to help figure out whether the elapsed time is really bad enough for us to consider it a problem.
I want to set up a monitoring script that pages us when a plan changes for the worse so we can decide whether to intervene and force on older plan to improve performance. I have a simple script running on one of our databases but, as I expected, it is returning a lot of false positives. So, I’m hoping to have some time this week to improve that script so that we get fewer false alarms and I’m going to take a close look at Carlos Sierra’s newly posted script for ideas.
– Bobby
Hi,
thanks for sharing – surely makes sense to have a look!
BTW I also recently published a script for this, with a configurable new vs. old elapsed time ratio threshold (although have no feedback yet from my colleagues on its usefulness)
http://recurrentnull.wordpress.com/2014/09/23/monitoring-execution-plan-changes/
Thanks for your comment and the link to your post. Have you thought about looking at v$session for the currently running SQL statements to see how they compare to history in dba_hist_sqlstat? That’s what I’m working on now. I want to run a check every 15 minutes or so and alert if a plan has changed for the worse.
Sorry, I didn’t have the “notify of new comments” box checked and had a lot of things going on 😉
In my case, it was targeted toward a bunch of long-running, nightly reports only, so dba_hist_sqlstat was enough…
That makes sense. Thank you for your comment.
– Bobby