RE: Thoughts on SQL tuning disorder

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 20 Jun 2013 20:30:50 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D8A17_at_exmbx06.thus.corp>


  • development resisted every suggestion until we hired a consultant. He made the same recommendations and upper management forced development to start fixing the code to stop the exodus of customers.

When I end up reporting to management the problems that the in-house DBA has been telling them about I make sure they know that the in-house DBA knows what they are talking about. ("While I was on site, your DBA highlighted a number interesting issues that he/she had already noted ...")

  • Some parts are still slow--again, according to our customers--but I have no idea which parts or the SQL behind them.
  • He told me that performance is good and I should stop asking development to fix queries. That I am exhibiting SQL tuning disorder and I should wait until we start experiencing performance issues again

So you've got a consultant who thinks that customer complaints aren't an indication of a performance issue ? If the end-users' opinions don't count, what does ? There is a bit of a problem though if the customer complaints are so vague that you can't pin down specific functional areas - it may simply be a case of the end-users never being satisfied; you need to collect some solid numbers of which operations, how fast they run and how fast the users think they should run so that you can make a case.

When you do make a case, I'd be inclined to make sure that you convey (in a not very subtle way) how many of the problems the consultant identified were problems that you had already reported - but do it in a way that focuses the management's attention on the fact that your opinions can be trusted and have been confirmed by the consultant.

Regards
Jonathan Lewis    



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Sandra Becker [sbecker6925_at_gmail.com] Sent: 20 June 2013 20:59
To: oracle-l
Subject: Thoughts on SQL tuning disorder

Oracle 11gR2 on IBM mainframe, SUSE11
For most of my tenure at the company, production database performance has been mediocre at best and poor for several areas of the applications.

Over the years, I made several suggestions to use bind variables, write more efficient code, store data in proper formats (dates as a date data type, not a number) so it doesn't have to be converted, etc. I offered proof through explain plans, traces, timing runs comparing the old vs new, but

Lots of hard work from development over the past 8 months and performance is pretty good for most of the application--this according to our customers. I've
asked, but am told they have a handle on it. I have identified about a half dozen queries that still perform poorly when they run and have requested they receive some attention.

The consultant is back and reviewing not just the database, but the storage, servers, etc. . That's the recommendation he intends to make to my boss, who doesn't know squat about databases.

Granted I am somewhat anal about that kind of thing, but I had a couple of reasons for wanting to continue:
1) These queries have caused occasional bottlenecks in the production database. I would like to be proactive about resolving these issues and cleaning up the code.
2) None of our developers have ever been trained in writing SQL. They "picked it up" as needed.
3) We have had high turnover in development and have only 2 developers with a fairly good understanding of the tables and their relationships, and then only for parts of the schema they work with most frequently. We have one application where no one has any expertise at all and I pray it doesn't need any work. I thought looking at the code and tuning would also enhance their knowledge of the application.

What are your thoughts? How would you handle this situation in your organization?
--

Sandy

--

http://www.freelists.org/webpage/oracle-l-- http://www.freelists.org/webpage/oracle-l Received on Thu Jun 20 2013 - 22:30:50 CEST

Original text of this message