Parallel Information

• The degree of parallelism for a query can be specified
• By specifying the parallel clause during table/index creation (Or later using alter)
• Using a parallel hint in the sql statement
• Using ‘alter session force parallel query parallel integer’ statement
• If One of the options in the above statement has not been specified (To enable parallelism) then, irrespective of whether the table or index is partitioned or not, oracle does not use parallelism in the query
• When using The dedicated server connection model (As opposed to the shared server model), the sessions shadow process acts as the query co-ordinator.
• The query uses as many “parallel execution servers” as determined by the query optimizer, to execute the query (This is based on a varitey of factors)
• When there is No parallel degree specified at the table level, in the query hint or at the session level, but parallelism is enabled at the session level (using alter session  force parallel query) then the optimizer uses the default degree of parallelism.
• The Default Degree of Parellism (DOP) is determined by cpu_count x parallel_threads_per_cpu
• If you specified parallelism by issueing ‘alter session force parallel query parallel integer’ statement, then the value used for integer is used for the degree of parallelism (If a parallel hint is not specified in the query).
• All of the above statements assume that there are enough query servers available in the pool and parallel_adaptive_multi_user does not reduce the number of parallel execution servers.
• If interoperation parallelism can be used then you could end up using double the number of parallel execution servers as the degree of parallelism
• If for some reason your query cannot be allocated enough parallel execution servers (typically when there are multiple sessions and all the parallel execution servers (gated by parallel_max_servers) are currently in use) and parallel_min_percent > 0 (0 is the default so your query will still get run without parallelism), your query will get an error and you can try again later.

 For a good primer on parallel query in oracle please read the white paper, Oracle Sql Parallel Execution