How to enable a parallel execution:
Don't enable parallelism for small objects
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.
- objects smaller than 200 MB will not use any parallelism
- objects between 200 MB and 5GB are using a DOP of 4
- objects beyond 5GB are getting a DOP of 32
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.
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 ;
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