8000 Feature/fix cache when parallel enabled by jgebal · Pull Request #1160 · utPLSQL/utPLSQL · GitHub
[go: up one dir, main page]
Skip to content

Feature/fix cache when parallel enabled #1160

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Nov 11, 2021
Merged

Feature/fix cache when parallel enabled #1160

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
14eca7f
Adding noparallel hint to elements causing issues on parallel-enabled…
jgebal Aug 17, 2021
f35694e
Added no_parallel hints to all SQLs to assure stable performance on p…
jgebal Nov 7, 2021
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
8000
Diff view
Diff view
8000
2 changes: 1 addition & 1 deletion source/api/ut.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -674,7 +674,7 @@ create or replace package body ut is
procedure set_nls is
begin
if g_nls_date_format is null then
select nsp.value
select /*+ no_parallel */ nsp.value
into g_nls_date_format
from nls_session_parameters nsp
where parameter = 'NLS_DATE_FORMAT';
Expand Down
2 changes: 1 addition & 1 deletion source/check_object_grants.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -24,7 +24,7 @@ begin
l_target_table := get_view('dba_tab_privs');
l_owner_column := case when l_target_table like 'dba%' then 'owner' else 'table_schema' end;
execute immediate q'[
select listagg(' - '||object_name,CHR(10)) within group(order by object_name)
select /*+ no_parallel */ listagg(' - '||object_name,CHR(10)) within group(order by object_name)
from (
select column_value as object_name
from table(:l_expected_grants)
Expand Down
51 changes: 26 additions & 25 deletions source/core/annotations/ut_annotation_cache_manager.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -21,41 +21,41 @@ create or replace package body ut_annotation_cache_manager as
l_timestamp timestamp := systimestamp;
pragma autonomous_transaction;
begin
update ut_annotation_cache_schema s
update /*+ no_parallel */ ut_annotation_cache_schema s
set s.max_parse_time = l_timestamp
where s.object_type = a_object.object_type and s.object_owner = a_object.object_owner;

if sql%rowcount = 0 then
insert into ut_annotation_cache_schema s
insert /*+ no_parallel */ into ut_annotation_cache_schema s
(object_owner, object_type, max_parse_time)
values (a_object.object_owner, a_object.object_type, l_timestamp);
end if;

-- if not in trigger, or object has annotations
if ora_sysevent is null or a_object.annotations is not null and a_object.annotations.count > 0 then

update ut_annotation_cache_info i
update /*+ no_parallel */ ut_annotation_cache_info i
set i.parse_time = l_timestamp
where (i.object_owner, i.object_name, i.object_type)
in ((a_object.object_owner, a_object.object_name, a_object.object_type))
returning cache_id into l_cache_id;

if sql%rowcount = 0 then

insert into ut_annotation_cache_info
insert /*+ no_parallel */ into ut_annotation_cache_info
(cache_id, object_owner, object_name, object_type, parse_time)
values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, l_timestamp)
returning cache_id into l_cache_id;
end if;

end if;

delete from ut_annotation_cache c where cache_id = l_cache_id;
delete /*+ no_parallel */ from ut_annotation_cache c where cache_id = l_cache_id;

if a_object.annotations is not null and a_object.annotations.count > 0 then
insert into ut_annotation_cache
insert /*+ no_parallel */ into ut_annotation_cache
(cache_id, annotation_position, annotation_name, annotation_text, subobject_name)
select l_cache_id, a.position, a.name, a.text, a.subobject_name
select /*+ no_parallel */ l_cache_id, a.position, a.name, a.text, a.subobject_name
from table(a_object.annotations) a;
end if;
commit;
Expand All @@ -67,9 +67,9 @@ create or replace package body ut_annotation_cache_manager as
pragma autonomous_transaction;
begin

delete from ut_annotation_cache c
delete /*+ no_parallel */ from ut_annotation_cache c
where c.cache_id
in (select i.cache_id
in (select /*+ no_parallel */ i.cache_id
from ut_annotation_cache_info i
join table (a_objects) o
on o.object_name = i.object_name
Expand All @@ -78,25 +78,26 @@ create or replace package body ut_annotation_cache_manager as
and o.needs_refresh = 'Y'
);

update ut_annotation_cache_schema s
update /*+ no_parallel */ ut_annotation_cache_schema s
set s.max_parse_time = l_timestamp
where (s.object_owner, s.object_type)
in (
select o.object_owner, o.object_type
select /*+ no_parallel */ o.object_owner, o.object_type
from table(a_objects) o
where o.needs_refresh = 'Y'
);

if sql%rowcount = 0 then
insert into ut_annotation_cache_schema s
insert /*+ no_parallel */ into ut_annotation_cache_schema s
(object_owner, object_type, max_parse_time)
select distinct o.object_owner, o.object_type, l_timestamp
select /*+ no_parallel */ distinct o.object_owner, o.object_type, l_timestamp
from table(a_objects) o
where o.needs_refresh = 'Y';
end if;

merge into ut_annotation_cache_info i
using (select o.object_name, o.object_type, o.object_owner
merge /*+ no_parallel */
into ut_annotation_cache_info i
using (select /*+ no_parallel */ o.object_name, o.object_type, o.object_owner
from table(a_objects) o
where o.needs_refresh = 'Y'
) o
Expand All @@ -116,7 +117,7 @@ create or replace package body ut_annotation_cache_manager as
function get_cached_objects_list(a_object_owner varchar2, a_object_type varchar2, a_parsed_after timestamp := null) return ut_annotation_objs_cache_info is
l_result ut_annotation_objs_cache_info;
begin
select ut_annotation_obj_cache_info(
select /*+ no_parallel */ ut_annotation_obj_cache_info(
object_owner => i.object_owner,
object_name => i.object_name,
object_type => i.object_type,
Expand All @@ -135,7 +136,7 @@ create or replace package body ut_annotation_cache_manager as
l_result t_cache_schema_info;
begin
begin
select *
select /*+ no_parallel */ *
into l_result
from ut_annotation_cache_schema s
where s.object_type = a_object_type and s.object_owner = a_object_owner;
Expand All @@ -149,7 +150,7 @@ create or replace package body ut_annotation_cache_manager as
procedure set_fully_refreshed(a_object_owner varchar2, a_object_type varchar2) is
pragma autonomous_transaction;
begin
update ut_annotation_cache_schema s
update /*+ no_parallel */ ut_annotation_cache_schema s
set s.full_refresh_time = s.max_parse_time
where s.object_owner = a_object_owner
and s.object_type = a_object_type;
Expand All @@ -160,9 +161,9 @@ create or replace package body ut_annotation_cache_manager as
pragma autonomous_transaction;
begin

delete from ut_annotation_cache_info i
delete /*+ no_parallel */ from ut_annotation_cache_info i
where exists (
select 1 from table (a_objects) o
select /*+ no_parallel */ 1 from table (a_objects) o
where o.object_name = i.object_name
and o.object_type = i.object_type
and o.object_owner = i.object_owner
Expand All @@ -175,7 +176,7 @@ create or replace package body ut_annotation_cache_manager as
l_results sys_refcursor;
begin
open l_results for
select ut_annotated_object(
select /*+ no_parallel */ ut_annotated_object(
i.object_owner, i.object_name, i.object_type, i.parse_time,
cast(
collect(
Expand Down Expand Up @@ -204,15 +205,15 @@ create or replace package body ut_annotation_cache_manager as
else
l_filter := case when a_object_owner is null then ':a_object_owner is null' else 'object_owner = :a_object_owner' end;
l_filter := l_filter || ' and ' || case when a_object_type is null then ':a_object_type is null' else 'object_type = :a_object_type' end;
l_cache_filter := ' c.cache_id in (select i.cache_id from ut_annotation_cache_info i where ' || l_filter || ' )';
l_cache_filter := ' c.cache_id in (select /*+ no_parallel */ i.cache_id from ut_annotation_cache_info i where ' || l_filter || ' )';
end if;
execute immediate 'delete from ut_annotation_cache c where ' || l_cache_filter
execute immediate 'delete /*+ no_parallel */ from ut_annotation_cache c where ' || l_cache_filter
using a_object_owner, a_object_type;

execute immediate ' delete from ut_annotation_cache_info i where ' || l_filter
execute immediate ' delete /*+ no_parallel */ from ut_annotation_cache_info i where ' || l_filter
using a_object_owner, a_object_type;

execute immediate ' delete from ut_annotation_cache_schema s where ' || l_filter
execute immediate ' delete /*+ no_parallel */ from ut_annotation_cache_schema s where ' || l_filter
using a_object_owner, a_object_type;

commit;
Expand Down
12 changes: 6 additions & 6 deletions source/core/annotations/ut_annotation_manager.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -34,7 +34,7 @@ create or replace package body ut_annotation_manager as
l_cached_objects := ut_annotation_cache_manager.get_cached_objects_list( a_object_owner, a_object_type );

if l_cached_objects is not empty then
execute immediate 'select /*+ cardinality(i '||ut_utils.scale_cardinality(cardinality(l_cached_objects))||') */
execute immediate 'select /*+ no_parallel cardinality(i '||ut_utils.scale_cardinality(cardinality(l_cached_objects))||') */
value(i)
from table( :l_data ) i
where
Expand Down Expand Up @@ -71,7 +71,7 @@ create or replace package body ut_annotation_manager as
--limit the list to objects that exist and are visible to the invoking user
--enrich the list by info about cache validity
execute immediate
'select /*+ cardinality(i '||ut_utils.scale_cardinality(cardinality(l_cached_objects))||') */
'select /*+ no_parallel cardinality(i '||ut_utils.scale_cardinality(cardinality(l_cached_objects))||') */
'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
object_owner => o.owner,
object_name => o.object_name,
Expand Down Expand Up @@ -108,7 +108,7 @@ create or replace package body ut_annotation_manager as
begin
l_card := ut_utils.scale_cardinality(cardinality(a_objects_to_refresh));
open l_result for
q'[select x.name, x.text
q'[select /*+ no_parallel */ x.name, x.text
from (select /*+ cardinality( r ]'||l_card||q'[ )*/
s.name, s.text, s.line,
max(case when s.text like '%--%\%%' escape '\'
Expand Down Expand Up @@ -252,7 +252,7 @@ create or replace package body ut_annotation_manager as
l_sql_lines := ut_utils.convert_collection( ut_utils.clob_to_table(l_sql_clob) );
end if;
open l_result for
select a_object_name as name, column_value||chr(10) as text from table(l_sql_lines);
select /*+ no_parallel */ a_object_name as name, column_value||chr(10) as text from table(l_sql_lines);
return l_result;
end;

Expand All @@ -261,7 +261,7 @@ create or replace package body ut_annotation_manager as
l_sources_view varchar2(200) := ut_metadata.get_source_view_name();
begin
open l_result for
q'[select :a_object_name, s.text
q'[select /*+ no_parallel */ :a_object_name, s.text
from ]'||l_sources_view||q'[ s
where s.type = :a_object_type
and s.owner = :a_object_owner
Expand All @@ -279,7 +279,7 @@ create or replace package body ut_annotation_manager as
'GSMCATUSER','GSMUSER','ORACLE_OCM','OUTLN','REMOTE_SCHEDULER_AGENT','SYS','SYS$UMF',
'SYSBACKUP','SYSDG','SYSKM','SYSRAC','SYSTEM','WMSYS','XDB','XS$NULL');
$else
select username bulk collect into l_restricted_users
select /*+ no_parallel */ username bulk collect into l_restricted_users
from all_users where oracle_maintained = 'Y';
$end
if ora_dict_obj_owner member of l_restricted_users then
Expand Down
2 changes: 1 addition & 1 deletion source/core/annotations/ut_annotation_parser.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -210,7 +210,7 @@ create or replace package body ut_annotation_parser as

dbms_lob.freetemporary(l_source);

select value(x) bulk collect into l_result from table(l_annotations) x order by x.position;
select /*+ no_parallel */ value(x) bulk collect into l_result from table(l_annotations) x order by x.position;

return l_result;
end parse_object_annotations;
Expand Down
2 changes: 1 addition & 1 deletion source/core/coverage/ut_coverage.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -77,7 +77,7 @@ create or replace package body ut_coverage is
end as to_be_skipped
from sources s
)
select full_name, owner, name, type, line, to_be_skipped, text
select /*+ no_parallel */ full_name, owner, name, type, line, to_be_skipped, text
from coverage_sources s
-- Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
where (s.owner, s.name) not in ( select /*+ cardinality(el {skipped_objects_cardinality})*/el.owner, el.name from table(:l_skipped_objects) el )
Expand Down
10 changes: 5 additions & 5 deletions source/core/coverage/ut_coverage_helper.pkb
View file
Open in desktop
F42D
Original file line number Diff line number Diff line change
Expand Up @@ -35,7 +35,7 @@ create or replace package body ut_coverage_helper is
procedure insert_into_tmp_table(a_data t_coverage_sources_tmp_rows) is
begin
forall i in 1 .. a_data.count
insert into ut_coverage_sources_tmp
insert /*+ no_parallel */ into ut_coverage_sources_tmp
(full_name,owner,name,type,line,text,to_be_skipped)
values(a_data(i).full_name,a_data(i).owner,a_data(i).name,a_data(i).type,a_data(i).line,a_data(i).text,a_data(i).to_be_skipped);
end;
Expand All @@ -49,7 +49,7 @@ create or replace package body ut_coverage_helper is
function is_tmp_table_populated return boolean is
l_result integer;
begin
select 1 into l_result from ut_coverage_sources_tmp where rownum = 1;
select /*+ no_parallel */ 1 into l_result from ut_coverage_sources_tmp where rownum = 1;
return (l_result = 1);
exception
when no_data_found then
Expand All @@ -60,7 +60,7 @@ create or replace package body ut_coverage_helper is
l_result t_tmp_table_objects_crsr;
begin
open l_result for
select o.owner, o.name, o.type, o.full_name, max(o.line) as lines_count,
select /*+ no_parallel */ o.owner, o.name, o.type, o.full_name, max(o.line) as lines_count,
cast(
collect(decode(to_be_skipped, 'Y', to_char(line))) as ut_varchar2_list
) as to_be_skipped_list
Expand All @@ -73,7 +73,7 @@ create or replace package body ut_coverage_helper is
function get_tmp_table_object_lines(a_owner varchar2, a_object_name varchar2) return ut_varchar2_list is
l_result ut_varchar2_list;
begin
select rtrim(s.text,chr(10)) as text
select /*+ no_parallel */ rtrim(s.text,chr(10)) as text
bulk collect into l_result
from ut_coverage_sources_tmp s
where s.owner = a_owner
Expand All @@ -86,7 +86,7 @@ create or replace package body ut_coverage_helper is
procedure set_coverage_run_ids( a_coverage_run_id raw, a_line_coverage_id integer, a_block_coverage_id integer ) is
pragma autonomous_transaction;
begin
insert into ut_coverage_runs
insert /*+ no_parallel */ into ut_coverage_runs
( coverage_run_id, line_coverage_id, block_coverage_id )
values
( a_coverage_run_id, a_line_coverage_id, a_block_coverage_id );
Expand Down
3 changes: 2 additions & 1 deletion source/core/coverage/ut_coverage_helper_block.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -46,7 +46,8 @@ create or replace package body ut_coverage_helper_block is
l_ut_owner varchar2(250) := ut_utils.ut_owner;
begin
execute immediate q'[
select line as line,
select /*+ no_parallel */
line as line,
count(block) as blocks,
sum(covered) as covered_blocks
from (select line,
Expand Down
2 changes: 1 addition & 1 deletion source/core/coverage/ut_coverage_helper_profiler.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -58,7 +58,7 @@ create or replace package body ut_coverage_helper_profiler is
function proftab_results(a_object ut_coverage_helper.t_tmp_table_object, a_coverage_run_id raw) return t_proftab_rows is
l_coverage_rows t_proftab_rows;
begin
select
select /*+ no_parallel */
d.line#,
case when sum(d.total_occur) = 0 and sum(d.total_time) > 0 then 1 else sum(d.total_occur) end total_occur
bulk collect into l_coverage_rows
Expand Down
4 changes: 2 additions & 2 deletions source/core/coverage/ut_coverage_reporter_base.tpb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -93,7 +93,7 @@ create or replace type body ut_coverage_reporter_base is
(l_reporter as ut_output_reporter_base).before_calling_run(null);
l_reporter.after_calling_run( ut_run( a_coverage_options => a_coverage_options, a_client_character_set => a_client_character_set ) );
l_reporter.on_finalize(null);
for i in (select x.text from table(l_reporter.get_lines(1, 1)) x ) loop
for i in (select /*+ no_parallel */ x.text from table(l_reporter.get_lines(1, 1)) x ) loop
pipe row (i.text);
end loop;
return;
Expand All @@ -107,7 +107,7 @@ create or replace type body ut_coverage_reporter_base is
(l_reporter as ut_output_reporter_base).before_calling_run(null);
l_reporter.after_calling_run( ut_run( a_coverage_options => a_coverage_options, a_client_character_set => a_client_character_set ) );
l_reporter.on_finalize(null);
open l_result for select x.text from table(l_reporter.get_lines(1, 1)) x;
open l_result for select /*+ no_parallel */ x.text from table(l_reporter.get_lines(1, 1)) x;
return l_result;
end;

Expand Down
8 changes: 4 additions & 4 deletions source/core/output_buffers/ut_output_buffer_base.tpb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -25,11 +25,11 @@ create or replace type body ut_output_buffer_base is
self.output_id := coalesce(a_output_id, self.output_id, sys_guid());
self.start_date := coalesce(self.start_date, sysdate);
self.last_message_id := 0;
select count(*) into l_exists from ut_output_buffer_info_tmp where output_id = self.output_id;
select /*+ no_parallel */ count(*) into l_exists from ut_output_buffer_info_tmp where output_id = self.output_id;
if ( l_exists > 0 ) then
update ut_output_buffer_info_tmp set start_date = self.start_date where output_id = self.output_id;
update /*+ no_parallel */ ut_output_buffer_info_tmp set start_date = self.start_date where output_id = self.output_id;
else
insert into ut_output_buffer_info_tmp(output_id, start_date) values (self.output_id, self.start_date);
insert /*+ no_parallel */ into ut_output_buffer_info_tmp(output_id, start_date) values (self.output_id, self.start_date);
end if;
commit;
self.is_closed := 0;
Expand All @@ -39,7 +39,7 @@ create or replace type body ut_output_buffer_base is
l_lines sys_refcursor;
begin
open l_lines for
select text, item_type
select /*+ no_parallel */ text, item_type
from table(self.get_lines(a_initial_timeout, a_timeout_sec));
return l_lines;
end;
Expand Down
Loading
0