Hi All,
I have one package with one SP(perf_test.p1). This is calling another stand alone sp(sp_perf_tst). I would like to know how much time it is taking for each sql. In stand alone sp, there is PL/SQL LOOP. Is it possible to get how much time it took to complete the PL/SQL LOOP. I want to know these details using oracle V$ Views. Please find my sample package and stand alone SP code below.
PS: I am able to get the SQL stats present in perf_test.p1 by using v$sql view. But not able to find the details about stand alone sp execution stats.
--Stand alone SP
create or replace
procedure sp_Perf_tst
IS
x int;
begin
for i in 1..10
loop
x := x+i;
end loop;
end sp_Perf_tst;
--Package Code
create or replace package perf_test is
procedure p1(a number, b number);
create or replace package body perf_test is
procedure p1(a number, b number)
IS
x int;
begin
select count(*)
into x
from USER_tables;
--Stand alone SP
sp_perf_tst;
select count(*)
into x
from ALL_tables;
for i in 1..50
loop
select count(*)
into x
from BRANDS;
end loop;
select count(*)
into x
from USER_tables, USER_tables;
end p1;
end;
Thank you in advance.
Thanks,
Venu Gopal