CODEWITHSUNDEEP
oracle-databaseplsqloracle11gprofiling
Volodymyr Frolov
Volodymyr Frolov

Reputation: 1306

Profiling stored functions in Oracle

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 ....

Upvotes: 6

Views: 737

Answers (3)

Brainhash
Brainhash

Reputation: 141

Did you try enabling profiler inside the stored function? This may create separate trace (different runId ).

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

I see all function in DBMSHP_FUNCTION_INFO exept sql function because Dbms_hprof dosen't trace sql function (such as ln, nvl... etc.). 

create or replace function addNum(p1 number,p2 number)  return 
as
begin 
 return p1+p2;
end;

BEGIN
  sys.DBMS_HPROF.START_PROFILING('TMP', 'test.trc');
END;
/

declare 
 n number;
 n2 number;
 n3 number;
BEGIN
  select sum(1),max(1),ln(1) into n,n2,n3 from dual;
  select max(addNum(1,level)) into n from dual connect by level<10;
END;
/

BEGIN
  DBMS_HPROF.STOP_PROFILING;
END;
/

So let's into trc file.
select sum(1) into n from dual; is marked as P#C SQL."".""."__static_sql_exec_line6" #6 that's mean that static sql has been executed in line 6.
select max(addNum(1,level)) into n from dual connect by level<10; this select starts with P#C SQL."".""."__static_sql_exec_line7" #7 start in line 6 and next it switches itself to plsql VM to caclucates result of addnum. __plsql_vm and ADDNUM appere 9 times in trc. 

P#C SQL."".""."__static_sql_exec_line7" #7
P#X 3338
P#C PLSQL."".""."__plsql_vm"  <--switch context
P#X 2
P#C PLSQL."ALUKASIEWICZ"."ADDNUM"::8."ADDNUM"#a7f835561d3611ed #1  <-- execut function

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17934

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.

Upvotes: 3

Related Questions