How to profile end to end performance of Oracle stored procedure

Question

I want to know how long my stored procedure is taking to execute from the time an external process hits the database and says execute this to the time the database returns back to the process and says here ya go.

Is there a simple easy way to do this very basic task?

Of course a report of what is happening during the stored procedure execution and how much of the stored procedure's time is spent doing each task (inserts, plsql string manipulation etc.) would be a bonus, but I really just want something simple and easy to use. (And free)

Answer 1

If you're using Oracle 11g you should have a look at the hierarchical profiler, DBMS_HPROF. This is a tool which will give you elapsed timings for all the nodes in a PL/SQL program. As the name suggests, it is especially useful for investigating programs which call programs which call programs. It also identifies timing for SQL statements distinct from function calls. Find out more.

It is part of the standard 11g deployment, and hence is free. Well, once you've paid for your license it's free :)

By default rights on the DBMS_HPROF package are not granted to any one. So, you'll need to get somebody with SYSDBA access to see you right. The analysis also requires the creation of some tables. Neither of these things should be an issue but I know sometimes they are.

---

Alas, you're on an earlier version. So that leaves you with just DBMS_PROFILER, which has been around since 9i. This basically works well for a single program. Its main drawback is that we can only use it on programs for which we have the CREATE privilege (i.e. just programs in our schema unless we have the CREATE ANY PROCEDURE privilge, which usually means being a DBA). Also, for profiling embedded SQL statements we need to use the DBMS_TRACE package. Find out more.