Author: Sanjay Mishra

Whenever you talk about data, the databases act as the corner stone. Most applications involve storage of large amounts of data however processing this data can be every slow. Therefore the processes are executed over multiple processes which significantly improve the performance.

Parallel Execution in Oracle:

Oracle's parallel execution features include the following:

These features should be used with care in order to avoid instability or poor performance of the application.

Parallel Execution for Small Data:

Parallel execution for a small amount of data can cause overhead of breaking the operation into subtasks and time required to execute the query in series on multiple processors.

Parallel Execution for Large Data:

Decision Support Systems (DSS) handle large amounts of data, execute complex queries, and load large amounts of data in bulk. Similarly Online Transaction Processing (OLTP) applications can benefit from parallel execution for batch processing.

Parallel Execution & System Resources:

Parallel execution requires a multiprocessor system with spare CPU and memory resources. Parallel Execution will degrade performance on a single-processor system. Similarly on an over-utilized system, parallel execution can cause bottlenecks, and degrade performance even further.

Regular Analysis of Tables and Indexes:

The statistics of tables and indexes should be analyzed regularly and kept up to date.

Best Possible Execution Plan:

Oracle's cost-based optimizer chooses the best possible execution plan if t ables and indexes are analyzed after performing large data loads, bulk inserts, and index creations.

Disabling logging for a non-critical operation:

All database operations are normally logged in the database redo log files which involves extra I/O and thus reduces performance. If logging is disables for non-critical operations then it can give significant performance boost to the system.

Avoiding generation of REDO for the LOAD:

In order to avoid generation of redo for the load the UNRECOVERABLE option for SQL*Loader sessions can be specified. This saves a lot of time and redo log space. However you may need to manually re-perform the operations that you did not log.

Bulk Inserts:

The NOLOGGING option can be used while creating tables in parallel using CREATE TABLE ... AS SELECT statements, or while using INSERT INTO ... SELECT statements to do bulk inserts into a table.

Drop indexes while loading data:

Maintaining indexes while loading data is resource-intensive and has a detrimental affect on the performance of the load. You can minimize the performance impact of this index maintenance by dropping all non-unique indexes before performing any large data load. After the load is complete, you can use Oracle's parallel index creation feature to recreate those indexes efficiently.

Set proper value for Degree of Parallelism:

The default values set by Oracle for the degree of parallelism at the instance level may not be appropriate for all queries. Therefore, you should override the default values by specifying the degree of parallelism at the table or index level, or at the statement level.

Disk Striping:

Always stripe the tablespaces used for tables, indexes, and temporary segments over multiple devices.

Partitioning:

Put partitions of a table on separate tablespaces in order to improve I/O performance. Some operations, such as UPDATE and DELETE statements, can be parallelized only on partitioned tables.

Dynamic Performance Views:

Oracle's dynamic performance views (V$ views) should be used to monitor the parallel execution performance of an Oracle database. These views can be queried and the data derived from them can be used to tune the initialization parameters like PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS, and PARALLEL_SERVER_IDLE_TIME.

EXPLAIN PLAN command:

Use the EXPLAIN PLAN command to view the execution plan for parallel SQL statements that you write. The PARALLEL_FROM_SERIAL operations indicate that one serial operation is going to feed the multiple processes of a parallel operation. The degree of parallelism should be set properly in order to avoid these types of operations.

Automatic Tuning Features:

Oracle8i supports automatic tuning of parallel execution by setting the PARALLEL_AUTOMATIC_TUNING initialization parameter. If this parameter is set to TRUE then tremendous performance gains can be achieved as the Oracle instance will automatically control the values of several other initialization parameters and your instance will automatically alter the degree of parallelism for SQL statements.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Management Goals: Scalable Oracle Web Systems
   Step by Step Guide to Oracle Parsing
   DBA Tips for Triggers in Oracle 11g!
   Oracle SQL Resource Usage, Some Concerns