Hard Parse vs. Soft Parse and Bind Variable in Oracle Database

I will explain Hard Parse vs. Soft Parse and Bind Variable in Oracle Database in this post.

 

 

Hard Parse vs. Soft Parse in Oracle

To understand what is the Hard Parse and Soft parse, you should know how SQL Statements process in Oracle . SQL statements processing steps are as follows.  This steps are very important to understand Hard Parse and Soft parse in Oracle.

 

 

 

 

SQL Statement Processing Steps in Oracle

SQL Statement Processing Steps are as follows.

  1. Create a cursor. ( It contains information for statement processing)
  2. Parse the statement. ( Representation of SQL created and moved into the shared SQL area if there is no identical SQL in the shared SQL area )
  3. Describe query results. ( This step provides information about the select list items )
  4. Define query output.  (it defines location, size, and data type information required to store fetched values in variables )
  5. Bind variables.
  6. Parallelize the statement.
  7. Execute the statement.
  8. Fetch rows of a query.
  9. Close the cursor.

 

 

If the query has not been run before,  SQL is parsed and created its execution plan and stored in the Shared SQL area in the library cache. This operation is called hard parse.

 

When any SQL is sent Oracle for the first time, Oracle will create a plan by following the steps below. Or, if the plan has been created, the existing plan will be used or new plan will be created even if the SQL is the same (different literal values, or not using bind variable ).

 

 

 

Bind Variable in Oracle Database

Library Cache: This is one of the most important areas in the shared pool. Because when an SQL clause comes into the Oracle database, Oracle first checks whether it has been executed or not by looking at the library cache. If the query has been execute before, Oracle uses the previous execution plan without parsing this SQL.This process is called soft parse. 
If the query has not been executed before, oracle will parse the executed SQL and save it to the Shared SQL Area in the library cache. This process is called hard parse.
One of the most important tasks of a DBA is to identify and fix unnecessary hard parse operations in a database.
One of the most important reasons for unnecessary hard parse operations is that;
There are upper-lower case differences in the same SQL statements:
1 nolu sorgu: select * from hr.personel;
2 nolu sorgu: select * from hr.Personel;

 

Another method to use as a solution is to use bind Variable for the same query types:
SQL> select name,surname,phone from hr.personel where id=1;

In the above query, personel information with an id value of 1 is requested. Imagine that this query is always requested with different id values. Oracle will repeatedly parse this query in the library cache even though the query is the same. So instead of making a soft parse and consuming less than Cpu source, it will make the hard parse and consumes unnecessary CPU resources. At this point, we assign the “bind variable” value for the id column to ensure that the query uses the same execution plan for each different incoming id value. The use of the above query with the bind variable is as follows.

 

SQL> variable person_id number
SQL> exec :person_id : =180251;
SQL> select name,surname,phone from hr.personel where id :=person_id;

 

If we use Bind variable, Oracle will use the same execution plan even if the id value in the queries changes.

 

 

 

If a new SQL is not exists in the Shared pool, it must be parsed from scratch.

The cost of creating the plan for the first time is very high for Oracle database.

 

If any SQL will run for the first time, Oracle has to do a hard parse. If this SQL has been run at least once before, that is, Oracle does a Soft parse for this execution.

 

 

 

 

For Oracle, One character of SQL is different or existing lower case-upper case character, then new plan is created for this time.

 

If you don’t use bind variables in the SQL queries then Oracle will know similar SQLs differently like following.

select * from customer where id=63;

select * from customer where id=34;

Above queries are almost same, just id variable is different. But Oracle optimizer will evaluate these SQLs like different SQL.

 

If you use bind variable instead of literal like following then Oracle will evaluate as same SQL and will use same execution plan and won’t be hard parse in Oracle.

 

variable SYS_B_0 number; 
exec :SYS_B_0:= 63 
select * from customer where id= :SYS_B_0;



If we use Bind variable, Oracle will use the same execution plan even if the id value in the queries changes.

 

If Hard Parse is very high, then most of your queries are not using Bind variables.

 

You can find the SQL Statements that are not using bind variables using the following post.

Oracle Find queries not using bind variables

 

 

 

You should read the following post to learn more details about Hard Parse and Soft parse.

Cursor Sharing and Bind Variable Usage Effect on Oracle Database Performance

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database

 1,068 views last month,  5 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply