Oracle Database - Parallel Operations

Card Puncher Data Processing

The fundamental difference and advantage of Oracle's capabilities, however, is that parallel data access and therefore the necessary data redistribution are not constrained by any given hardware architecture or database setup.

Operations That Can Be Parallelized

The Oracle server can use parallel execution for any of the following:

  • Access methods. For example, table scans, index full scans, and partitioned index range scans.
  • Join methods. For example, nested loop, sort merge, hash, and star transformation.
  • DDL statements. CREATE TABLE AS SELECT, CREATE INDEX, REBUILD INDEX, REBUILD INDEX PARTITION, and MOVE SPLIT COALESCE PARTITION
  • DML statements. For example, INSERT AS SELECT, updates, deletes, and MERGE operations.
  • Miscellaneous SQL operations. For example, GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, and ROLLUP, as well as aggregate and table functions.

See: Oracle Database - SQL Parallel Execution

Principle

OLTP and DataWarehouse

  • DataWarehouses: A DataWarehouse should always use parallel execution to achieve good performance.
  • OLTP: Specific operations in OLTP applications, such as batch operations, can also significantly benefit from parallel execution.

The ultimate goal: scalability

If you allocate twice the number of resources and achieve a processing time that is half of what it was with the original amount of resources, then the operation scales linearly.

Parallel Linearly Scalable Operation

The graph does not look linear because it shows the absolute processing time, not a relative speedup factor.

For example, using 2x the resources reduces the processing time from 360 to 180, and from 2x to 4x down to 90, both cases of linear scalability. It's just that the absolute performance gain is decreasing with higher number of resources.

When you spend two thirds of the original processing time to complete the task. When doubling the resources does not give the expected linear reduction in processing time, the operation does not scale as well.

In a database there are multiple components involved in processing a query, each having its own maximum processing power. Most notably CPUs, memory and Input/Output (I/O) all collaborate together.

For database processing you may experience a lack of scalability if you don't allocate resources in the correct quantities across the various components. For example, if you add CPU resources but you don't add I/O resources then the CPUs may not be able to retrieve the data fast enough to keep processing at full speed.

Examples of resource-intensive database operations

Examples of resource-intensive database operations include:

  • Large (long-running) queries: for example data warehouse analysis comparing one year's results with the results of the year prior
  • Building indexes on large tables (Index builds)
  • Gathering statistics in a large database
  • Loading a large amount of data into a database
  • Taking a database backup (RMAN backups)
  • SQL loader and SQL-based data loads
  • And more





Discover More
Db File Sequential Read
Oracle Database - db file sequential read Wait Event

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read. From SQL Developer,...
Card Puncher Data Processing
Oracle Database - CPU_COUNT parameter

CPU_COUNT specifies the number of CPUs core (processor) available for Oracle Database to use. CPU count is an automatically derived parameter by the Oracle system and is used to determine: the default...
A Synchronous Read
Oracle Database - DISK_ASYNCH_IO parameter

For optimum performance make sure you use asynchronous I/Os. asynchronous stream TRUE is the default parameter value for the majority of platforms. These parameters enable or disable the operating...
Oracle Database Hash Redistribution
Oracle Database - Data Redistribution (Parallel)

Data redistribution is not unique to the Oracle Database. In fact, this is one of the most fundamental principles of parallel processing, being used by every product that provides parallel capabilities....
Card Puncher Data Processing
Oracle Database - Data Warehousing

Parallelism, Partitioning, Result Set Cache, Advanced Compression Star transformation The biggest wait event for large data warehouse sites is: a “direct path read” wait event A shared...
Oracle Database Consumer Producer
Oracle Database - Producer / Consumer Model

The producer consumer model is used in order to execute a statement in parallel. Thesets of PX servers work in pairs: one set is producing rows (producer) and one set is consuming the rows (consumer)....
Oracle Database Sql Parallel Execution Principle
Oracle Database - SQL Parallel Execution

Parallel execution was first introduced in Oracle Version 7.3 in 1996 Oracle will make use of Parallel when a table or a statement is marked as “Parallel”. The concept of parallelism is when many...



Share this page:
Follow us:
Task Runner