How to enable a parallel execution:
Small tables/indexes (up to thousands of records; up to 10s of data blocks) should never be enabled for parallel execution. Operations that only hit small tables will not benefit much from executing in parallel, whereas they would use parallel servers that you want to be available for operations accessing large tables.
Best practices when using object sizes as the main driving factor for parallelism are commonly aligning the DOP with some kind of step function for parallelism, e.g.
Needless to say that your personal optimal settings may vary - either in size range or DOP - and highly depend on your target workload and business requirements only.
You can enable parallel execution and determine the DOP in the following priority order:
The DOP is limited by the Oracle Database Resource Manager (DBRM) settings. For example, if your resource plan has a policy of using a maximum DOP of 4 and you request a DOP of 16 via a hint, your SQL will run with a DOP of 4.
Oracle will make use of Parallel Loading when “Parallel” hints is used in a query block SQL Statement. The requested DOP for this query is DEFAULT.
select /*+ parallel(c) parallel(s) */
c.state_province
, sum(s.amount) revenue
from customers c
, sales s
where s.customer_id = c.id
and s.purchase_date
between to_date('01-JAN-2007','DD-MON-YYYY')
and to_date('31-DEC-2007','DD-MON-YYYY')
and c.country = 'United States'
group by c.state_province
/
This method is mainly useful for testing purposes, or if you have a particular statement or few statements that you want to execute in parallel, but most statements run in serial.
The requested DOP for this query is 16 for the s table (sales)
select /*+ parallel(s,16) */ count(*)
from sales s ;
Reference: Oracle® Database SQL Reference - 10g Release 2 (10.2) - Alter Session
The PARALLEL parameter determines whether all subsequent query statements in the session will be considered for parallel execution.
Force: If no parallel clause or hint is specified, then a DEFAULT degree of parallelism is used.
alter session force parallel query;
This force method is useful if your application always runs in serial except for this particular session that you want to execute in parallel. A batch operation in an OLTP application may fall into this category.
ALTER session enable parallel query;
alter session disable parallel query;
SELECT DISTINCT px.req_degree "Req. DOP",
px.degree "Actual DOP"
FROM v$px_session px
WHERE px.req_degree IS NOT NULL
Req. DOP Actual DOP
---------------------- ----------------------
16 16
ALTER TABLE <table_name> PARALLEL 32;
ALTER TABLE <table_name> PARALLEL ( DEGREE 32 );
ALTER TABLE <table_name> PARALLEL ( DEGREE DEFAULT );
Use this method if you generally want to execute operations accessing these tables in parallel.
Tables and/or indexes in the select statement accessed have the parallel degree setting at the object level. If objects have a DEFAULT setting then the database determines the DOP value that belongs to DEFAULT.
For a query that processes objects with different DOP settings, the object with the highest parallel degree setting accessed in the query determines the requested DOP.
ALTER TABLE table_name NOPARALLEL;
SELECT table_name, degree FROM user_tables WHERE table_name='MyTableName';
To enable parallelization of Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE , execute the following statement.
alter session enable parallel dml;
To see the rule on parallel DML, see this article Oracle Database - Parallel DML