Re: OT Discussion- Priority of Performance Tuning...
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Nov 2011 11:50:15 -0400
Lets look at it from there perspective. Until it causes a problem, they
don't see it. So why is some archaic number like a logical IO an issue to
them? They are paying for features. They need those features to drive their
business. Most developers (in particular java developers) do not know
anything about performance and just shrug. I worked with a java developer
once who got Sun training who told them to never tune code, its always
cheaper to add hardware. In some cases that is actually true, but in
anything related to bad sql or too much sql it usually isn't. So they
have their developers going this is fine, lets go.
People who make requirements don't understand the technical specs
underlying what they want. I just want to add this. They don't understand
what a data model is, what it looks like, how much legacy data your
dealing with, how much legacy infrastructure code your dealing with. They
need a new feature. This new feature could add revenues to the company. I
remember going to the library some time back and the library catalog search
tool was slow one day and it was taking a long time to find a book I was
looking for. The librarian told me 'it is just slow some days'. There might
be more people using it today. She looked around and said the library was
not that busy. This was part of a county library system and she did not
realize that people at other libraries or on the internet may be increasing
traffic.
The other problem is that some DBAs complain about performance problems
when there really are not any. They also complain about queries, then take
a very long time to provide a solution or worse, just complain and don't
want to help fix something. I have had to deal with this in the past. Where
previous DBAs did this and then I would say, they are not me. It takes a
long time to earn people's trust.
Rest is a bit of a vent... but its a good example.
Another issue is doing with the old 'we have always done it this way and it
works for us'. Their definition of works really means 'I am still
collecting my pay check'. I ran into this on a government project I was
stuck on for 2 years. There were people in upper management and
'architectural' who had been there for years. It is the only place they
really ever worked. They have never seen other ways to do things. They were
in awe of the complex. Applications would be far more complex than they
needed to be, but they didn't get that. Also, if you suggest a change, you
put yourself out there for someone to complain about you, but if you go
along to get along, you don't ruffle any feathers. Another issue is that
alot of the developer who wrote this lousy code got hired by the government
and were now the managers. I was specifically told by my manager not to
offer to 'fix' slow code because it might make some of those managers mad
and since its government contracting risk termination. So I had to keep my
mouth shut.
I remember there was this batch process that ran nightly. I pulled data
from an OLTP and loaded it loaded it to a reporting database. It initially
ran in about 7-8 hours. As soon as I looked at it I saw a simple change.
They were dropping and re-creating materialized views each night. From
experience I knew that was much slower than dropping and creating tables
with nologging. They didn't believe me. Literally took months before they
even agreed to try it out. They just wanted me to 'monitor' it. In project
speak, this meant, stare at it until it finished, send out long winding
technical emails that say nothing and make no sense what soever so it looks
like I know what I'm doing without risking anything.
Once they did that it sped up the process to about abotu 1.5 hours on
average. (there were alot of jobs running at the same time that all created
materialized views). We also had the problem that one some random days it
would take 20 hours to run. Over the years, the DBA lead had sent out long,
technical emails that said nothing explaining why this happened. This again
was obvious as soon as I ran an AWR report on a good day and a bad day. The
large create materialized view statements (that were not CTAS) had multiple
custom function calls in the 'where' clause. This was for each row. So they
executed each function millions of times per query. These functions would
run 1 or more queries each. When I looked at the AWR reports fo rgood days
and bad, this was the bottleneck. The most interesting part is that in both
days the AVERAGE response time per function call was get this '0.0'. This
means that the change in response time was so small oracle was still
rounding it down to 0. I punched the numbers into a calculator and realized
that we had something like a .01 (might have been less) increase in
response time. The problem was that we called the thing too much. When I
announced this the managers demanded to know what caused this HUGE increase
in response time. I tried to tell them, I don't care, it doesn't matter,
and it could be something different each time. We were on a SAN that was
shared with a large group outside of our project, so who knows?
I had no support from my management or team lead and got blown off. I told
them that we could make a simple change, to get rid of these function calls
and do a series of CTAS statements that built on each other. This would be
much faster and have much more consistent response time. They had said they
wanted to add more jobs to load more data to the reporting database, but
were told the server couldn't handle it. I told that if we made this
change, it could easily handle it if we wrote all the future code this way.
I got totally blown off. I got told 'no one has time to do this', but they
have time to have me stair at the thing all the time and do nothing? I got
tired of 'monitoring' this so I offered to re-write the entire batch start
to finish myself and take full responsibility for it. Got the generic 'its
complicated' responses, the 'if','maybe', and 'how do you know responses'
(this is how people can raise issues they can use to point fingers later
and not be held responsible since they just asked a question). I told them
it wasn't a big deal, since I had the outputs to look at. It didn't matter
if I knew what each of the 300 pages of code(the code was crap. I wasn't
even going to look at it when I re-wrote it), I knew what the inputs were
and I knew what the data had to look like when it came out each night. So I
could compare. Blank stare. Got blown off by my own team lead and manager.
My team lead continued to send out long winding nonsensical emails
explaining nothing for the 2 years I was there.
.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
