SQL with High Parse Calls
 Concept

The 'parse calls' in in SQL stats refers to the number of 'tries' Oracle makes to parse a SQL statement before executing it. The purpose of a parse call is to load the SQL statement into Oracle’s memory (shared pool), optimize it and generate SQL plan for execution.

Parse call is different from parsing. It leads to one of the following situations and may or may not end up with parsing (metalink note 32895.1):

  •   Oracle searches the shared pool but cannot find the same SQL statement there. Oracle has to optimize and generate SQL plan. This is called as hard parse.

  •   Oracle searches the shared pool and finds the same SQL statement there. However, Oracle needs to test if it can be reused. If Oracle cannot reuse, Oracle will define a child cursor, optimize and generate SQL plan. This is called as cursor authentication.

  •   Oracle searches the shared pool and finds the same SQL statement there. Oracle simply reuse SQL plan and there is no optimization. This is called as soft parse.

  •   Oracle uses the session cursor cache or PL/SQL cursor cache as a shortcut to find a SQL statement’s location in the shared pool and use it, eliminating the need to search shared pool. There is no parsing involved.

SQL Stats provides three other variables to help us understand the impact of parse calls. The 'loads' and 'invalidation' tells whether a cursor is invalidated and gets reloaded, which helps us to verify whether a parse call becomes hard parse. The 'version count' tells how many child cursors a SQL statement has in the shared pool, indicating whether a parse call leads to cursor authentication. If there is no change in values of these three variables, we are able to assume a parse call turns out to be either soft parse of no parsing, which has the least performance impact.

Excessive parsing can cause contention on Oracle’s memory structure and CPU spikes on database servers, which is not only database performance issue but also application’s scalability issue. We need to investigate SQL statements with high parse calls in order to make sure they don’t cause excessive parsing. The key tuning strategies include:

  •   Reducing parse calls from application code holds the key to minimize parsing because it is the parse call which initiates parsing. Commonly-adopted techniques include using bind variables, cursor caching for the purpose of 'parse once and execute many times', etc.
  •   Adjust database parameters such as cursor_sharing and session_cached_cursors to facilitate parsing on database side.

 Case Study Summary

The case scenarios include:

 Case 1 : SQL statement with the pattern of 'parse once and execute many times'

A SQL statement with the pattern of 'parse once and execute many times' was issued from application using Spring JDBC templates. This is the best case scenario.

 Case 2 : SQL statement with high parses due to the pattern of 'one parse per execution'

A SQL statement with the pattern of 'one parse per execute' was issued from application using Hibernate. Although we confirmed parse calls are all soft parse and no database performance issue was incurred so far, the application scalability was compromised.

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us