Skip to content

Commit 9d6bfe8

Browse files
jgebaljgebal
committed
Improved performance for reading data from output buffer.
Decreased read limit back to 1000 as it should not have significant performance impact anymore.
1 parent 7bae9ff commit 9d6bfe8

6 files changed

Lines changed: 28 additions & 25 deletions

File tree

‎source/core/output_buffers/ut_message_id_seq.sql‎

Lines changed: 0 additions & 1 deletion
This file was deleted.

‎source/core/output_buffers/ut_output_buffer_base.tps‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -18,10 +18,10 @@ create or replace type ut_output_buffer_base authid definer as object(
1818

1919
output_id raw(32),
2020
member procedure init(self in out nocopy ut_output_buffer_base),
21-
not instantiable member procedure close(self in ut_output_buffer_base),
22-
not instantiable member procedure send_line(self in ut_output_buffer_base, a_text varchar2, a_item_type varchar2 := null),
23-
not instantiable member procedure send_lines(self in ut_output_buffer_base, a_text_list ut_varchar2_rows, a_item_type varchar2 := null),
24-
not instantiable member procedure send_clob(self in ut_output_buffer_base, a_text clob, a_item_type varchar2 := null),
21+
not instantiable member procedure close(self in out nocopy ut_output_buffer_base),
22+
not instantiable member procedure send_line(self in out nocopy ut_output_buffer_base, a_text varchar2, a_item_type varchar2 := null),
23+
not instantiable member procedure send_lines(self in out nocopy ut_output_buffer_base, a_text_list ut_varchar2_rows, a_item_type varchar2 := null),
24+
not instantiable member procedure send_clob(self in out nocopy ut_output_buffer_base, a_text clob, a_item_type varchar2 := null),
2525
not instantiable member function get_lines(a_initial_timeout natural := null, a_timeout_sec natural := null) return ut_output_data_rows pipelined,
2626
not instantiable member function get_lines_cursor(a_initial_timeout natural := null, a_timeout_sec natural := null) return sys_refcursor,
2727
not instantiable member procedure lines_to_dbms_output(self in ut_output_buffer_base, a_initial_timeout natural := null, a_timeout_sec natural := null)

‎source/core/output_buffers/ut_output_table_buffer.tpb‎

Lines changed: 19 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@ create or replace type body ut_output_table_buffer is
2020
begin
2121
self.output_id := coalesce(a_output_id, sys_guid());
2222
self.start_date := sysdate;
23+
self.last_message_id := 0;
2324
self.init();
2425
self.cleanup_buffer();
2526
return;
@@ -38,41 +39,44 @@ create or replace type body ut_output_table_buffer is
3839
commit;
3940
end;
4041

41-
overriding member procedure close(self in ut_output_table_buffer) is
42+
overriding member procedure close(self in out nocopy ut_output_table_buffer) is
4243
pragma autonomous_transaction;
4344
begin
45+
self.last_message_id := self.last_message_id + 1;
4446
insert into ut_output_buffer_tmp(output_id, message_id, is_finished)
45-
values (self.output_id, ut_message_id_seq.nextval, 1);
47+
values (self.output_id, self.last_message_id, 1);
4648
commit;
4749
end;
4850

49-
overriding member procedure send_line(self in ut_output_table_buffer, a_text varchar2, a_item_type varchar2 := null) is
51+
overriding member procedure send_line(self in out nocopy ut_output_table_buffer, a_text varchar2, a_item_type varchar2 := null) is
5052
pragma autonomous_transaction;
5153
begin
5254
if a_text is not null or a_item_type is not null then
55+
self.last_message_id := self.last_message_id + 1;
5356
insert into ut_output_buffer_tmp(output_id, message_id, text, item_type)
54-
values (self.output_id, ut_message_id_seq.nextval, a_text, a_item_type);
57+
values (self.output_id, self.last_message_id, a_text, a_item_type);
5558
end if;
5659
commit;
5760
end;
5861

59-
overriding member procedure send_lines(self in ut_output_table_buffer, a_text_list ut_varchar2_rows, a_item_type varchar2 := null) is
62+
overriding member procedure send_lines(self in out nocopy ut_output_table_buffer, a_text_list ut_varchar2_rows, a_item_type varchar2 := null) is
6063
pragma autonomous_transaction;
6164
begin
6265
insert into ut_output_buffer_tmp(output_id, message_id, text, item_type)
63-
select self.output_id, ut_message_id_seq.nextval, t.column_value, a_item_type
66+
select self.output_id, self.last_message_id + rownum, t.column_value, a_item_type
6467
from table(a_text_list) t
6568
where t.column_value is not null or a_item_type is not null;
66-
69+
self.last_message_id := self.last_message_id + a_text_list.count;
6770
commit;
6871
end;
6972

70-
overriding member procedure send_clob(self in ut_output_table_buffer, a_text clob, a_item_type varchar2 := null) is
73+
overriding member procedure send_clob(self in out nocopy ut_output_table_buffer, a_text clob, a_item_type varchar2 := null) is
7174
pragma autonomous_transaction;
7275
begin
7376
if a_text is not null and a_text != empty_clob() or a_item_type is not null then
77+
self.last_message_id := self.last_message_id + 1;
7478
insert into ut_output_buffer_tmp(output_id, message_id, text, item_type)
75-
values (self.output_id, ut_message_id_seq.nextval, a_text, a_item_type);
79+
values (self.output_id, self.last_message_id, a_text, a_item_type);
7680
end if;
7781
commit;
7882
end;
@@ -91,7 +95,8 @@ create or replace type body ut_output_table_buffer is
9195
lc_long_sleep_time constant number(1) := 1; --sleep for 1 s when waiting long
9296
lc_long_wait_time constant number(1) := 1; --waiting more than 1 sec
9397
l_sleep_time number(2,1) := lc_short_sleep_time;
94-
lc_bulk_limit constant integer := 3000;
98+
lc_bulk_limit constant integer := 1000;
99+
l_max_message_id integer := lc_bulk_limit;
95100

96101
procedure remove_read_data(a_message_rowids t_rowid_tab) is
97102
pragma autonomous_transaction;
@@ -113,15 +118,15 @@ create or replace type body ut_output_table_buffer is
113118
begin
114119
while not l_finished loop
115120
with ordered_buffer as (
116-
select a.rowid, ut_output_data_row(a.text, a.item_type), is_finished
121+
select /*+ index(a) */ a.rowid, ut_output_data_row(a.text, a.item_type), is_finished
117122
from ut_output_buffer_tmp a
118123
where a.output_id = self.output_id
124+
and a.message_id <= l_max_message_id
119125
order by a.message_id
120126
)
121127
select b.*
122128
bulk collect into l_message_rowids, l_buffer_data, l_finished_flags
123-
from ordered_buffer b
124-
where rownum <= lc_bulk_limit;
129+
from ordered_buffer b;
125130

126131
--nothing fetched from output, wait and try again
127132
if l_buffer_data.count = 0 then
@@ -159,6 +164,7 @@ create or replace type body ut_output_table_buffer is
159164
);
160165
end if;
161166
end if;
167+
l_max_message_id := l_max_message_id + lc_bulk_limit;
162168
end loop;
163169
return;
164170
end;

‎source/core/output_buffers/ut_output_table_buffer.tps‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -17,12 +17,13 @@ create or replace type ut_output_table_buffer under ut_output_buffer_base (
1717
*/
1818

1919
start_date date,
20+
last_message_id number(38,0),
2021
constructor function ut_output_table_buffer(self in out nocopy ut_output_table_buffer, a_output_id raw := null) return self as result,
2122
overriding member procedure init(self in out nocopy ut_output_table_buffer),
22-
overriding member procedure send_line(self in ut_output_table_buffer, a_text varchar2, a_item_type varchar2 := null),
23-
overriding member procedure send_lines(self in ut_output_table_buffer, a_text_list ut_varchar2_rows, a_item_type varchar2 := null),
24-
overriding member procedure send_clob(self in ut_output_table_buffer, a_text clob, a_item_type varchar2 := null),
25-
overriding member procedure close(self in ut_output_table_buffer),
23+
overriding member procedure send_line(self in out nocopy ut_output_table_buffer, a_text varchar2, a_item_type varchar2 := null),
24+
overriding member procedure send_lines(self in out nocopy ut_output_table_buffer, a_text_list ut_varchar2_rows, a_item_type varchar2 := null),
25+
overriding member procedure send_clob(self in out nocopy ut_output_table_buffer, a_text clob, a_item_type varchar2 := null),
26+
overriding member procedure close(self in out nocopy ut_output_table_buffer),
2627
overriding member function get_lines(a_initial_timeout natural := null, a_timeout_sec natural := null) return ut_output_data_rows pipelined,
2728
overriding member function get_lines_cursor(a_initial_timeout natural := null, a_timeout_sec natural := null) return sys_refcursor,
2829
overriding member procedure lines_to_dbms_output(self in ut_output_table_buffer, a_initial_timeout natural := null, a_timeout_sec natural := null),

‎source/install.sql‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -99,7 +99,6 @@ alter session set current_schema = &&ut3_owner;
9999
--output buffer table
100100
@@install_component.sql 'core/output_buffers/ut_output_buffer_info_tmp.sql'
101101
@@install_component.sql 'core/output_buffers/ut_output_buffer_tmp.sql'
102-
@@install_component.sql 'core/output_buffers/ut_message_id_seq.sql'
103102
--output buffer table api
104103
@@install_component.sql 'core/output_buffers/ut_output_table_buffer.tps'
105104
@@install_component.sql 'core/output_buffers/ut_output_table_buffer.tpb'

‎source/uninstall_objects.sql‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -257,8 +257,6 @@ drop view ut_output_buffer_info_tmp;
257257

258258
drop table ut_output_buffer_info_tmp$;
259259

260-
drop sequence ut_message_id_seq;
261-
262260
drop type ut_output_data_rows force;
263261

264262
drop type ut_output_data_row force;

0 commit comments

Comments
 (0)