Skip to main content

Tips for getting along with your DBA


Developers and DBAs: can't we all just get along?

Sure we can!

We just have to break out of the old routine of

Developer: Hey, DBA, add twelve indexes to make my code run faster!
DBA: Hey, Developer, tune your code to make it run faster!

That is, finger-pointing.

Instead, we need to work together, and developers I am not the least big reluctant to say:

It's up to us, not the DBAs, to take the first steps.

So here are tips on what you, the developer, can do to foster a strong, collaborative and highly productive relationship with your DBA:

1. Ask your DBA for advice. 

"I want to make my code run faster. What do you think I should do?" There's no better to improve a relationship than to show some humility and express interest in the opinions - and knowledge - of others.

2. Do the right thing. 

Learn about the performance-related features of PL/SQL (and SQL) and apply them. Here are some links to help get started:

PL/SQL Optimization and Tuning (Doc)
High Performance PL/SQL Videos
SQL Analytics Videos by Connor McDonald
Introduction to Indexing Videos by Chris Saxon

3. Give your DBA a heads-up when your pattern of writing code changes. 

Utilizing new and different features of PL/SQL can have a ripple effect on memory consumption and overall application performance. Don't blindside your DBA.

For example, you learn about executing "bulk SQL" from PL/SQL. So cool! So powerful! And potentially a big PGA memory suck, through the use of collections.

Or you discover the Function Result Cache. Another very exciting enhancement added in 11.1. "Hey, I'm going to add the RESULT_CACHE clause to 100 functions. So easy!" Yes, but you might kill overall database activity with latch contention.





Comments

  1. AnonymousApril 26, 2017 at 3:37 AM

    In theory this is nice. But unfortunatly the real world is different.

    In the real world large companies have outsourced there DBAs to another consulting firm. This consulting firm has outsourced the database support to bangladore or some other IT capital.

    The DBAs there have no idea anymore what the original system is about and can only run scripts without giving advise. They are only paid to close tickets and not to create better database applications. I do not blame them. I blame the management of the company that decided to outsource their DBA knowledge in the first place.

    The only place where Developers and DBAs can still work hand in hand and everbody can have good expertise is mid sized companies or the ones in the IT industry.

    ReplyDelete
  2. Steven FeuersteinApril 26, 2017 at 5:49 AM

    Thanks for these insights, Sven. Yes, with such a configuration (whether DBAs are outsourced to another firm or simply off in some other division of the company), collaboration becomes really hard.

    ReplyDelete
  3. iudithApril 27, 2017 at 4:32 PM

    Hello All,
    I just waited for someone else to start commenting on this ...

    In my late company, though the DBA was not outsourced, its activity was
    mostly related to database installations, upgrades, patches, clones,
    backup, deployment and so on.
    They did not have effective knowledge of the applications beyond
    their indirect involvement, because the DBA team was the only
    one allowed to deploy application changes in production.

    At most, they sometimes took emergency actions to solve urgent performance problems, but at the database level only, without deeper involvement in the application logic or data knowledge.

    It was NOT a proper hand-in-hand working with the developers.

    Working hand-in-hand requires a full familiarity with the application logic and data characteristics, and it requires sitting together and thinking together, and this was not the case.

    Each side had its own "ticking clock" and urgent tasks.

    Regarding outsourcing, I think that the development side had a much higher "chance" to be outsourced than the DBA side.
    Therefore, for some reason, the DBA team was held on a "much higher esteem" than the developers ...

    In my strange position as a "full heart" developer,
    but "officially" belonging to the DBA team,
    I can only be sorry that this was the case :(

    By the way, the "black and white" children in the picture do tell the story ... it only remains to be specified who is who ...

    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete
  4. UnknownMay 1, 2017 at 11:55 PM

    Steven, you're so right! Once I had a work in company, where they chose exactly that way to work, and it was great! There's nothing impossible, just when you don't know how to do something right, or are in doubt whether to pick up this strategy, you just go to some of DBA and discuss it. Our DBA team was very experienced, not in application logic, but in Oracle, so we always solved our problems together. That way most 'for loops' were rewritten to bulks, we started to use result cache and scalar subquery caching instead of marking some undeterministic function as DETERMINISTIC just to improve performance of one query.
    And thank you, Steven, that you help to create better solutions.

    ReplyDelete
  5. Steven FeuersteinMay 2, 2017 at 12:56 PM

    Dmitry, thanks for taking the time to share your experience. It is nice to hear a positive story.

    ReplyDelete
  6. MarcusMay 4, 2017 at 3:10 AM

    So I'm one of the lucky guys :-)

    At our company there's a cafeteria where admins and developers meet each day at 9 o'clock for small talk and if you have a problem you will find all the experts there. Pro's an Con's of new features, security aspects, different viewpoints: nothing is done surreptitiously but usually it is smoother to talk about the 'how to' before going the official way.

    Most important is that this is an informal meeting. Whenever there is no technical problem we talk about family, movies, whatever. IMHO it's much easier to handle problems if you know each other well.

    ReplyDelete
    Replies
    1. Steven FeuersteinMay 4, 2017 at 3:33 AM

      What a great idea, thanks for sharing. I hope it will inspire others to do the same.

      Delete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,
4 comments
Read more

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p
24 comments
Read more