Tag Archives: Parallel hint

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:


to force parallel behaviour, regardless of the parallel degree you have placed on an object:


For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:


2. Run the parallel operation by specifying HInts in the statements:




If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints


Enabling Parallelism for a SQL Query

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 hints
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.

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):





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;

Hints used in Oracle

Hints used in Oracle

Some common hints used by developer in daily life to make the system performance better.

Hints used in SQL language for generating better execution plan to save resource cost.

Hints is solution provided by oracle to force optimizer to choose different path for execution plan as you want to make execution plan for effective.
Hints used in sql statement for alter the execution plan during execution of particular query.
Note: Do not specify schema name in hint

FULL Hint: It is used for full table scan purpose.

SELECT /*+ FULL(e) */ emp_name
FROM hr.emp e
WHERE last_name LIKE :b1;

Perform full table scan on employee table even last_name have index on it.
Index Hint: Optimizer used indexes which mentioned in hints for generating execution plan.

Case 1: If you used the index name in statement as hint, then optimizer used index scan on this index. Optimizer doesn’t use full or another index scan on query.

SELECT /*+ INDEX (e emp_emp_idx)*/
emp_id, dept_id
FROM hr.emp e
WHERE emp_id > 50;

Case 2: If you used the more indexes in hint, then optimizer checked index scan and find better execution plan to run. Optimizer does not use full table scan or a scan on another index not mentioned.

SELECT /*+ INDEX (e dept_id_idx),INDEX (e emp_id_idx) */
emp_id, dept_id
FROM emp e
WHERE dept_id > 50 and emp_id = 10;

Case 3: If you used no indexes, then the optimizer scan each index present on table to find better execution plan but no full scan.

SELECT /*+ INDEX (emp)*/ emp_id, dept_id
FROM emp e
WHERE dept_id > 50 and emp_id = 10;

No Index Hints: Optimizer does not use indexes which is mention as hints.
Case 1. If you used index name, then that index is not use for sql statement

SELECT /*+ NO_INDEX(e empid_idx) */ emp_id
FROM emp e
WHERE emp_id > 200;

Case 2:If you used multiple index then except mentioned index all other index is considered
Case 3: If you specify no_index then optimizer not considered any index
INDEX_FFS hint: Optimizer perform fast full index scan instead of full table scan.

SELECT /*+ INDEX_FFS(e empname_idx) */ first_name FROM emp e;

NO_INDEX_FFS hint: Optimizer did not do full index scan for that index which is mentioned in hint

SELECT /*+ NO_INDEX_FFS(e empname_idx) */ emp_name FROM emp e;

INDEX_SS hint: Optimizer choose index skip scan for table

SELECT /*+ INDEX_SS(e empname_idx) */ emp_name
FROM emp e
WHERE emp_name = ‘RAM’;

NO_INDEX_SS hint: causes the optimizer to exclude a skip scan of the specified indexes on the specified table.

SELECT /*+ NO_INDEX_SS(e emp_name_ix) */ ep_name
FROM emp e
WHERE emp_name = ‘RAM’;

Parallel hint: use to increased the speed of query by forcing optimizer to used parallel process

SELECT /*+ FULL(e) PARALLEL(e, 5) */ emp_name FROM emp e;

NO_PARALLEL hint: overrides a PARALLEL default value set for table. It is deprecated.
Example: Use of hint no_index_ss for increase the performance.

I have query which changes execution plan with different literals value. Sometime it’s run normal and in some cases it’s hanged.To overcome this problem, I used sql query with hints because one execution plan use skip scan and degrade the performance of query.

Note: Query 1 pick wrong index which degrade the query performance but Query 2 with different literals value use correct index.
Same query used two different execution plan with different laterals.
To avoid the skip level scanning in query 1 used no_index_ss scan hint to generate good plan as shown in query 3.
Query 1: Used the wrong index in execution plan (Bad Query)
SELECT * FROM scott.Sales WHERE entity_no = ‘000151’ AND order_date = ’28-Dec-2016′ AND cust_number = ‘0000002000100084’;

Execution Plan
Plan hash value: 2316688008
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                    |                  |     1 |   609 |   338  (86)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES            |     1 |   609 |   338  (86)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | SALE_CUST_NO_IDX |    33 |       |   311  (93)| 00:00:01 |
Predicate Information (identified by operation id):
   1 - filter("ORDER_DATE"=TO_DATE(' 2016-12-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access("ENTITY_NO"='000151' AND "CUST_NUMBER"='0000002000100084')

Query 2: Used the better index in execution plan (Good Query)
SELECT * FROM SCOTT.SALES WHERE entity_no = ‘000151’ AND order_date=’12-Dec-2016′ AND cust_number = ‘0000000151009034’;

Execution Plan
Plan hash value: 2381214534
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                    |                |     1 |   609 |  1165   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES          |     1 |   609 |  1165   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | 1SALE_PRIM_IDX | 13707 |       |    60   (0)| 00:00:01 |
Predicate Information (identified by operation id):
   1 - filter("CUST_NUMBER"='0000000151009034')
   2 - access("ENTITY_NO"='000151' AND "order_date"=TO_DATE(' 2016-12-12 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

Query 3: Used Hint to make it better (modified query)
SELECT /*+ NO_INDEX_SS(tran) */ * FROM scott.Sales WHERE entity_no = ‘000151’ AND
order_date = ’28-Dec-2016′ AND cust_number = ‘0000002000100084’;

Execution Plan
Plan hash value: 2381214534
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                    |                |     1 |   609 |  1164   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES          |     1 |   609 |  1164   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | 1SALE_PRIM_IDX | 13689 |       |    60   (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("CUST_NUMBER"='0000002000100084';)
2 - access("ENTITY_NO"='000151' AND "ORDER_DATE"=TO_DATE(' 2016-12-28 00:00:00',
           'syyyy-mm-dd hh24:mi:ss'))