Consider Parallel Query configuration For Large Tables Loading

#ETL #DAC #OBIA #EDW

The Data Warehouse Administration Console (DAC) leverages the Oracle Parallel Query option for computing statistics

and building indexes on target tables. By default DAC creates indexes with the 'PARALLEL' clause and computes

statistics with pre-calculated degree of parallelism. Refer to the init.ora template files, located in

<ORACLEBI_HOME>\dwrep\Documentation for details on setting the following parameters:

parallel_max_servers

parallel_min_servers

parallel_threads_per_cpu

Important: 

Parallel execution is non-scalable. It could easily lead to increased resource contention, creating I/O bottlenecks, and increasing response time when the resources are shared by many concurrent transactions.

Since DAC creates indexes and computes statistics on target tables in parallel on a single table and across multiple tables, the parallel execution may cause performance problems if the values parallel_max_servers and parallel_threads_per_cpu are too high. 

-The system load from parallel operations can be observed by executing the

following query:

SQL> select name, value from v$sysstat where name like 'Parallel%';

-Reduce the "parallel_threads_per_cpu" and "parallel_max_servers" value if the system is overloaded.