RE: Thoughts on SQL tuning disorder
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <sbecker6925@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Jun 2013 15:10:55 -0500
Cary Milsap covers this idea pretty well in his book "Optimizing Oracle
Performance" (read it if you haven't as it's an easy read thru most of it).
The idea in a nutshell is if the business isn't complaining about it, or if the
execution times fall within requirements, then don't spend time trying to
improve it.
The reason for this is, I assume you have other items on a list of sorts you
can work on - if you spend time working on something that runs within
requirements (however they're given - formal or informal) then you probably are
missing out on accomplishing something else of [more] worth to the company.
I think that kind of sums it up - the list (& Cary) please correct me if I
oversimplified that too badly.
*However* if you can demonstrate a negative net impact to the company by
allowing this code to run as-is and put some numbers to it (also covered in
Cary's book is a template I believe to do exactly this) then you have a case
for working on it ahead of other priorities.
Tuning is fun for a lot of us while other things are more ho-hum so I find
myself tending to spend more time on tuning than I probably should.
Chris
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sandra Becker
Sent: Thursday, June 20, 2013 2:59 PM
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 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
