SQL with high Parse Calls

SQL with high Parse Calls

‘Parse calls’ in SQL statistics refers to the number of ‘tries’ oracle makes to parse a SQL statement before it executing it. 

The sole purpose of a parse call is to load the SQL statement into shared pool (oracle’s memory), optimize it and generate a SQL plan for execution.

A Parse call is different from parsing. It could be parsed or not parsed under one of the following situations.

a.       Oracle searches in the memory (shared pool) for the sql statement and cannot find the same in there. Then oracle has to optimize and generate a SQL plan. This is called HARD PARSE.

b.      Oracle searches in the shared pool and finds the sql statement.  However, oracle has to retest it to see whether it can be reused. If it cannot be reused, oracle will define a child cursor, optimize and generate a SQL plan.  This is known as CURSOR AUTHENTICATION.

c.       Oracle searches in the shared pool and finds the same sql statement. Then, oracle will simply use the same SQL plan and will not do any optimization. This is called SOFT PARSE.

d.      Oracle uses the session cursor cache or Pl/SQL cursor cache as shortcut to find the SQL statements location in the shared pool and use it and it will eliminate the search in the  shared pool.

To understand better about parse calls, SQL stats provide 3 other variables. 

a.       The ‘loads’ and ‘invalidations’ tells whether a cursor is invalidated and getting reloaded, which helps to verify a parse call becoming hard parse. 

b.      ‘Version Count’ tells how many child cursors are there in shared pool for a SQL statement, indicating whether a parse call lead to CURSOR AUTHENTICATION (means has it got optimized and generated a SQL plan).

If all the above 3 variables are not changed, we are able to assume a parse call turns out to be soft parse or no parsing and have least performance impact.

When we have excessive paring, it can cause contention on oracle’s memory/shared pool and CPU spikes on the servers, which is not only going to be performance issue related to db but also application scalability issue.  We have to investigate the SQL statements with high parse calls in order to make sure they don’t cause excessive parsing.

Tuning Strategies

Reducing parse calls from application code holds the key to minimize the parsing because the parse calls are the one which initiates the parsing.  Few common techniques for reducing parse calls are using bind variables, cursor caching for the purpose of ‘parse once and execute many times’.

Adjust database parameters “CURSOR_SHARING”  and “SESSION_CACHED_CURSORS” to facilitate parsing on database side.