HomeUncategorized › Dev or DBA? Where does the responsibility lie…

Dev or DBA? Where does the responsibility lie…

… which could be the title of a really great post about a big prod issue debriefing, but sadly, all you’re getting is this post instead!

At the UKOUG 2011 conference, I attended a presentation by Michael Salt about indexes. During the course of the presentation, he mentioned something along the lines of “DBA’s should be responsible for indexes (including creating them)” (that’s not a quote; I can’t remember exactly what he said, unfortunately!). I more or less recoiled, because as a database developer, I would expect to be the person worrying about whether I should be creating an index on a column(s) or not, as part of the design / coding phase. Or even as part of the “why is this query going soooo sloooooooowly?!” prod issue investigation phase.

When I mentioned this on Twitter and Facebook, one of my friends said it was because I was a control freak. He’s not wrong – I am a control freak – but I think that’s irrelevant in this case; the time for thinking about index creation is at design phase, and that (at least, to my mind) is firmly in the developer’s area.

Don’t get me wrong; DBAs are fantastic, knowledgable people (I have to say that, I know far more DBAs than Oracle devs! Anyway, it’s true – at least for most of the DBAs I know) and I’m more than willing to take up my DBA’s advice whenever he’s got some to dispense, but DBA’s are always busy looking after far more systems than I do, so I try hard not to take up more of their time than I have to.

It seems that I’m in a minority over where the dev responsibility ends and the DBA’s starts – things like schema / app design, index creation, SQL optimisation etc are dev activities, whereas things like backups, restores, db creations, GoldenGate setup and tinkering, db tuning, giving advice on poorly performing areas of the code, etc etc are more DBA concerns.

Is it just me? Who should deal with thinking about indexes? Devs or DBA’s? What are your thoughts?

9 Comments.[ Leave a comment ]

  1. Well, there is a role called a Development DBA, someone who sits with a leg in each camp – it seems to be a role that drops in and out of fasion. A developent DBA has a good grasp of how Oracle works, the features available etc but is probably not someone who regulalry backs up and patches databases. They are also a competent developer, especially on the server side of things, but you probably would not want them writing your most crucial UI logic. They are the ideal person to do the indexing strategy and put them in place.

    And that leads me to my real answer. Indexes should be identified and put in place by anyone able to understand both DBA and development worlds. They can be on either side of the camp. The problem is, more and more you get developers who do not know much about the database being used and DBAs who have never written a proper program in their lives let alone understand the total workload of the App. Neither is a good candidate but the DBA role probably ends up with the job as they also monitor the databse for issues, including SQL performance, and have the privs needed to make the changes. Developers are often excluded from that.

    A good DBA with no programming skills should be willing to take advice from a good developer in indexing and a good developer should be willing to ask advice from a good DBA on the indexing possibilities on that database.

    So the right person is someone who understands both sides, second best is two people who respect each others talents and reality is some poor sod who has to guess wildly and then gets into a world of trouble 🙂

    Sorry for the long answer!

    • “Indexes should be identified and put in place by anyone able to understand both DBA and development worlds.” — I’d perhaps change that to “Indexes should be identified and put in place by anyone able to understand the database (and app) performance implications.”

      I think that because it’s a joint thing, the lines are definitely blurred; devs should consider them as part of the development process, DBA’s should understand about them and their effects on the optimizer etc. Both should take ownership, perhaps.

      As long as someone does, I guess!

  2. Hiya
    I’d say it is a combination of developer and dba. Developer might expect specific advantages that an index can provide, thus the reason for adding one. However DBAs can look beyond that specific advantage and provide more insight. I think it shouldn’t be either/or … it should be a joint effort. Of course if you are someone who understands both sides, by all means it should be you, but I’d hesitate to say it should be only one.
    — jamadara

    • Hey Raj! Good to hear from you *{:-D

      You’re right – it is a combination of the both; but I still think that devs should consider creating indexes as part of the development process… or is that just me?!

  3. I think it depends very much on the environment. A developer should be able to say that an index is required based on the knowledge that a particular query will be run a lot and needs an index for adequate performance. Also based on a need for a unique constraint.
    A DBA might also want to add/alter indexes where they are not necessary for application performance but improve general database performance.

    The best solution, of course, is that they talk to each other.

    • “The best solution, of course, is that they talk to each other.” — oh, so true!

      You’re right; I think that my general disagreement with the statement that Michael Salt said is that it isn’t just the DBA’s or the Dev’s responsibility to create indexes, it’s not that clear cut. That plus the fact that I would automatically think about creating indexes as part of the development process!

  4. It all comes down to your definition of what constitutes a DBA and Developer.

    As a DBA it is up to me to make the database fly. That is almost impossible if the apps are bad. It’s my role as a DBA to educate the developers to write good apps. I am a PL/SQL guy, but I also know enough about other programming languages to be able to identify and advise on the common mistakes people run into when developing against Oracle.

    As part of the education process you have to teach people about how to write efficient SQL against Oracle and this will include indexes and tuning etc. If the developers are cool they will want to pick this up and it will be one less thing I have to do. If they are not cool they will ignore it and it will be my job.

    It’s not possible to write good apps against Oracle unless you understand Oracle. It’s not possible to diagnose and fix performance problems unless you understand programming. To me the DBA and developer roles have a massive crossover.



    • “If the developers are cool they will want to pick this up and it will be one less thing I have to do. If they are not cool they will ignore it and it will be my job.” <---- really? There are devs who don't care about how performant there code is? Huh. (That's probably really naiive of me, but I just don't think that way! Performance is key, IMHO!) "To me the DBA and developer roles have a massive crossover." -- definitely agree with this, although I'm more than happy to leave the backups and restores etc to the DBAs *{;-)

  5. Agree with Tim and boneist in that there is no definitive line between and that there is a massive crossover between the APP and Operational side. There must be cooperation between and only then will the best solution for implementation be reached. In the instances where the DBA is not involved up front based on my experience you end up having db’s that are difficult to manage and do not perform as expected. If the applications groups are insistent on the doing the physical design they must also be responsible for ALL performance issues. This rarely happens.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>