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

1 nolu sorgu: select * from hr.personel;
2 nolu sorgu: select * from hr.Personel;

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