Added no_parallel hints to all SQLs to assure stable performance on p…

…arallel-enabled systems.
utPLSQL · jgebal · Nov 17, 2021 · Feb 23, 2020 · Feb 23, 2020 · Feb 23, 2020
commit f35694e744e2bd8d322a0f3a153cfe54c36b5525
diff --git a/source/api/ut.pkb b/source/api/ut.pkb
@@ -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';

diff --git a/source/check_object_grants.sql b/source/check_object_grants.sql
@@ -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)

diff --git a/source/core/annotations/ut_annotation_cache_manager.pkb b/source/core/annotations/ut_annotation_cache_manager.pkb
@@ -21,41 +21,41 @@ create or replace package body ut_annotation_cache_manager as
     l_timestamp         timestamp := systimestamp;
     pragma autonomous_transaction;
   begin
-    update /*+ noparallel */ 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 /*+ noparallel */ 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 /*+ noparallel */ 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 /*+ noparallel */ 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 /*+ noparallel */ 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 /*+ noparallel */ 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;
@@ -67,9 +67,9 @@ create or replace package body ut_annotation_cache_manager as
     pragma autonomous_transaction;
   begin
 
-    delete /*+ noparallel */ 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
@@ -78,26 +78,26 @@ create or replace package body ut_annotation_cache_manager as
                and o.needs_refresh = 'Y'
            );
 
-    update /*+ noparallel */ 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 /*+ noparallel */ 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 /*+ noparallel */
+    merge /*+ no_parallel */
       into ut_annotation_cache_info i
-      using (select o.object_name, o.object_type, o.object_owner
+      using (select /*+ no_parallel */ o.object_name, o.object_type, o.object_owner
                from table(a_objects) o
               where o.needs_refresh = 'Y'
         ) o
@@ -117,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,
@@ -136,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;
@@ -150,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 /*+ noparallel */ 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;
@@ -161,9 +161,9 @@ create or replace package body ut_annotation_cache_manager as
     pragma autonomous_transaction;
   begin
 
-    delete /*+ noparallel */ 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
@@ -176,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(
@@ -205,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 /*+ noparallel */ 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 /*+ noparallel */ 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 /*+ noparallel */ 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;

diff --git a/source/core/annotations/ut_annotation_manager.pkb b/source/core/annotations/ut_annotation_manager.pkb
@@ -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
@@ -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,
@@ -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 '\'
@@ -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;
 
@@ -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
@@ -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

diff --git a/source/core/annotations/ut_annotation_parser.pkb b/source/core/annotations/ut_annotation_parser.pkb
@@ -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;

diff --git a/source/core/coverage/ut_coverage.pkb b/source/core/coverage/ut_coverage.pkb
@@ -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 )

diff --git a/source/core/coverage/ut_coverage_helper.pkb b/source/core/coverage/ut_coverage_helper.pkb
@@ -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;
@@ -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
@@ -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
@@ -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
@@ -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 );

diff --git a/source/core/coverage/ut_coverage_helper_block.pkb b/source/core/coverage/ut_coverage_helper_block.pkb
@@ -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,

diff --git a/source/core/coverage/ut_coverage_helper_profiler.pkb b/source/core/coverage/ut_coverage_helper_profiler.pkb
@@ -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

diff --git a/source/core/coverage/ut_coverage_reporter_base.tpb b/source/core/coverage/ut_coverage_reporter_base.tpb
@@ -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;
@@ -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;
 

diff --git a/source/core/output_buffers/ut_output_buffer_base.tpb b/source/core/output_buffers/ut_output_buffer_base.tpb
@@ -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;
@@ -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;