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.
There is no content with the specified labels