SQL Tuning Groundhog day

With a bit of exaggeration, this seems to sum up the constant cycle I’m in.

Me: I think we should review the indexing strategy – there are far too many indexes.
Them: No.

Me: We could monitor them and see which ones aren’t being used. We could even set them as invisible and run a full cycle of tests, etc.
Them: No.

Me: I think we should review the stats gathering strategy, you know we’ve got genuinely thousands of histograms that we can’t need.
Them: No.

Me: ‘FOR ALL INDEXED COLUMNS SIZE AUTO’ is generally a bad idea. I definitely think we should review it.
Them: No.

Me: No, we could. We could relatively quickly reduce this to a much, much smaller list of histograms and think of using ‘FOR ALL COLUMNS SIZE REPEAT’.
Them: No.

Me: Ok. Then how about just using the 11gR2 stats gathering defaults?
Them: Maybe. But if it messes up ever, it’s your fault and we want to go back to ‘FOR ALL INDEXED COLUMNS’ immediately.

Me: It would be a really good idea to move away from having these massive sql statements with just the odd index hint here and there.
Them: No.

Me: Seriously, any plan stability you think this gives is an illusion. You can’t have hundreds of lines of SQL with loads of tables and expect a single use_nl to keep it adequately under control all the time.
Them: No.

Me: Using _optimizer_ignore_hints, we could run the 11gR2 batch tests turning off those inadequate hints used for legacy reasons back in 7,8,9i.
Them: No.

Me: I want us to look at ways of not flushing the shared pool three times per day for the batch run for each different region.
Them: No.

Me: If you have all these histograms in conjunction with bind variable peeking, it’s no wonder that you get shared plans not suitable for sharing, hence the flushing. I really think we could do away with this vicious circle.
Them: No.

Me: But we could look at the issues above and then maybe also stuff like SQL Baselines, SQL Plan Management features to offer genuine plan stability if that’s what you want, together with bind sensitivity if necessary.
Them: No.

Me: We could gather stored outlines from the next 9i regression run. We could migrate them into SQL Baselines and you’d have some of the existing 9i plans in the 11g environment which you could go back to in the event of problems.
Them: No.

Me: You said before that you want to run the SQL Tuning Advisor against lots of SQL statements?
Them: Yes.

Me: But if it recommends SQL Profiles or changes in indexing, these are to be ignored?
Them: Yes.

Me: So, really what you’re saying is that you want me to make all this SQL run significantly faster without changing anything or using any new features?
Them: Yes.

Me: But failing that, I’m only really allowed to keep manually hinting SQL.
Them: Yes.

Me: I think you should be open-minded.
Them: We are.

Filed under the unofficial category of “Dominic’s travails against the reality of large corporate environments”.


11 Responses to SQL Tuning Groundhog day

  1. Dom Brooks says:

    Also missing:
    Me: These nullable columns are not nullable, can we redefine them as such?
    Them: No.

    Me: This non-unique index is obviously unique, can I change it?
    Them: No.

  2. Tim Hall says:

    Perhaps if you say them in quick enough succession you might actually get a “Yes” thrown in by mistake. It works in kids TV all the time… 🙂

    • Dom Brooks says:

      Thousands of indexes + ‘FOR ALL INDEXED COLUMNS’ = thousands of histograms.

      Thousands of histograms + bind variable peeking = shared plans which shouldn’t be shared

      Shared plans which shouldn’t be shared + changing bind variables = flushing the shared pool

      And allied with…

      New features = Fear.

      Reminds me of Star Wars, you old film buff, you.

      Fear is the path to the dark side.

      Fear leads to Anger.

      Anger leads to Hate.

      Hate leads to Suffering.



  3. Centinul says:

    It’s funny that you posted this today. I just went through this with respect to the indexing strategy and it’s like pulling teeth. It’s tough working in an environment that is inherently reactive instead of proactive.

    • Dom Brooks says:

      Indeed. These have been my conversations for months now.

      • Centinul says:

        It’s unfortunate that no resources are available for code re-factoring and the like. I can’t seem to get the point across that if you are constantly looking for ways to re-factor things or approach them differently it will generate savings in the long run.

        The mind set is always to plow ahead without any foresight.

  4. Noons says:

    At least you still get a “No” as a reply.
    Where I am, most of the “business analyst” team gets lots of “???” on top of their heads when I ask simple questions like: “which release of Oracle do you need for thiis new system”.
    Apparently, I must be speaking in languages…

  5. Doug Burns says:

    God I wish I hadn’t read this this morning. Came at just the right time, or wrong time, depending on how I look at it.

    On the one hand, I am in the absolutely privileged position of working for someone who is letting me investigate some of these things.

    On the other, the second *anything* runs slower, I just know we’ll be putting that stupid optimizer_index_cost_adj back in 😦

  6. Narendra says:

    Apologies for the late comments.
    Would you feel better if the response is not direct “NO” but something like “Ohh…you know that is a very good idea. Document it and send it to me and I will look into it” which effectively means “Thanks…but no thanks” ?
    🙂 Welcome to my world

  7. A scenario I’ve seen a couple of times now is to have a huge, poorly designed legacy system where the stats were locked in 2008 due to FUD and problems with unwelcome plan changes following the previous upgrade, and now the stats and endpoint timebombs are starting to go off causing serious performance problems. Any attempt to fix anything can now cause unpredictable chaos and typically does so around mid-morning Singapore time, resulting in 3am phonecalls.

    At least if you are on 11g, the SQL Performance Analyzer could help a lot.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: