Skip to content

Commit 0db7cab

Browse files
DDieterichDDieterich
committed
Optimize WT_PROFILER
1 parent 9a038af commit 0db7cab

4 files changed

Lines changed: 87 additions & 101 deletions

File tree

‎docs/core/Call_Tree_Diagrams.odg‎

645 Bytes
Binary file not shown.

‎docs/core/Call_Tree_Diagrams.pdf‎

2.84 KB
Binary file not shown.

‎src/core/wt_profiler.pkb‎

Lines changed: 86 additions & 100 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,11 @@ as
1010
,trigger_offset binary_integer
1111
,error_message varchar2(4000));
1212
g_rec rec_type;
13+
14+
TYPE anno_aa_type is table
15+
of varchar2(1)
16+
index by PLS_INTEGER;
17+
anno_aa anno_aa_type;
1318

1419

1520
----------------------
@@ -182,6 +187,76 @@ begin
182187

183188
end find_dbout;
184189

190+
------------------------------------------------------------
191+
procedure load_anno_aa
192+
is
193+
194+
cursor c_find_begin is
195+
select line
196+
,instr(text,'--%WTPLSQL_begin_ignore_lines%--') col
197+
from all_source
198+
where owner = g_rec.dbout_owner
199+
and name = g_rec.dbout_name
200+
and type = g_rec.dbout_type
201+
and text like '%--\%WTPLSQL_begin_ignore_lines\%--%' escape '\'
202+
order by line;
203+
buff_find_begin c_find_begin%ROWTYPE;
204+
205+
cursor c_find_end (in_line in number, in_col in number) is
206+
with q1 as (
207+
select line
208+
,instr(text,'--%WTPLSQL_end_ignore_lines%--') col
209+
from all_source
210+
where owner = g_rec.dbout_owner
211+
and name = g_rec.dbout_name
212+
and type = g_rec.dbout_type
213+
and line >= in_line
214+
and text like '%--\%WTPLSQL_end_ignore_lines\%--%' escape '\'
215+
)
216+
select line
217+
,col
218+
from q1
219+
where line > in_line
220+
or ( line = in_line
221+
and col > in_col)
222+
order by line
223+
,col;
224+
buff_find_end c_find_end%ROWTYPE;
225+
226+
begin
227+
228+
anno_aa.delete;
229+
230+
open c_find_begin;
231+
loop
232+
233+
fetch c_find_begin into buff_find_begin;
234+
exit when c_find_begin%NOTFOUND;
235+
236+
open c_find_end (buff_find_begin.line, buff_find_begin.col);
237+
fetch c_find_end into buff_find_end;
238+
if c_find_end%NOTFOUND
239+
then
240+
select max(line)
241+
into buff_find_end.line
242+
from all_source
243+
where owner = g_rec.dbout_owner
244+
and name = g_rec.dbout_name
245+
and type = g_rec.dbout_type;
246+
end if;
247+
close c_find_end;
248+
249+
for i in buff_find_begin.line + g_rec.trigger_offset ..
250+
buff_find_end.line + g_rec.trigger_offset
251+
loop
252+
anno_aa(i) := 'X';
253+
end loop;
254+
255+
end loop;
256+
close c_find_begin;
257+
258+
end load_anno_aa;
259+
185260
------------------------------------------------------------
186261
function find_excluded
187262
(in_text in varchar2
@@ -190,25 +265,6 @@ function find_excluded
190265
is
191266
begin
192267
out_not_exec_text := '';
193-
-- Find statements that can never be executed per DBMS_PROFILE
194-
-- for buf2 in (
195-
-- select name
196-
-- from all_identifiers
197-
-- where owner = g_rec.dbout_owner
198-
-- and object_name = g_rec.dbout_name
199-
-- and object_type = g_rec.dbout_type
200-
-- and usage = 'DEFINITION'
201-
-- group by name )
202-
-- loop
203-
-- if regexp_like (in_text
204-
-- ,'^[[:space:]]*end[[:space:]]+' || buf2.name || '[[:space:]]*[;]'
205-
-- ,'i')
206-
-- then
207-
-- out_not_exec_text := 'Exclude "END ' || buf2.name ||
208-
-- '; "Statement';
209-
-- return TRUE;
210-
-- end if;
211-
-- end loop;
212268
-- Find NOT_EXECUTABLE excluded statements
213269
-- MIN is a GROUP function and will always return a record
214270
select min(ne.text)
@@ -230,6 +286,7 @@ is
230286
begin
231287

232288
prof_rec.test_run_id := g_rec.test_run_id;
289+
load_anno_aa;
233290

234291
for buf1 in (
235292
select src.line
@@ -259,23 +316,25 @@ begin
259316
prof_rec.min_time := buf1.min_time;
260317
prof_rec.max_time := buf1.max_time;
261318
prof_rec.text := buf1.text;
262-
263-
-- Reset and set STATUS and NOT_EXEC_TEXT
264-
prof_rec.status := '';
265319
prof_rec.not_exec_text := '';
320+
266321
case
267-
when buf1.total_occur > 0
322+
when anno_aa.EXISTS(buf1.line)
268323
then
269-
-- Found Executed Statements
270-
prof_rec.status := 'EXEC';
324+
-- Found Annotated Statement
325+
prof_rec.status := 'ANNO';
271326
when find_excluded(buf1.text, prof_rec.not_exec_text)
272327
then
273-
-- Found Excluded Statements
328+
-- Found Excluded Statement
274329
prof_rec.status := 'EXCL';
330+
when buf1.total_occur > 0
331+
then
332+
-- Found Executed Statement
333+
prof_rec.status := 'EXEC';
275334
when buf1.total_occur = 0
276335
and buf1.total_time = 0
277336
then
278-
-- Found Not Executed Statements
337+
-- Found Not Executed Statement
279338
prof_rec.status := 'NOTX';
280339
else
281340
-- Everything else is unknown
@@ -293,77 +352,6 @@ begin
293352

294353
end insert_dbout_profile;
295354

296-
------------------------------------------------------------
297-
procedure update_anno_status
298-
is
299-
300-
PRAGMA AUTONOMOUS_TRANSACTION;
301-
302-
cursor c_find_begin is
303-
select line
304-
,instr(text,'--%WTPLSQL_begin_ignore_lines%--') col
305-
from all_source
306-
where owner = g_rec.dbout_owner
307-
and name = g_rec.dbout_name
308-
and type = g_rec.dbout_type
309-
and text like '%--\%WTPLSQL_begin_ignore_lines\%--%' escape '\'
310-
order by line;
311-
buff_find_begin c_find_begin%ROWTYPE;
312-
313-
cursor c_find_end (in_line in number, in_col in number) is
314-
with q1 as (
315-
select line
316-
,instr(text,'--%WTPLSQL_end_ignore_lines%--') col
317-
from all_source
318-
where owner = g_rec.dbout_owner
319-
and name = g_rec.dbout_name
320-
and type = g_rec.dbout_type
321-
and line >= in_line
322-
and text like '%--\%WTPLSQL_end_ignore_lines\%--%' escape '\'
323-
)
324-
select line
325-
,col
326-
from q1
327-
where line > in_line
328-
or ( line = in_line
329-
and col > in_col)
330-
order by line
331-
,col;
332-
buff_find_end c_find_end%ROWTYPE;
333-
334-
begin
335-
336-
open c_find_begin;
337-
loop
338-
fetch c_find_begin into buff_find_begin;
339-
340-
exit when c_find_begin%NOTFOUND;
341-
342-
open c_find_end (buff_find_begin.line, buff_find_begin.col);
343-
fetch c_find_end into buff_find_end;
344-
if c_find_end%NOTFOUND
345-
then
346-
buff_find_end.line := NULL;
347-
end if;
348-
close c_find_end;
349-
350-
update wt_dbout_profiles
351-
set status = 'ANNO'
352-
where status not in ('UNKN','EXCL')
353-
and test_run_id = g_rec.test_run_id
354-
and line >= buff_find_begin.line + g_rec.trigger_offset
355-
and ( buff_find_end.line is NULL
356-
OR line <= buff_find_end.line + g_rec.trigger_offset );
357-
358-
exit when buff_find_end.line is NULL;
359-
360-
end loop;
361-
close c_find_begin;
362-
363-
COMMIT;
364-
365-
end update_anno_status;
366-
367355

368356
---------------------
369357
-- Public Procedures
@@ -451,8 +439,6 @@ begin
451439

452440
insert_dbout_profile;
453441

454-
update_anno_status;
455-
456442
reset_g_rec;
457443

458444
end finalize;

‎src/core/wtplsql.pkb‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -261,7 +261,7 @@ begin
261261
begin
262262
g_test_runs_rec.runner_name := 'BOGUS';
263263
l_msg_in := 'Invalid RUNNER_NAME';
264-
l_err_in := 'ORA-20002: RUNNER_NAME is not valid';
264+
l_err_in := 'ORA-20002: RUNNER_NAME "BOGUS" is not valid';
265265
check_runner;
266266
test_sqlerrm;
267267
exception when others then

0 commit comments

Comments
 (0)