In 11g2, my HPROF results include strings in package functions called
SELECT my_pkg.my_func(x) INTO y FROM dual;
Now I do not 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 if I call the p function as SELECT INTO vs if I just assign the value directly through PL / SQL. In any case, all the time for 10,000 natural logarithms is kept under the HPROF heading for the line
FUNCTION p (a NUMBER)
I also get the same results if I just profile the call directly in MATT_T1.P ().
So, I think that HPROF may have some restrictions regarding what types of PL / SQL lines it can include, but it seems to me that the calling method (SELECT..INTO) has nothing to do with it.