Provides a summary of the top SQL ordered by parse calls

If the number of Parsed Calls equals Executions then cursor sharing may not be taking place. In addition, bind variables may not be used.

Such situations, especially in the case of hard parses two things happen:

1. Excessive shared pool cache misses resulting in high CPU overhead
2. Shared pool flooding with non-resuable SQL

---

Parse calls in SQL statistics is the number of tries Oracle makes to parse a SQL statement before it executing it. The purpose of a parse call is to load the SQL statement into shared pool, 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 (Metalink Note: 32895.1):

1. Hard Parse:If Oracle searches the shared pool but cannot find the same SQL statement there then it has to optimize and generate a SQL plan.


2. Cursor authentication: If Oracle searches in the shared pool and finds the SQL statement then 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.


3. Soft Parse:If Oracle searches in the shared pool and finds the same SQL statement there then it will simply use the same SQL plan and will not do any optimization.


4. 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, eliminating the search in the shared pool.

To understand better about parse calls, SQL statistics provides three other variables.

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


• 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 three 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.

Excessive parsing can cause contention in the shared pool and CPU spikes on the servers, which is not only going to be performance issue related to databased but also application scalability issue. We have to investigate the SQL statements with high parse calls in order to make sure they do not cause excessive parsing.

Key tuning strategies include:

1. 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, etc..


2. Adjust database parameters cursor sharing and session cached cursors to facilitate parsing on database side.