Overview of Parallel Execution - Data Warehousing

Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems (DSS)and data warehouses.You can also implementparallel execution on certain types of online transaction processing (OLTP) and hybrid systems. Parallel execution is sometimes called parallelism. Simply expressed, parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time. An example of this is when four processes handle four different quarters in a year instead of one process handling all four quarters by itself. The improvement in performance can be quite high. In this case, each quarter will be apartition,a smaller and more manageable unit of an index or table.

When to implement Parallel Execution
The most common use of parallel execution is in DSS and data warehousing environments.Complex queries, such as those involving joins of several tables or searches of very large tables,are often best executed in parallel.

Parallel execution is useful for many types of operations that access significant amounts of data. Parallel execution improves processing for:

  • Large table scans and joins
  • Creation of large indexes
  • Partitioned index scans
  • Bulk inserts, updates, and deletes
  • Aggregations and copying

You can also use parallel execution to access object types within an Oracle database. For example, use parallel execution to access LOBs (large objects).

Parallel execution benefits systems that have all of the following characteristics:

  • Symmetric multi-processors (SMP), clusters, or massively parallel systems.
  • Sufficient I/O bandwidth.
  • Underutilized or intermittently used CPUs (for example, systems where CPU usage is typically less than 30%).
  • Sufficient memory to support additional memory-intensive processes such as sorts, hashing, and I/O buffers.

If your system lacks any of these characteristics, parallel execution might not significantly improve performance. In fact, parallel execution can reduce system performance on overutilized systems or systems with small I/O bandwidth.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Data Warehousing Topics