Re: OT Discussion- Priority of Performance Tuning...

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 1 Nov 2011 11:50:15 -0400
Message-ID: <CAE-dsO+0KBOjU2WdEbA6_i=M1M=isRZzZBD=LWg6Ld=JwMoQZw_at_mail.gmail.com>



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. .

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 01 2011 - 10:50:15 CDT

Original text of this message