In a serial – non-parallel – execution environment, a single process or thread undertakes the operations required to process your SQL statement and each action must complete before the succeeding action can commence.Parallel SQL allows a SQL statement to be processed by multiple threads or processes simultaneously.Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:• Queries that involve table or index range scans.• Bulk insert, update or delete operations.• Table and index creation.In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table. There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU. If there were insufficient parallel servers to satisfy the requested DOP, one of three things could occur:• The SQL would be run at a reduced DOP (be downgraded)• The SQL would run in serial mode (be serialized)• If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate with “ORA-12827: insufficient parallel query slaves available”.Oracle 11g release 2 introduced Automatic computation of DOP to parallelize
SQL. Automatic DOP whichis – as the name says – a way of having Oracle determine the degree of parallelism depending on the nature of the operations to be performed and the sizes of the objects involved. The decision is made based on various initialization parameter settings.1. PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO

– The optimizer computes the optimal degree-of-parallelism value based on estimated execution statistics.

. AUTO

– will consider to assign a system computed DOP to all tables

– Enables parallel statement queuing :If the requested or required DOP is not possible because parallel servers are busy, then Oracle will defer statement execution rather than downgrading or serializing the SQL until enough parallel slaves become available.

– Enables in-memory parallel execution.

2. PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query’s estimated execution time is likely to run longer than the acceptable value (in seconds) for PARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at the cost of other non-parallelizable operations.The default of this parameter is 10 seconds.

3. PARALLEL_ADAPTIVE_MULTI_USER : If it is set to TRUE, then Oracle will adjust the degree of parallel based on the overall load on the system. When the system is more heavily loaded, then the degree of parallelism will be reduced.

4. If PARALLEL_IO_CAP is set to TRUE in 11g or higher, then Oracle will limit the Degree of Parallelism to that which the IO subsystem can support. The IO subsystem limits can be calculated by using the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

5. PARALLEL_MAX_SERVERS : To avoid an arbitrary number of parallel processes to be running on a system, which may overload that system, the parameter parallel_max_servers provides a hard upper boundary. Regardless of any other setting, the degree of parallelism cannot exceed that which can be supported by PARALLEL_MAX_SERVERS. For most SQL statements, the number of servers required will be twice the Degree of Parallelism.

6. PARALLEL_DEGREE_LIMIT : It is the maximum DOP that can be used. It can take various values :

– CPU (Default) : the total CPU count of the system

– IO : the limit depends on the I/O load on the database host

– any number greater than 0.

Note that even if you increase the parameter value, the automatically computed degree of parallelism may not increase, because the database may determine that the computed degree of parallelism is sufficient.The DOP that we run the statement with is the minimum value of the computed DOP (or ideal DOP) and that parallel_degree_limit parameter.

• A degree of parallelism can be specified at the table or index level by using the PARALLEL clause of CREATE TABLE, CREATE INDEX, ALTER TABLE or ALTER INDEX.

• The PARALLEL hint can be used to specify the degree of parallelism for a specific table within a query.

In my next article in this series , I will practically demonstrate oracle behaviour for various values of the parameter PARALLEL_DEGREE_POLICY i.e. MANUAL, LIMITED AND AUTO