Oracle database provides various methods of parallelism to improve performance, which are as follows:
1. Parallel Query: In this method, the processing of a SQL statement is divided into multiple smaller tasks that are executed concurrently by multiple CPUs or nodes. Each parallel query operation has a degree of parallelism, which indicates the number of parallel execution servers that will be used.
For example, consider a query to select all the customers from a large table:
SELECT * FROM customers;If the table is large, the query can be parallelized to improve the response time:
SELECT /*+ parallel(customers, 4) */ * FROM customers;In this example, we are using parallel query with a degree of parallelism of 4. This means that the query will be divided into 4 smaller tasks and each task will be executed by a separate parallel execution server.
The impact of parallel query on performance depends on the degree of parallelism, the size of the table, and the availability of resources. In general, parallel query can provide significant performance gains for larger tables and high-concurrency workloads.
2. Parallel DML: In this method, the processing of a DML (Data Manipulation Language) statement such as INSERT, UPDATE, or DELETE is divided into multiple smaller tasks that are executed concurrently by multiple CPUs or nodes. Each parallel DML operation has a degree of parallelism, which indicates the number of parallel execution servers that will be used.
For example, consider an INSERT statement to insert a large number of rows into a table:
INSERT INTO orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date FROM new_orders;If the number of rows to be inserted is large, the statement can be parallelized to improve the performance:
INSERT /*+ append parallel(orders, 4) */ INTO orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date FROM new_orders;In this example, we are using parallel DML with a degree of parallelism of 4. This means that the INSERT statement will be divided into 4 smaller tasks and each task will be executed by a separate parallel execution server.
The impact of parallel DML on performance depends on the degree of parallelism, the size of the table, and the availability of resources. In general, parallel DML can provide significant performance gains for larger tables and high-concurrency workloads.
3. Parallel Index Creation: In this method, the creation of an index on a large table is divided into multiple smaller tasks that are executed concurrently by multiple CPUs or nodes. Each parallel index creation operation has a degree of parallelism, which indicates the number of parallel execution servers that will be used.
For example, consider the creation of an index on a large table:
CREATE INDEX idx_orders ON orders (order_date);If the table is large, the index creation can be parallelized to improve the performance:
CREATE /*+ parallel(orders, 4) */ INDEX idx_orders ON orders (order_date);In this example, we are using parallel index creation with a degree of parallelism of 4. This means that the index creation will be divided into 4 smaller tasks and each task will be executed by a separate parallel execution server.
The impact of parallel index creation on performance depends on the degree of parallelism, the size of the table, and the availability of resources. In general, parallel index creation can provide significant performance gains for larger tables and high-concurrency workloads.
4. Parallel Backup and Recovery: In this method, the backup or recovery of a database is divided into multiple smaller tasks that are executed concurrently by multiple CPUs or nodes. Each parallel backup or recovery operation has a degree of parallelism, which indicates the number of parallel execution servers that will be used.
For example, consider the backup of a large database:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;If the database is large, the backup can be parallelized to improve the performance:
RMAN> BACKUP /*+ parallelism(4) */ DATABASE PLUS ARCHIVELOG;In this example, we are using parallel backup with a degree of parallelism of 4. This means that the backup operation will be divided into 4 smaller tasks and each task will be executed by a separate parallel execution server.
The impact of parallel backup and recovery on performance depends on the degree of parallelism, the size of the database, and the availability of resources. In general, parallel backup and recovery can provide significant performance gains for larger databases and high-concurrency workloads.
In conclusion, parallelism can significantly improve the performance of Oracle databases for larger tables and high-concurrency workloads. However, the impact of parallelism on performance depends on several factors such as the degree of parallelism, the size of the table or database, and the availability of resources. It is recommended to perform thorough testing and analysis before implementing parallelism in a production database.