Performance Tuning in Oracle #Diksha Oracle Training Centre

Performance Tuning in Oracle.

Hello Everyone,

Before Reading this blog please go through my previous blog for better understanding of this topic. Today I will discuss about performance tuning by using SQL for Data Analysis.

Performance Tuning play important role in any application where data is heavy and on daily basis data is extracting from user. As everyone needs data / information on time and if get stuck somewhere it means we need to tune the query to improve the performance of the application. So in this blog, we will learn to identify when SQL queries needs to be improved, and how to improve them.

 Let’s understand Performance tuning in Simple way.

Performance tuning is the process of optimizing Database performance by streamlining the execution of SQL statements. In other words, performance tuning simplifies the process of accessing and altering information contained by the database with the intention of improving query response times and application operations. Before Starting SQL query Tuning let understand how the SQL query execution is done.

Any SQL query execute with below steps.

1.From Clause

2.Where Clause

3.Group by Clause

4.Having Clause

5.Aggregate functions and select statement.

 

We need to consider the execution of specific sql statement. Then user needs to know about what exactly the full table scan means and what is index scan in SQL. Let’s understand in simple word about Full table Scan and Index Scan in database.

The full table scan reads all the records from the table and filter out those which records does not meet the criteria of selection. Each row from the column is examined and determined which is used in where clause. Whereas Index scan is When optimizer uses the indexes to fetch the data then the scanning technique is known as Index scan. It retrieves the data specified by index. Please go through my Index Blog for more clarification.

Performance Tuning Tips for Query:

1. The first step to tune any query in SQL is to check its explain plan. The SQL Query Tuning is nothing but the reducing cost of the query and improving the query speed. Explain plan gives the plan of query execution step by step. To Check the explain plan of the SQL query below syntax is used.

Explain (SQL QUERY)

2. The second step to tune the SQL is Query needs to be written by considering some factors. Below are some simple tips to write the SQL query in perfect manner.

a) Use column names in the Query instead of using * operator to select all records from the table.

b) Avoid uses of distinct clause in query as it increases the sorts.

c) Avoid Use of not equal to in where clause instead of that use greater than or less than operator

d) Avoid Having clause or group by clause if possible.

e) Avoid use of Union statement in the query instead of that try to use union all statement.

 

Above are some important points to check before starting the tuning of SQL Statement.

 3: Use of Proper Indexing

Indexing is best way to improve the query performance. But it also decreases the performance if it is not used in proper way .Best indexes will help user to find out the specified data with less disk I/O operations. To create a proper indexing on different columns user should have idea about the different indexing techniques in SQL as well as its types.

Example:

Suppose user wants to create index for following query. Let us consider that Employee table have 1 k data and only 2 Employees belongs to IT department

Select Employee_name,Employee_salary

from Employee

 where depoartment_name= ‘IT’;

Below is the output of above query where you can see index not used and also in output you can see un indexed column shown in red.

 

After creating index in column below are the output of the query.

 

Note : If the statement table does not have any index then it will be execute slow. Here the department_name  column needs indexing. In this case simple index will not work. User needs to go for Bit-map index to improve the performance of the query.So dont go for direct indexing. Avoid indexing on small tables as the processing of direct table scan faster than index scan in that case.

 

Step 4: Create Composite indexes

Creating composite indexes is best part of doing the performance tuning of specific query. There are so many queries where the conditions like and and or are used in where clause.In that case in-spite of using different indexes on different columns create a composite index which will definitely reduce cost of the query and improves the performance of the query.

Suppose user wants to optimize the below query with proper indexing.

Select Emp_name,Phone from Employee

where First_name=’Dheeraj’

And Last_name=’Jha’;

The above query has where condition on first_name and last_name column. In that case user needs to create the composite index. The composite index will retrieve the data fast .

5: Response Time and Total Execution time

Response time is the time which takes for a query to return the first record. Total Execution time is the time it takes for the query to return all records.

Understanding of response time and total execution time is important.

 6: Use SQL Tuner Advisory or SQL Optimizer

The proper use of SQL Tuner advisory or SQL Optimizer will give user different suggestions about the SQL Query tuning. If the indexing is not proper then SQL tuner advisory will suggest user to remove those indexes to improve the performance of the query. The SQL Tuner advisory will give you results against below information.

 

a) Statistical Analysis:

 SQL tuner advisory will provide you report of statistical analysis of the data.

b) SQL Profiling:

 SQL Tuner advisory will give you suggestions to do some kind of SQL profiling.

c) Index Analysis:

SQL Tuner advisory will give you different suggestions to remove and add new indexes according to the execution of query.SQL tuner advisory results user needs to do the indexing and profiling accordingly to improve the query performance.

 7: Create indexes on group by and order by columns

The group by column always sorts the data and then it groups the data according to the column data. Creating indexes on group by column will improve the SQL Query performance, so that for SQL Query tuning user needs to create the indexes on group by columns.

Example:

Select * from Employee

 group by department;

In above example programmer needs to create index on department column to improve the performance of the query.

Select * from Employee

order by salary desc;

In above example user needs to create index on salary column to improve performance of Query.

8: Distinct column Indexing

In some situations where programmer needs to use the distinct column. Make sure that the distinct column is properly indexed. The proper use of indexing the distinct column is important. If only one column is using distinct then the indexing will not work. If more than two columns are distinct  then use the composite index to tune the query.

Example:

Select distinct first_name

,last_name

from Employee;

Here user wants first_name and last_name distinct Employees from Employees table.In that case user needs to create composite index on first_name and last_name to improve the performance of the query.

9: Avoid using sub-queries

There are some situations where user needs to write sub-queries to achieve specific functionality. It is not recommended to use sub-queries to tune the query. Instead of sub query use joins

10: Use Table Partitioning

Table partitioning correctly according to the requirement is the key part of performance tuning. If the table is very huge and user needs to divide that table. User needs to use table partitioning in that case.

  

Please go through this blog carefully and try to write queries in that way which will give you output fast. Practice the query as suggested in this blog. Performance Tuning plays very important role in IT industry as applications needs to give data fast and using above techniques we can optimize the SQL queries. Most of the interview questions is being asked from performance tuning part for Fresher’s as well as for experienced candidate. As Performance Tuning is also important in Production / Live environment. Please let me know if you have doubt on this blog , I suggest you to practice with some queries and create index to see the performance of dummy data of at least 2000 records.

Thanks