152 lines
4.3 KiB
SQL
152 lines
4.3 KiB
SQL
SET client_min_messages = WARNING;
|
|
\set VERBOSITY terse
|
|
\set ECHO all
|
|
|
|
drop procedure if exists do_something;
|
|
drop procedure if exists do_wrapper;
|
|
drop procedure if exists test_profiler_start;
|
|
drop procedure if exists test_profiler_flush;
|
|
drop procedure if exists test_profiler_version;
|
|
drop procedure if exists test_profiler_pause;
|
|
drop procedure if exists test_profiler_resume;
|
|
drop procedure if exists test_profiler_resume2;
|
|
drop procedure if exists test_profiler_version_check;
|
|
drop procedure if exists test_profiler_start_ext;
|
|
|
|
create extension gms_profiler;
|
|
|
|
create or replace procedure do_something (p_times in number) as
|
|
l_dummy number;
|
|
begin
|
|
for i in 1 .. p_times loop
|
|
select l_dummy +1
|
|
into l_dummy;
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
create or replace procedure do_wrapper (p_times in number) as
|
|
begin
|
|
for i in 1 .. p_times loop
|
|
do_something(p_times);
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_start () as
|
|
declare
|
|
l_result binary_integer;
|
|
begin
|
|
l_result := gms_profiler.start_profiler('test_profiler', 'simple');
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.stop_profiler();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_flush () as
|
|
declare
|
|
l_result binary_integer;
|
|
begin
|
|
l_result := gms_profiler.start_profiler('test_profiler_flush', 'flush');
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.flush_data();
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.stop_profiler();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_version () as
|
|
declare
|
|
major_v binary_integer;
|
|
minor_v binary_integer;
|
|
begin
|
|
select major, minor into major_v, minor_v from gms_profiler.get_version();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_pause () as
|
|
declare
|
|
l_result binary_integer;
|
|
begin
|
|
l_result := gms_profiler.start_profiler('test_profiler_pause', 'pause');
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.pause_profiler();
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.stop_profiler();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_resume () as
|
|
declare
|
|
l_result binary_integer;
|
|
begin
|
|
l_result := gms_profiler.start_profiler('test_profiler_resume', 'resume1');
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.pause_profiler();
|
|
l_result := gms_profiler.resume_profiler();
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.stop_profiler();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_resume2 () as
|
|
declare
|
|
l_result binary_integer;
|
|
begin
|
|
l_result := gms_profiler.resume_profiler();
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.start_profiler('test_profiler_resume2', 'resume2');
|
|
l_result := gms_profiler.pause_profiler();
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.pause_profiler();
|
|
l_result := gms_profiler.resume_profiler();
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.stop_profiler();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_version_check () as
|
|
declare
|
|
l_result binary_integer;
|
|
begin
|
|
l_result := gms_profiler.internal_version_check();
|
|
end;
|
|
/
|
|
|
|
create or replace procedure test_profiler_start_ext () as
|
|
declare
|
|
l_result binary_integer;
|
|
runid binary_integer;
|
|
begin
|
|
select run_number, run_result into runid, l_result from gms_profiler.start_profiler_ext('start_profiler_ext');
|
|
do_wrapper(p_times => 2);
|
|
l_result := gms_profiler.stop_profiler();
|
|
end;
|
|
/
|
|
|
|
call test_profiler_start();
|
|
call test_profiler_flush();
|
|
call test_profiler_version();
|
|
call test_profiler_pause();
|
|
call test_profiler_resume();
|
|
call test_profiler_resume2();
|
|
call test_profiler_version_check();
|
|
call test_profiler_start_ext();
|
|
|
|
select runid, run_comment, run_comment1 from gms_profiler.plsql_profiler_runs order by runid;
|
|
select runid, unit_number, unit_type, unit_name from gms_profiler.plsql_profiler_units order by runid, unit_number;
|
|
select runid, unit_number, line#, total_occur from gms_profiler.plsql_profiler_data order by runid, unit_number, line#;
|
|
|
|
drop procedure if exists do_something;
|
|
drop procedure if exists do_wrapper;
|
|
drop procedure if exists test_profiler_start;
|
|
drop procedure if exists test_profiler_flush;
|
|
drop procedure if exists test_profiler_version;
|
|
drop procedure if exists test_profiler_pause;
|
|
drop procedure if exists test_profiler_resume;
|
|
drop procedure if exists test_profiler_resume2;
|
|
drop procedure if exists test_profiler_version_check;
|
|
drop procedure if exists test_profiler_start_ext;
|
|
|
|
reset client_min_messages;
|