Using DBMS_PROFILER
exec DBMS_PROFILER.start_profiler;
ERROR at line 1:
ORA-06528: Error executing PL/SQL profiler
ORA-06512: at "SYS.DBMS_PROFILER", line 123
ORA-06512: at "SYS.DBMS_PROFILER", line 132
ORA-06512: at line 1
@?\rdbms\admin\proftab.sql
create or replace function tablecounter
return number
as
c
pls_integer;
begin
select
count(*) into c from lineitem;
return c;
end tablecounter;
exec
DBMS_PROFILER.start_profiler('tablecounter');
variable a number
exec :a:= tablecounter;
PL/SQL procedure successfully
completed.
exec DBMS_PROFILER.stop_profiler;
BREAK ON runid
ON run_owner ON run_comment ON run_secs
SELECT a.runid, a.run_owner, a.run_comment,
a.run_total_time / 1000000000 run_secs, c.total_occur,
c.total_time / 1000000000 line_total_secs, c.line#, u.text
FROM
plsql_profiler_runs a,
plsql_profiler_units b,
plsql_profiler_data c,
user_source u
WHERE a.runid =
b.runid
AND a.runid =
c.runid
AND
b.unit_name = u.NAME
AND c.line# =
u.line
/
|
RUNID
|
RUN_OWNER
|
RUN_COMMENT
|
RUN_SECS
|
TOTAL_OCCUR
|
LINE_TOTAL_SECS
|
LINE#
|
TEXT
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
2
|
0.000073
|
1
|
function tablecounter
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
0
|
0.000007
|
1
|
function tablecounter
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
2
|
0.000059
|
1
|
function tablecounter
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
1
|
0.000002
|
1
|
function tablecounter
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
1
|
0.309489
|
6
|
select count(*) into c from lineitem;
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
1
|
0.000001
|
7
|
return c;
|
|
1
|
TPCH2
|
tablecounter
|
99.864557
|
1
|
0.000006
|
8
|
end tablecounter;
|
