DBMS_PROFILER 查看存储过程执行时间
create table test (a int);
/
CREATE OR REPLACE PROCEDURE mytest
AS
BEGIN
--启动profiler
sys.DBMS_PROFILER.start_profiler ('xx');
FOR I IN 1 .. 100
LOOP
INSERT INTO test(a) VALUES (I);
END LOOP;
COMMIT;
--停止profiler
sys.DBMS_PROFILER.stop_profiler;
END;
/
begin
sp_test;
end;
/
CREATE OR REPLACE PROCEDURE mytest
AS
BEGIN
--启动profiler
sys.DBMS_PROFILER.start_profiler ('xx');
FOR I IN 1 .. 100
LOOP
INSERT INTO test(a) VALUES (I);
END LOOP;
COMMIT;
--停止profiler
sys.DBMS_PROFILER.stop_profiler;
END;
/
begin
sp_test;
end;
select t1.line#,t3.text,t1.total_occur,t1.total_time,min_time, max_time
from plsql_profiler_data t1,plsql_profiler_units t2,user_source t3
where t1.runid = t2.runid
and t2.unit_name='MYTEST'
and t2.unit_name = t3.name
and t1.line#=t3.line
and t1.runid=10
order by t1.line#
from plsql_profiler_data t1,plsql_profiler_units t2,user_source t3
where t1.runid = t2.runid
and t2.unit_name='MYTEST'
and t2.unit_name = t3.name
and t1.line#=t3.line
and t1.runid=10
order by t1.line#
LINE# TEXT TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
1 PROCEDURE sp_test 0 0 0 0
5 sys.DBMS_PROFILER.start_profiler ('xx'); 0 0 0 0
6 FOR I IN 1 .. 100 101 11723 97 313
8 INSERT INTO test(a) VALUES (I); 100 3601016 26234 554188
10 COMMIT; 1 93067 93067 93067
12 sys.DBMS_PROFILER.stop_profiler; 1 3552 3552 3552
13 END; 0 0 0 0
1 PROCEDURE sp_test 0 0 0 0
5 sys.DBMS_PROFILER.start_profiler ('xx'); 0 0 0 0
6 FOR I IN 1 .. 100 101 11723 97 313
8 INSERT INTO test(a) VALUES (I); 100 3601016 26234 554188
10 COMMIT; 1 93067 93067 93067
12 sys.DBMS_PROFILER.stop_profiler; 1 3552 3552 3552
13 END; 0 0 0 0
二、也可以使用pl/sql develop中的测试窗口来查看执行效率。
并且可以导出数据到html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25323853/viewspace-708805/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
glogin.sql
请登录后发表评论
登录
全部评论
