There is an ongoing discussion about who is responsible for application performance tuning: Oracle developers or DBAs. While all Oracle database users and developers argue that it’s a DBA who is responsible for application performance tuning, DBA cries it’s a developer who write the code and so he should write it in an efficient way and later optimize it.
When it comes to application performance tuning most of the times DBA remains in a tight corner, new functionalities being added to the database are just like giving more weapons to performance firefighters when it should also empower the developers community to write and test the efficient code. Whenever I listen to any Oracle community member – the way they speak is like application performance tuning is a battle to be fought only by Oracle DBAs. Look at the Oracle database’s new features in recent versions like 3-click performance tuning in Enterprise Manager Cloud Control 12c, claim that ADDM solve all the performance issues, automatic SQL tuning advisor and Real application testing. While some of these are just marketing gimmick others are really good. No doubt Oracle has put it’s heart and soul into making the product mature and easy to use for everyone, but even after so much efforts everyone who deals with Oracle databases believe that performance tuning is a sole responsibility of an Oracle DBA and all these new features are meant solely for Oracle DBAs.
I don’t think Oracle as a company is doing anything wrong it’s just a perception which has been made since long and become so strong in last many years that performance tuning is sole responsibility of an Oracle DBA. I must say when it comes to application performance tuning, it’s a developer who can contribute more than a DBA and its a developer who should design, write and test his SQLs and PL/SQL.
Almost 7 years back I started my career as a performance tuning DBA, I struggled a lot dealing with application performance tuning issues most of which were out of my control. Think about it. How many times you have left the office on Friday evening with everything well in place and all of sudden on Monday morning everyone is complaining about database performance. You as a DBA becomes from hero to villain just over the weekend. Everyone looks at you suspiciously. You look at every corner of the database for a clue but unfortunately you don’t find any. You get back to the basics and start doing all the things you have learnt over the years – take an AWR/Statspack snapshot and compare it with the known-good time and look for the clues what has changed? Now you are surprised to see many new SQLs are coming in the reports. Later discussions and meetings reveal that actually developers changed the code during the weekend. These changes were not documented anywhere, because developers are unaware that little changes to the SQLs can change the way how CBO looks at them. Forget the ITIL processes, change approval process etc, as such a thick layer of IT processes are rarely there in small Oracle shops. Generally there is a chinese wall between large developers team and small DBAs team in any company.
Now think about some scenarios:
- MultiColumn CBO Statistics is helpful in improving the selectivity estimates in situations where there is a relationship between the columns. But who is best placed to conclude such a relationship, developers or DBAs?
- Partitioning Criteria: DBA can tell which tables should be partitioned but what should be the partitioning criteria, which partitioned indexes. Can he take such decisions without considering the table’s access criteria?
- Index Design: Which columns should be indexes and what type of index? Without functional knowledge can we expect DBA prepare optimal index designs?
How many times we have find Oracle developers who do 10053 trace to investigate why CBO is behaving in a particular way, profiling their PL/SQL codes for clues of performance improvements. Rarely! All assumes that all these tools are meant for DBAs who works as performance firefighters. Yes, this is true that Oracle DBAs work as firefighters whenever there is a performance issue, but if developers leverage this rich functionality of best database technology during development and testing phase, many of the issue could be solved before deployment.
Most of the times developer is a person who just passed out from a college and got a job as an Oracle developer. Sometimes Oracle developer is a person who has development experience but in other programming language like C, Java etc. So in a normal scenario developer is unaware of Oracle architecture, different ways to write optimized SQLs. So how can we expect optimized code hitting an Oracle database?
Now think from an Oracle DBA perspective for a second, how many times during high workload or high utilization time Oracle DBA finds top sessions are coming from TOAD and Toad-like frond-end tools which are pertaining to Oracle developers who with the help of rich functionality provided by tools are executing dumb, poorly written SQLs. Best practice says – no testing should be done in production environments, developers should not have access to dynamic public views blah blah, but that is not the case in many Oracle shops.
The conclusion is – we do need smarter DBAs but more importantly we really need smarter developers. Developers who look at the performance aspects of the code during early at the software development cycle, test them properly using Oracle rich set of tools like SQL trace, 10053 trace, explain plan, tkprof etc and then ultimately release them to the production database. To start with you can have a formal process in place where before any changes developers discuss them with DBA. Some examples are – before any table creation they consider how data will be populated to the table, what will be the access pattern, index design based on where clause etc. Later as the process and people get mature, you will find fine-tuned code executing on the database.