how to get explain plan from pl/sql function call?

Question

consider this working pl/sql block:

declare
 o_result boolean;
 io_error varchar2(30000);
 tab1 someTable1;
 tab2 someTable2;
begin
 tab1(1).col1 := 'str';
 tab1(1).col2 := 1;
 tab2(1).col1 := 'str';
 tab2(1).col2 := 0.5;
 tab2(1).col3 := 6;
 o_result := some_function(io_error, 2,'str', 5, to_date('01-JAN-23','DD-MON-YY'), to_date('31-DEC-23','DD-MON-YY'), 4, 'A', tab1, tab2, 'str', 'B', 'str');
end;

how to get the explain plan for pl/sql function calls where its inputs are dates and custom types?

• mustaccio: Does this answer your question? Oracle How to get Execution Plan for SQL executed inside of PLSQL?
• gabriel119435: unfortunately it doesn't because i want the execution plan for the PLSQL code, not SQL. since my function has in and out parameters, i cannot call it embedded in sql select my_f(...) from dual, i have to call it in pl/sql
• mustaccio: There is no "execution plan for the PLSQL code"

Answer 1

SQL and PL/SQL are different languages, different engines. SQL is declarative and you can use explain plan, tuning advisor and other tools for this for SQL. PL/SQL is imperative and the tool you're looking for is DBMS_PROFILER.

DBMS_PROFILER

The package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.

• gabriel119435: when i tried to run exec dbms_profiler.start_profiler; i got Error executing PL/SQL profiler *Cause: An error occurred in during execution of a PL/SQL profiler procedure. do you have any working example to be used in SQLDeveloper client?