Thursday, July 18, 2019

When HARD PARSE and SOFT PARSE and How we can avoid it?


When any new SQL arrives, 
  • It tries to find a suitable child cursor on the library cache, then SOFT PARSE occurs. 
  • If there is no parent cursor found, then HARD PARSE occurs. 
  • If there is a parent cursor found, but It's existing children can't be reused by this call.
  • As it depend on then size of bind variables and optimizer settings and NLS settings, as well. At this time, there will be a HARD PARSE.
  • If there is a parent cursor found, but if any existing child cursors executed with similar execution plan, then it can be reused and, there will be a SOFT PARSE.
Ideally, the Parent cursor contains the SQL statement text only and Child cursor contains Execution plans.

By using bind variables we can avoid unnecessary HARD PARSE to the DB.
If bind variables are not used, then there is HARD PARSE of all SQL statements. 
This has a major server impact on performance and we will face with the high waits time during SQL execution and higher cost of SQL.