Thoughts on SQL tuning disorder
Date: Thu, 20 Jun 2013 13:59:23 -0600
Message-ID: <CAJzM94A=Uaosi-yiHZUb_5kCCU8KKQi0R1L5R_3LP+F9TQAKdQ_at_mail.gmail.com>
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 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.
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. Some parts are still slow--again, according to our customers--but I have no idea which parts or the SQL behind them. 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. 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. 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-lReceived on Thu Jun 20 2013 - 21:59:23 CEST