Neeraj Bhatia's Blog

December 20, 2011

Do we need Smarter Developers or Smarter DBAs?

Filed under: Oracle - General — neerajbhatia @ 19:30
Tags: , , ,

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.

Cheers!

About these ads

4 Comments »

  1. Well… good one but just wondering how unlikely of developer who is just new passed out or came from some other world, the DBA is a guy who born with experience in hand :-)

    Comment by Mandeep — December 20, 2011 @ 21:39 | Reply

    • Mandeep,

      Thanks for the reading.

      I didn’t mean to say that DBA born with practical experience. But its very unlikely (at least I’ve seen this) that DBA doesn’t have any prior experience because while working for SPs either they work on L1 support or they start with something else and over the run they become a DBA.

      Neeraj

      Comment by neerajbhatia — December 22, 2011 @ 09:36 | Reply

  2. I’d disagree with this blanket statement: “all Oracle database users and developers argue that it’s a DBA who is responsible for application performance tuning” – this is quite untrue. Maybe a lot of users/developers might argue that, but it’s certainly not universal. For both my current and former clients, performance was the responsibility of (a) enterprise architects, (b) system designers, (c) developers, as well as DBAs.

    Comment by Jeffrey Kemp — December 21, 2011 @ 09:33 | Reply

    • Jeffrey,

      Thanks for your comments.

      I agree with you that it’s not a universal truth. (probably I should have said “Most of the times …” instead of “all”). My intention was to discuss that most of the times it happens. I happened to me and to my whole network of friends who call me for technical consulting and while tuning a particular SQL when I ask functional questions they get blank face. Then I say, you should report these top resource-consuming SQLs to developers/application support teams because they can do more in these situations.

      Cheers,
      Neeraj

      Comment by neerajbhatia — December 22, 2011 @ 09:31 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: