Profiling stored functions in Oracle

Question

Is it possible in Oracle11g to profile stored functions which are invoked in plsql code from within SELECT ... INTO ... statement?

For profiling I use DBMS_HPROF utility. After a profiling run in DBMSHP_FUNCTION_INFO table I can see everything except for functions which were invoked within SELECT ... INTO ....

Answer 1

In 11g2, my HPROF results include lines in package functions that are called as

SELECT my_pkg.my_func(x) INTO y FROM dual;

Now, I don't see every line -- usually only SQL statements. For example, I profiled "main_test" in the following package.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=0;

CREATE OR REPLACE PACKAGE matt_t1 AS
  FUNCTION p (a NUMBER)
    RETURN NUMBER;

  PROCEDURE main_test;
END matt_t1;

CREATE OR REPLACE  TYPE my_num_tab_type IS TABLE OF NUMBER;

CREATE OR REPLACE PACKAGE BODY matt_t1 AS
  FUNCTION p (a NUMBER)
    RETURN NUMBER IS
    x   NUMBER := 0;
    t my_num_tab_type;
   BEGIN
     t := new my_num_tab_type();
      for i in 1..10000 loop
          x := ln (x+i);
          t.extend();
         t(i) := x;
     END loop;

     SELECT SUM(column_value) INTO x FROM TABLE(t); 
    RETURN x;
  END p;

  PROCEDURE main_test IS
    x   NUMBER;
  BEGIN
    FOR i IN 1 .. 100 LOOP
      x   := matt_t1.p (i);
       DBMS_OUTPUT.put_line (x);
    END LOOP;
  END main_test;
END matt_t1;

In the HPROF results, I see entries for

SELECT SUM(column_value) INTO x FROM TABLE(t); 

But not, for example,

x := ln (x+i);

I get the same results whether I call function p as SELECT INTO vs if I just assign a value directly via PL/SQL. Either way, all the time for the 10,000 natural logarithms is bucketed under the HPROF entry for line

FUNCTION p (a NUMBER)

I also get the same results if I just profile a call to MATT_T1.P() directly.

So, I think HPROF may have some limitations as to what sorts of PL/SQL lines it can include, but it seems to me that the call method (SELECT..INTO) has nothing to do with it.