Monday, August 9, 2010

Mister Trace

For the past several weeks, my team at Method R have been working hard on a new software tool that we released today. It is an extension for Oracle SQL Developer called Method R Trace. We call it MR Trace for short.

MR Trace is for SQL and PL/SQL developers who care about performance. Every time you execute code from a SQL Developer worksheet, MR Trace automatically copies a carefully scoped trace file to your SQL Developer workstation. There, you can open it with any application you want, just by clicking. You can tag it for easy lookup later. There’s a 3-minute video if you’re interested in seeing what it looks like.

I’m particularly excited about MR Trace because it’s the smallest software tool we’ve ever designed. That may sound funny to a lot of people, but it won’t sound funny to you if you’ve read Rework by Jason Fried and David Heinemeier Hansson of 37signals. MR Trace does a seemingly very small thing—it gets your trace file for you—but if you’ve ever done that job yourself, you might get a kick out of seeing it happen so automatically, so simply, and so quickly.

The thing is, the normal process of getting trace files is raw misery for many of our friends. It’s a common story: “If I trace some SQL, then to get my trace files, I have to call up my SA or DBA. I apologize for the interruption and hope he’s in a good mood. I tell him what system I need him to look at. He has to figure out which trace files are the ones I need, and then he FTPs them over to where I can get to them. I try not to bother him, but there’s no other way.”

Most places don’t have any security reasons to prohibit developers from getting their trace files, but they just don’t have the time or the interest to create procedures that developers can use to fetch only the files they’re supposed to see. The resulting bother is so labor-intensive and so demotivating that developers stop fighting and just move on without trace files to guide them.

That’s a big problem: if you can’t see how the code you write consumes response time, then how can you optimize it? How can you even know if you should try to? If you have to guess where your code spends time, then you can’t possibly think clearly about performance.

We have tried to design MR Trace to be a beautiful little application that does exactly what you need by staying out of your way. If we did it right, then you won’t be thinking about MR Trace whenever you use it; you’ll just have the trace files you need, right where and when you need them. And you’ll have them with no extra typing, no extra clicks, and—for goodness’ sake—certainly no more phone calls or trips down the hall. ...Unless it’s to to show off a performance problem you found and fixed before anyone else ever noticed it.

Key information:

Name:
Method R Trace
Type:
Extension for Oracle SQL Developer
Function:
Zero-click trace file collector
Price:
$49.95 USD
Risk:
30-day free trial
URL:
http://method-r.com/software/mrtrace
Designer:
Method R Corporation

17 comments:

  1. Cary, how tempted were you to call it Sparky?

    ReplyDelete
  2. Thank you, @Joel.
    @Kurt (grin), not very.

    ReplyDelete
  3. Saw the movie. It looks awesome.

    Is it 10046 based (aka can I alter the event...) ?

    :-)

    ReplyDelete
  4. @Marco, thanks. Yes, it's 10046-based, but altering the event would be a new feature we haven't included. Hadn't thought of it, but it's an interesting idea. I'll log it as a case for consideration.

    ReplyDelete
  5. This is really a great tool.
    Do you plan to support also DBMS_HPROF?

    ReplyDelete
  6. Feature Request:

    It would be useful to have support for some of the 10046 tracing enhancements in 11G, as well.

    Example use case: I want to manually execute a PL/SQL func/proc but trace only one key SQL statement within the call tree.

    Keep up the good work - love the name.

    ReplyDelete
  7. Sterol, Mathew: Hadn't considered DBMS_HPROF or the new tracing options yet for MR Trace. Will investigate. Thanks for reading, and keep the ideas coming!

    ReplyDelete
  8. What about site licensing? It's a real pain keeping track of who has it, who is still using it, who has quit, what is the total count, etc.

    That overhead has stopped us from using other tools who wouldn't come up with some sort of single fee.

    ReplyDelete
  9. @Skipjacker, understood, and no problem. Write us through our Contact Us page at method-r.com, and we'll be glad to work with you.

    ReplyDelete
  10. Hello Cary:

    I tried this "Mister Trace" using below Anonymous plsql block. but the Trace file is not collected to my local machine. but this works well for Standalone SQL's. does that don't work for Anonymous blocks?

    DECLARE
    l_cnt number;
    BEGIN
    FOR r IN (SELECT owner,object_type,object_name FROM all_objects)
    LOOP
    SELECT /*+ INDEX(T,T_IND1) */ COUNT(*)
    INTO l_cnt
    FROM T
    WHERE owner =r.owner
    AND object_type =r.object_type
    AND object_name =r.object_name;

    SELECT /*+ INDEX(T,T_IND2) */ COUNT(*)
    INTO l_cnt
    FROM T
    WHERE owner =r.owner
    AND object_type =r.object_type
    AND object_name =r.object_name;
    END LOOP;
    end;
    /

    ReplyDelete
  11. @Rajesh, using MR Trace on an anonymous block like this shouldn't be any problem at all. I just executed a case very similar to yours, and the trace file showed up just as expected. Is it possible that you clicked the Run Statement button instead of the Run Script button in SQL Developer?

    Let's take this up over at http://method-r.com/forums, where I can involve our MR Trace development team if we need to.

    ReplyDelete
  12. Awesome tool and was very easy to use. I would like see call stack of all the sub-rocedures/functions called from main one. Is there way to do it?

    ReplyDelete
  13. The reason I once asked if alternatives were maybe an option, and/or actually may strange indirect way of asking regarding "does it support 10046", was because I also was thinking about usefulness in the context of 10053 or maybe 19027, level 0x2000 (http://www.liberidu.com/blog/?p=2508), although I am still fighting with the later.

    ;-)

    ReplyDelete
  14. Raghav: When you say call stack, which perspective do you mean? Some choices: (a) SQL or PL/SQL recursive parent-child relationships, (b) execution plan row source operation call stack, (c) something else.

    Marco: I understand. 10053 is on the candidate features list already. Won't be in the next release, but maybe the one after that. We're considering other tracing events as well.

    ReplyDelete
  15. Cary,

    Thanks for your quick response.It is others option (C).I was looking for a way, wherein I run the main procedure (e.g processInvoices) then I would like to get all the procedures/functions called within say processInvoices. From MR. trace, I am able to see all SQLs called within it but not actually functions/procedures from where the SQL comes. Is there way to get this info from Mr. trace?

    ReplyDelete
  16. Raghav: Would you mind if we continue this conversation at http://method-r.com/forums, where my colleagues can help me answer?

    ReplyDelete