SQL Optimizer for Oracle :

In Previous articles I just explained about the Basics of Performance Tuning.In this article I will explain Parser and Optimizer in SQL and how it works from inside.What exactly the optimizer is?Optimizer is nothing but the execution of query in optimum manner.Parser and Optimizer in SQL is most efficient way of processing the query.SQL parser ,SQL Optimizer and source code generator compiles the SQL statement.This article gives you idea about SQL Optimizer for Oracle. I will try to brief you about the concept and working of SQL Optimizer for Oracle which is useful for Performance Tuning of SQL Query.

IF you want to see the Stepwise execution of SQL Select Statement Click Here

1.What is SQL Parser?

When SQL Statement has been written and generated then first step is parsing of that SQL Statement.Parsing is nothing but checking of SQL Query.Parser Performs following functions:

1.Syntax Analysis:

The parser checks for SQL statement syntaxs.If the syntax is incorrect then parser gives the incorrect syntax error.

2.Semantic Analysis:

This checks for references of object and object attributes referenced are correct.

2. SQL Optimizer for Oracle :

There are two types of SQL Optimizer for Oracle :

1.Rule Based Optimizer

2.Cost Based Optimizer

CLICK HERE TO GET BASIC IDEA OF PERFORMANCE TUNING……

1.Rule Base Optimizer:

When we execute any SQL statement ,the optimizer uses the predefined rules which defines what indexes are present in the database and which indexes needs to be executed during the execution.Rule Based optimizer is used to specify which table is been full scanned and which tables are taking the indexes during the execution.In Earlier the only optimizer which is used by Oracle is Rule Based optimizer.This is most important  SQL Optimizer for Oracle which is used to optimize the query in earlier stage.

“Rule Based Optimizer  specifies the rules for how to execute the query.”

Real Life Scenario:

If Table contains the Employees records and 100 records in which 80 Employees  from department named ‘Oracle’.

When we run the query.and there is index on Department_name

Select * from Employee where Department_name=’Oracle’; 

If We execute the above query then the table is full scanned so that because of index and rule based optimizer the time taken to execute the query high if the query is executed with the indexes. The Biggest disadvantage of Rule Based Optimizer is it does not take data distribution in to account. For data distribution Cost Based Optimizer is used.

CLICK HERE TO GET 20 MOST IMPORTANT INTERVEW QUESTIONS FOR TECH MAHINDRA

2.Cost Based Optimizer:

Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself  decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on  the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.

“Cost based optimizer considers the statistical information of the table for query execution”

Real Life Scenario:

If Table contains the Employees records and 100 records in which 80 Employees  from department named ‘Oracle’.

When we run the query.and there is index on Department_name

Select * from Employee where Department_name=’Oracle’; 

When We use the rule based  Optimizer then it will take the unnecessary index and the performance will slow down if the index is been taken.Using cost based optimizer it will take the statistical information of the table named Employee.

Following is the Stepwise Execution using  the above SQL Statement  using optimizer:

Execute Statement: Select * from Employee where Department_name=’Oracle’; 

Index Used:On Department name: IDX_DEPT_NAME

CLICK HERE TO GET WHAT IS INDEXING AND HOW IT IS USED…..

1.Step 1:

The Parser checks both syntax and Semantics of the Query.

Select * from Employee where Department_name=’Oracle’; 

Syntax correct.Semantics correct.

2.Step 2:

Then Optimizer Decides which method to be used.If the oracle version is older then only Rule based Optimizer is used and index has been taken.If New Oracle version is there then Cost based optimizer decides on the table data that index is to be used or not.It determines the most efficient way of producing the result of the query.

3.Step 3:

The Row source generator receives the optimal plan  and outputs the execution plan.

4.Step 4:

Full Table Scan if Cost Based Optimizer is used:

What is Full table Scan?

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.

Full tables scans are faster when there is large volume of data because of its larger I/O calls than the index scan..

The Optimizer uses Full table Scan in Following cases:

1.When Specified Column is not Indexed

2.To Fetch very large ammount of data

3.High Parallelism

So if in our example the data of department name is ‘Oracle’ is 80% of the total records(assumption).Then the table is full scanned.If the data is less than and divided properly in the table then cost based optimizer fetches the index and scans the table using index.

Rule Based Optimization uses Index Scan:

What is Index scan?

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.First oracle optimizer searches for the indexes on the column and then it executes the indexes and based on that indexes the execution of the SQL statement is been performed.

So in our example if optimizer is Rule based optimizer then it fetches the index from the table and based on that index the execution is done.

Hope everyone get the basic idea of the Parser and Optimizer in SQL and how parsing and optimizing is done in SQL.In next article i will explain how to check and analyse the query execution plan and tune the query with Real Life Example in detail.If You like this article dont forget to comment in comment section.If you want PDF notes of this article Comment in comment section you will get it.

HOME