CODEWITHSUNDEEP
oracle-databaseoracle11g
ThinkJet
ThinkJet

Reputation: 6745

Collecting profiler information for PL/SQL package owned by another user

Suppose we have 2 users (schemes) in Oracle database: TARGET_USER and TESTER_USER.

TARGET_USER owns some package:

create or replace package TARGET_USER.SomePackage 
is

  procedure some_interface_proc(p1 number, p2 varchar2, p3 xmltype);

end;

Body of this package contains a lot of functions, called from some_interface_proc, e.g.:

create or replace package body TARGET_USER.SomePackage
is

  procedure do_some_operation_1 
  is 
  begin
    null; -- Really do some actions here 
  end;

  procedure do_some_operation_2 
  is 
  begin
    null; -- Really do some actions here 
  end;

  procedure some_interface_proc(p1 number, p2 varchar2, p3 xmltype)
  is
  begin
    do_some_operation_1;
    do_some_operation_2;
  end;

end;

Privileges to execute this package granted to TESTER_USER :

grant execute on TARGET_USER.SomePackage to TESTER_USER

Also TESTER_USER granted with all privileges required to run DBMS_PROFILER package and owns all required tables. With such a setup TESTER_USER able to successfully run profiler against test script which can be simplified as follows:

begin

  for cData in (
   select a, b, с from table_with_test_data
  ) loop

    TARGET_USER.SomePackage.some_interface_proc(a,b,c);

  end loop;

end;

So, all statistic collected and all seems to be Ok but ...

Question

Why TESTER_USER can't see detailed statistics about running time for SomePackage.do_some_operation_1 and SomePackage.do_some_operation_2 procedures?

As specified in DBMS_PROFILER documentation there are 4 things which can affect profiler:

  1. The profiler only gathers data for units for which a user has CREATE privilege
  2. In general, if a user can debug a unit, the same user can profile it.
  3. Debug information in compiled unit.
  4. Program unit must be compiled to interpreted (not native) code.

While trying to meet all requirements next actions done:

grant create any procedure  to TESTER_USER;
grant alter any procedure  to TESTER_USER;
grant debug on TARGET_USER.SomePackage to TESTER_USER;

and checked compilation mode of SomePackage and if debug information exists.

But after completing all this actions TESTER_USER still can't access profiler statistics for internal procedures.
So, question is: what must be done to correct this situation if it even possible?

Upvotes: 3

Views: 605

Answers (1)

ThinkJet
ThinkJet

Reputation: 6745

Recently I meet colleague who introduced this situation to me and we realize that all works well on Oracle side, but steps to insure that all conditions are met was wrong.
So I apologize to all those who spent their time to answer the incorrectly positioned question.

But our founding may help someone else, so I'll try to explain situation and answer my own question.
In short there are two points:

  1. Always use native Oracle tools to verify strange situation;

  2. Reproduce full situation from scratch on each test attempt.

Source of the problem is the way that switching between Native and Interpreted code was implemented in "PL/SQL Developer" tool. There are application-wide "PL/SQL Code type" preference which affects every package compilation done with help of user interface. While verifying all conditions collegaue used alter session set PLSQL_CODE_TYPE=INTERPRETED or alter session set PLSQL_CODE_TYPE=NATIVE to switch between compilation modes, but after that occasionally used interface features to recompile a package, so tool applies it's own settings and done compilation in native mode.

After arranging all actions step-by-step we got a clue to solve a problem and now can profile this package successfully.

Upvotes: 2

Related Questions