There’s no such thing as proactive tuning. Either you do it right before it goes into production (in which case it’s called “design”) or you react to the nasty surprises you get after it’s gone live.
Even if you do something about the problems before anyone has complained that’s still “reactive” not “proactive” – and the trick is to be reactive as soon as possible. For most companies the best strategy is probably to stick a big screen on the wall with the “Top Activity” page from OEM (or equivalent) so that the key players (DBAs and Developers) can keep a casual eye out for resource hogs and prioritise dealing with them as they appear.
Footnote: if you fall into that rare group of people who can look at what’s currently happening, identify the design errors, and explain how to fix them before the performance starts to nose-dive, then I’ll let you say you’re doing proactive tuning; but personally I tend to call that process “eliminating design errors”.
Amen Brother
Comment by Pete — July 11, 2012 @ 6:29 pm BST Jul 11,2012 |
You mean the undocumented parameter that makes any SQL go faster, what was it again – “_sql_go_fast” ? – doesn’t work anymore? Something surely is wrong!
(then again with 2403 “hidden” parameters now in 11.2.0.3, one has to start wondering…)
Comment by Noons — July 11, 2012 @ 10:00 pm BST Jul 11,2012 |
True ! So True !
Comment by Amardeep Sidhu — July 12, 2012 @ 5:28 am BST Jul 12,2012 |
“if you fall into that rare group of people who can look at what’s currently happening, identify the design errors, and explain how to fix them before the performance starts to nose-dive”
I managed to pull that off once – plus I had the patch developed, tested, and ready for installation when the production instance did stop on a Thursday afternoon. The feeling is great!
Comment by Flado — July 12, 2012 @ 8:22 am BST Jul 12,2012 |
Very True!
Comment by baskar — July 12, 2012 @ 8:24 am BST Jul 12,2012 |
This explains why I’ve never liked looking at things like an AWR report just to be proactive. Some call this Compulsive Tuning Disorder (CTD). I hate those questions like “here is my AWR report, can you help me spot performance problems”. Unless I am looking to solve a specific problem, then I never look at an AWR report. But there I go again being reactive! LOL
Comment by Brian Peasland — July 12, 2012 @ 3:03 pm BST Jul 12,2012 |
[…] Lewis talks about proactive tuning on his blog. Personally, I’ve never performed any sort of proactive tuning and Jonathan helps me […]
Pingback by July Reading » Peasland Database Blog — July 12, 2012 @ 3:12 pm BST Jul 12,2012 |
> “_sql_go_fast” ? – doesn’t work anymore?
It is deprecated for a long time. The new one is /*+ WARP_DRIVE(9) */
https://forums.oracle.com/forums/thread.jspa?messageID=4333700
Marcus
Comment by Marcus — July 12, 2012 @ 3:52 pm BST Jul 12,2012 |
Good grief! I’m surprised that SQL in OTN doesn’t have a DISTINCT in it!!! Can I hire that developer?
WARP_DRIVE indeed! Good old Billy, he’s always good value! :)
Comment by Noons — July 12, 2012 @ 9:44 pm BST Jul 12,2012 |
Unfortunately proactive tuning often refers to tuning before the customer complains or the current bottleneck hits 100%.
Comment by k3nnyp — July 12, 2012 @ 11:34 pm BST Jul 12,2012 |
K3nnyp,
True, and echoed in Timur’s article referenced below.
For most people, “proactive” turns out to mean “faster reactive”.
Comment by Jonathan Lewis — July 13, 2012 @ 10:35 am BST Jul 13,2012 |
Here’s an article on the same topic appeared in my RSS feed: Proactive vs Reactive: How to prevent problems instead of fixing them faster. Maybe it will be interesting to somebody.
Comment by Timur Akhmadeev — July 13, 2012 @ 8:48 am BST Jul 13,2012 |
Timur,
Nice article – thanks for the link.
Comment by Jonathan Lewis — July 13, 2012 @ 10:33 am BST Jul 13,2012 |
I think the reason why the word “proactive” is (ab)used so frequently is that it can serve as a euphimism for “I have no idea why I’m doing this”.
Comment by savvinov — July 13, 2012 @ 5:06 pm BST Jul 13,2012 |
Set it to 11 “_sql_go_fast=11′, it’s one more than 10.
Comment by rlivermoRob — July 13, 2012 @ 5:54 pm BST Jul 13,2012 |
[…] Proactive Tuning (jonathanlewis.wordpress.com) […]
Pingback by Proactive behaviour « lifeskillsguru — July 19, 2012 @ 1:24 pm BST Jul 19,2012 |