Enabling Parallelism for a SQL Query
You found a query running on large table, you want to enable parallelism to check its speed after normal execution.
For using the parallelism on SQL Query, you need to explicitly defines hints on the SQL Query for forcefully used the parallel operations.
Two way to be used parallel operation on SQL Query as
1. Parallel Hint
2. Parallel_index Hint
Parallel hint works on tables as follows:
In following queries, you are specify the parallel 4 means 4 process is used to fetch data from the employees table.
SELECT /*+ parallel(employees,4) */ emp_id, name FROM hr.employees;
In following queries, you specify nothing, Oracle detect the parallel operation based on database initialization parameters:
SELECT/*+ parallel(emp) */ emp_id, name FROM hr.employees emp;
Parallel_index hint used for parallel access to indexes.
SELECT /*+ parallel_index(emp, emp_i4 ,4) */ emp_id, name
FROM employees WHERE deptno = 10;
Two arguments used in parallel_index hint: Table name and Index name. If you do not specify parallel number then oracle choose automatic on db settings.
NO_PARALLEL & NO_PARALLEL_INDEX Hints
Both used if you want to test the query without using parallel execution. Sometime Query is executed better in case of non parallel then parallel and your parallel level is set on table degree or instance level (PARALLEL_DEGREE_POLICY=AUTO) and you want to over come this situation for specific query. Then use No_PARALLEL and NO_PARALLEL_INDEX.
Note: As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated. Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.
Check the parallelism of object or index with following query:
select degree from dba_tables where table_name = 'EMPLOYEES';
select index_name,degree from dba_indexes where table_name = 'EMPLOYEES';
Note: If automatic DOP is enabled (PARALLEL_DEGREE_POLICY=AUTO), then the parallelism that you set on objects is ignored.
Change the degree at object level ( table or index):
ALTER TABLE EMPLOYEES PARALLEL(DEGREE 4);
ALTER INDEX EMP_IDX PARALLEL(DEGREE 4);
ALTER TABLE EMPLOYEES PARALLEL(DEGREE 1);
ALTER TABLE EMPLOYEES NOPARALLEL;
Script for change parallel degree for all objects in schema
-- For indexes
select 'alter index '||owner||'."'||index_name||'" parallel (degree 4);' from dba_indexes where owner='IC';
-- For Tables
select 'alter table '||owner||'."'||table_name||'" parallel (degree 4);' from dba_tables where owner='IC';
Enable parallel at session level forcefully:
alter session force parallel query parallel N;
--example: Run SQL Query with parallel with 4 process
alter session force parallel query parallel 4;