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博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-708805/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25323853/viewspace-708805/