Is there a different tool to display the plan? Yes, Tkprof display it well.
set feedback off
drop table t1 purge;
drop table t2 purge;
create table t1 as select * from all_users;
create table t2 as select * from all_objects;
alter table t1 add constraint t1_pk
primary key(username);
alter table t2 add constraint t2_fk
foreign key(owner)
references t1
modify owner not null;
begin
dbms_stats.gather_table_stats(user,'T1');
dbms_stats.gather_table_stats(user,'T2');
end;
/
create or replace function foo(p_owner varchar2)
return number as
l_object_id int;
begin
select max(object_id)
into l_object_id
from t2
where owner = p_owner;
return l_object_id;
end;
/
create or replace procedure prc as
cursor c is select username, foo(username)
from t1
where user_id > 55;
r c%rowtype;
begin
open c ;
loop
fetch c into r ;
exit when c%notfound;
end loop;
close c;
end;
/
exec prc;
set feedback on
********************************************************************************
SQL ID: 7f9fb6qhgrygy Plan Hash: 3617692013
SELECT USERNAME, FOO(USERNAME)
FROM
T1 WHERE USER_ID > 55
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 24 0.01 0.00 0 25 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26 0.01 0.00 0 25 0 23
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
23 23 23 TABLE ACCESS FULL T1 (cr=25 pr=0 pw=0 time=29 us cost=4 size=494 card=38)
********************************************************************************recursive depth: 1 - Tell us that this sql is executed from either procedure/function/packages.
********************************************************************************
SQL ID: 0pt4k5fgquawx Plan Hash: 3321871023
SELECT MAX(OBJECT_ID)
FROM
T2 WHERE OWNER = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 23 0.00 0.00 0 0 0 0
Fetch 23 0.04 0.05 0 27899 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 47 0.06 0.06 0 27899 0 23
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90 (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1213 pr=0 pw=0 time=2753 us)
0 0 0 TABLE ACCESS FULL T2 (cr=1213 pr=0 pw=0 time=2748 us cost=630 size=29975 card=2725)
********************************************************************************recursive depth: 2 - Tell us that this sql is invoked from sql executed from procedure/function/packages.