Performance

I was thinking about this while sitting in Vivek’s session at OCYatra in Gurgaon. He was talking about the techniques one can use to optimize badly performing SQLs. But there does exist the other side of this story i.e. throwing hardware at a performance problem to fix it. Of course, it works till a point only and it can’t solve every damn performance problem out there. But it is relatively easier to do (costs money, though) and depending upon where you are hosting your workloads, can be quicker too. You see CPU usage going up or users complaining, simple thing to do is to throw more cores at the database. If the system is CPU starved, it is going to give some temporary relief and you can control the situation. Same can be the case with the storage IOPS. In some extreme cases, you could even migrate the database to a more powerful hardware. With systems like Exadata being around which can make even a bad SQL do fairly well, this lever becomes even more powerful. But is it sustainable or is it the right way to deal with these kind of issues? ...

To put it in bit of an Indian context, database is not your daughter-in-law that you can blame it for every performance issue that occurs in the environment. But it does happen. Most of the time it is the database that is blamed for all such issues. Many times, the issues are in some other layer like OS, network or storage. Faced this issue recently at one of the customer sites where performance in one of the databases went down suddenly. It was a 2 node RAC on 12.1.0.2 running on Linux 7 using some kind of Hitachi SSD storage array. There were no changes as per DBA, application, OS and storage teams. But something must have changed somewhere. Otherwise why would performance degrade just like that. I & my colleague checked some details and found that something happened in the morning a day before. Starting from that point in time, the execution time for all the commonly run queries shot up. Generally speaking, when all the queries are doing bad and you are sure that nothing has been changed on the database side, the reasons could be outside the database. But being a DBA, it is not easy to prove that. We took AWRs from good and bad times and the wait events section looked like this: ...

Just a quick note/post about the significance of COMPRESS and TCPBUFSIZE parameter in performance of a GoldenGate Extract Pump process. COMPRESS helps in compressing the outgoing blocks hence helping in better utilization of the bandwidth from source to target. GG is going to uncompress the blocks before writing them to the remote trail file on the target. Compression ratios of 4:1 or better can be achieved. Of course, use of COMPRESS may result in increased CPU usage on both the sides. ...