Support Knowledge Base
Login Registration
Contents

Slow to Reload Large Amounts of Data in RDM

Both the problem and solution are Oracle specific.

Problem

The first import works as expected, but following imports are very slow. The problem is in the Oracle statistics, and the execution plan which Oracle uses for the query (you might see that the Nested Loops join strategy is used, but this join method should only be used while processing a smaller number of records). The problem can be fixed by updating the Oracle statistics using the following script.

Solution

Note that value SCHEMA_USERNAME should be changed.

declare
  v_u varchar2(1000) := 'SCHEMA_USERNAME';
  v_no number(10) := 200000;
  v_gpk varchar2(100) := 'GENERATEDPK';
  v_ggid varchar2(100) := 'GENERATEDGPK';
  v_username_column varchar2(100) := 'USERNAME';
  v_ord varchar2(100) := 'AC#ORDER';
begin
  for i in (select table_name from user_tables where dropped = 'NO') loop
    dbms_stats.unlock_table_stats(ownname => v_u, tabname => i.table_name); 
  end loop;
  for i in (select t.table_name, ti.table_name as ti, te.table_name as te, tu.table_name as tu, c.column_name from user_tab_cols c 
            inner join user_tables t on c.table_name = t.table_name  and t.dropped = 'NO'
              left join user_tables ti on ti.table_name || 'I' = t.table_name
                left join user_tables te on te.table_name || 'E' = t.table_name
                  left join user_tables tu on tu.table_name || 'U' = t.table_name
              ) loop
      dbms_stats.delete_column_stats(ownname => v_u, tabname => i.table_name, colname => i.column_name, no_invalidate => false, force => true);
      if (i.ti is not null) then
         if (i.column_name = v_gpk) then
            dbms_stats.set_column_stats(ownname => v_u, tabname => i.table_name, colname => i.column_name, distcnt => 2*v_no/3, nullcnt => v_no/3, no_invalidate => false, force => true);
         end if;
      elsif (i.te is not null) then
         if (i.column_name = v_gpk) then
           dbms_stats.set_column_stats(ownname => v_u, tabname => i.table_name, colname => i.column_name, distcnt => v_no, nullcnt => 0, no_invalidate => false, force => true);
         end if;
      elsif (i.tu is not null) then
         if (i.column_name = v_gpk) then
           dbms_stats.set_column_stats(ownname => v_u, tabname => i.table_name, colname => i.column_name, distcnt => v_no, nullcnt => 0, no_invalidate => false, force => true);
         end if;
      else
         if (i.column_name in (v_gpk, v_ggid)) then
           dbms_stats.set_column_stats(ownname => v_u, tabname => i.table_name, colname => i.column_name, distcnt => v_no, nullcnt => 0, no_invalidate => false, force => true);
         end if;
      end if;
  end loop; 
  for i in (select table_name from user_tables  where dropped = 'NO') loop
    dbms_stats.set_table_stats(ownname => v_u, tabname => i.table_name, force=>true, no_invalidate=>false, numrows=>v_no);
  end loop;
  for i in (select distinct c.index_name from user_ind_columns c inner join user_indexes i on c.INDEX_NAME = i.index_name and i.dropped = 'NO' where column_name = v_gpk) loop
    dbms_stats.set_index_stats(ownname => v_u, indname => i.index_name, numrows => v_no, numdist => v_no, indlevel => 100);
  end loop;
end;

This script should cause Oracle to use a different JOIN strategy while processing the queries for RDM tables. Make sure to check that the script changed in Oracle statistics after it is executed.

Content by label

There is no content with the specified labels