I spend a lot of time speaking to PL/SQL development teams. A conservative estimate would break down to about 200 presentations a year and 2,000 developers. I have several stock presentations, but I prefer to have conversations with my audience rather than just start throwing slides up on the projector. If we find a topic of interest, then I can jump into presentation mode.

When I get desperate for audience participation, I’ll start throwing concepts and problems out hoping that someone will latch on. Can you guess what Mother-Of-All-Problems topic is? That’s right, performance tuning.

Performance tuning is a challenge for any developer. No matter how efficient the logic/design/code is, a pesky user will come up with a way to throw a monkey-wrench into it. And then occaisionaly you will find a database developer who concentrate solely on functionality and assumes the DBA will take on the performance aspect.

To Heck with Debugging!

When I talk about your standard PL/SQL IDE features, everyone will start jumping in with questions about debugging. “Can you show us how to debug?” “Can you show us how to use watches?” I’m fine to tackle this subject. However, I’m flabbergasted that not once in 10 years has someone asked me to demonstrate profiling.

Even a Manager Can See the Problem Here

I Like To Throw Grenades and See What Happens
As I’m finishing up a discussion on debugging, I like to put up the image you see above on the projector. The room gets really quiet, and then people start asking excitedly, ‘How did you do that!?! Is that new?’ When I’m feeling particularly snarky (only 95% of the time), I let them know this information has been readily available since 2000.

This Is Not a Tutorial for the Profiler
Do a google, you’ll find about 70,000 pages on the subject. If you’re too lazy to do that, here’s two quick links from our OraDBPedia wiki:

  • DBMS PROFILER – Overview
  • DBMS PROFILER – Examples
  • Developer Aren’t Stupid, Why Don’t They Know About This Already?
    Well, most developers coming to see me speak haven’t been working with PL/SQL or Oracle since 2000. They have been working with Oracle for a few years or maybe just a few months. They know what ‘they need to know’, or what their co-workers have shown them. In order for them to pick up on these excellent tips, they have to want to grow as developers and Oracle professionals.

    Of course, we can’t drag developers kicking and screaming to conferences or the Internet, but we can be available to provide assistance and guidance. The worst thing we can do is treat a NOOB like a NOOB. This helps no one and hurts everyone. And to the DBAs – it’s your job to educate and nurture your developers. Make sure these packages are installed in your development databases and granted to your developers!

    Developing with 11g?
    Oracle has delivered a new take on profiling with DBMS_HPROF and the hierarchical profiler. Same story, but easier to see data across multiple PL/SQL objects and comes with better reporting (HTML). If you’re a developer, try to get around to this before 2018, OK?


    I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.


    1. I think the problem was that to use the profiler, you needed a couple of scripts run by the DBA. That made it hard for developers to try it to see whether it was useful, and without trying it, it was hard to justify to a DBA why the scripts should be run.

      • JeffS

        you’re right on that Gary! That’s why I propose all Dev databases have this setup automatically. It’d be like giving a developer the the rights to create their PL/SQL but not run it.

    2. Pingback: Tweets that mention ‘New’ Technology from 2000 | 140,000 Characters or Less -- Topsy.com

    3. Good point Chet. That’s more of my issue, the lack of ‘caring’

      Just code it, run it, forget about it…until something doesn’t do what it’s suppose to!

    4. I think I found DBMS_PROFILER just a couple of years ago. Pretty freaking awesome I must say.

      The reason no one knows about it is because most of us don’t care about it…or we’re just too far removed from the actual implementation to care. OK, it was last year I found it.

      I was debugging a 2500 line procedure where the BEGIN was on line 2000. I had to prove that this was thing was slow and also show the multiple, unnecessary, paths it took. It was quite fun to have that kind of evidence.

      I still don’t have a completely cogent thought, so I’ll just stop now before rambling too much.

    Write A Comment