CODEWITHSUNDEEP
oracle-databaseplsql
Tom Tresansky
Tom Tresansky

Reputation: 19877

Why did plsql_profiler produce no data rows?

DECLARE
  v_run_no binary_integer;
  v_run_time_in_sec number;
BEGIN 
  dbms_profiler.start_profiler('Profiling my proc', 'Tom', v_run_no);

  schema.my_package.my_proc();

  dbms_profiler.stop_profiler();

  -- Calculate the TOTAL_TIME for each unit in the run by summing the TOTAL_TIME
  -- from each data record in the unit and writing it to the TOTAL_TIME column
  -- in the plsql_profiler_units table.
  dbms_profiler.rollup_run(v_run_no);

  -- Grab total run time for display
  SELECT r.RUN_TOTAL_TIME / 1000000000
  INTO v_run_time_in_sec
  FROM ucms_crim_conv.plsql_profiler_runs r
  WHERE r.RUNID = v_run_no;

  dbms_output.put_line('Profiled as run ' || v_run_no || ' in ' || v_run_time_in_sec || ' total sec.');
END;

I've run this same script to profile a different procedure call, by changing ONLY the schema.my_package.my_proc(); line to call a different procedure, and everything went fine.

This time, after the script completes, I can see a row with a value in the TOTAL_TIME column for the run id in the plsql_profiler_runs table.

Previously I would also see 2 rows in plsql_profiler_units, one for the anonymous calling block, and 1 for the procedure being profiled, with associated rows in plsql_profiler_data for each unit. However, this time, I see only the anonymous block in plsql_profiler_units, and the only plsql_profiler_data records for this run id are for the calling anonymous block, not the procedure itself, which is obviously what I'm interested in.

Why might this happen? What can I do to fix it and see data for my procedure?

Upvotes: 2

Views: 1098

Answers (1)

Kirill Leontev
Kirill Leontev

Reputation: 10941

According to the DBMS_PROFILER reference:

Security Model

The profiler only gathers data for units for which a user has CREATE privilege; you cannot use the package to profile units for which EXECUTE ONLY access has been granted. In general, if a user can debug a unit, the same user can profile it. However, a unit can be profiled whether or not it has been compiled DEBUG. Oracle advises that modules that are being profiled should be compiled DEBUG, since this provides additional information about the unit in the database.

I was able to reproduce your issue when profiled procedure was created in another schema and my profiling user lacked either CREATE ANY PROCEDURE or ALTER ANY PROCEDURE privileges. When he had both - everything ran just fine. Probably you reference another schema's package and suffer the same issue.

Upvotes: 4

Related Questions