Oracle Scratchpad

July 11, 2012

Proactive Tuning

Filed under: Oracle,Performance — Jonathan Lewis @ 6:25 pm GMT Jul 11,2012

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

16 Comments »

  1. Amen Brother

    Comment by Pete — July 11, 2012 @ 6:29 pm GMT Jul 11,2012 | Reply

  2. 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 GMT Jul 11,2012 | Reply

  3. True ! So True !

    Comment by Amardeep Sidhu — July 12, 2012 @ 5:28 am GMT Jul 12,2012 | Reply

  4. “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 GMT Jul 12,2012 | Reply

  5. Very True!

    Comment by baskar — July 12, 2012 @ 8:24 am GMT Jul 12,2012 | Reply

  6. 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 GMT Jul 12,2012 | Reply

  7. [...] 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 GMT Jul 12,2012 | Reply

  8. > “_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 GMT Jul 12,2012 | Reply

  9. 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 GMT Jul 12,2012 | Reply

  10. 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 GMT Jul 12,2012 | Reply

    • 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 GMT Jul 13,2012 | Reply

  11. 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 GMT Jul 13,2012 | Reply

  12. 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 GMT Jul 13,2012 | Reply

  13. Set it to 11 “_sql_go_fast=11′, it’s one more than 10.

    Comment by rlivermoRob — July 13, 2012 @ 5:54 pm GMT Jul 13,2012 | Reply

  14. [...] Proactive Tuning (jonathanlewis.wordpress.com) [...]

    Pingback by Proactive behaviour « lifeskillsguru — July 19, 2012 @ 1:24 pm GMT Jul 19,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,414 other followers