In my previous articles I have already given the details about the SQL performance tuning with examples. In this article I would like to give the SQL performance technique to use the hints in SQL. In this article I would like to give you the details and example of the most common link which is used to improve the performance of SQL – parallel hint with examples. The parallel hint is used to create the multiple parallel sessions so that the load will be divided properly within sever at the time of execution. If load is dividing parallelly in the sessions then it will definitely improve the performance of the SQL execution.
What you will find in this article?
- Parallel Hint with examples
- Parallel_enable Hint with examples
- How parallel hint used to improve the SQL performance?
What is Parallel Hint with examples?
- The parallel hint is one of the good optimizer hint which allows you to improve the performance of SQL select statement.
- The parallel hint is useful only when the table is being full scanned and not partially scanned.
- There are following 4 types of degree of parallelism which you may use in Oracle SQL but with this hint we are using SQL statement level parallelism : 1. System Level Parallelism : This technique is mainly used in DWH. Make sure that you are not using this parallelism in Online Transactional Processing ( OLTP). It may slow down the transactions. 2.Session Level : If you have requirement where you need to run queries fast at session level then you can apply this parallelism concept. Example : Alter session force parallel query; 3.Object Level parallelism : You can apply parallelism at object level. Example : If you want to run employee table data fast you can use following query. Alter table Employee parallel degree 15; 4.Use of Parallel Hint : You can directly use the parallel hint to run query in parallel sessions. Example : Select /*+ parallel (emp,16) */ from Employee emp;
- The parallel hint will open multiple parallel processes to execute the query.
Syntax :
SELECT /*+ parallel(table_name,degree_of_parallelism) */ column_1, column_2,column..n FROM Table_name;
Example :
SELECT /*+ parallel(employees,8) */ employee_id, Employee_name FROM hr.employees;
The above statement will run the query with 8 degree of parallelism. Here 8 different processes with open to run the select query fast. The each one of the process will take the subset of the data.
If the above employees table has 80000 records this means that each process will process 10000 records parallelly rather than serial execution of 80000 records together.
5. If you want to process the indexes in parallel fashion then you can use the parallel_index hint which has two arguments one is table name and other one is the index name.
Example :
SELECT /*+ parallel_index(emp, employee_id ,8) */ employee_id, name
FROM employees WHERE deptno = 101;
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.
6. If you dont want to use parallel execution if system level or oracle level parallelism is set then you can use NO_PARALLEL or NO_PARALLEL_INDEX hint.
Parallel_enable Hint with examples :
We can use the parallel_enable hint to improve the performance of the user defined functions in oracle SQL. The parallel_enable hint will be used in functions as an optimization hints to run queries in parallelized fashion.
Create or Replace Function F_Square(Num Number)
RETURN Number parallel_enable is
begin
return Num * Num;
End F_Square;
The above function is used to calculate the square of the number uses parallel_enable statement. You can use this function in any query containing the parallel statement.
Select /*+parallel(4)*/ Employee_name,F_Square(sal)/20 as Current_salary from Employees;
The above query as well as function will run parallelly and it will create 4 processes.
Just Remember : The Parallel hints can only be used in Oracle Enterprise Editions.
I hope you like this article on parallel hints. If you like this article or if you have any issues with the same kindly comment in comments section.