One PARSE per execution

One PARSE for each execution means the cursor is opened and closed on every use. Even when light, each parse costs a call, CPU and contention in the library cache; SQL prepared once and executed many times avoids all of that.

Symptoms

In the Diagnosis, the finding Statement #n: one PARSE per execution (n PARSE, n EXEC) (PARSE_POR_EXECUCAO) appears, for example Statement #1: one PARSE per execution (2,000 PARSE, 2,000 EXEC). The text says the cursor is opened and closed on every use and ends with View statement #1, which opens the statement detail.

The finding appears for heavily executed statements in which almost every execution (EXEC) comes with its own PARSE, starting with those that weigh the most. The severity is low and becomes medium when the PARSE of the statement weighs more on the total time; that weight is the percentage next to the title.

On a row with the N variants badge, which adds up SQL with different literals only, the PARSE with a hard parse is the one for each new value, and the finding that handles it is literals instead of binds. This finding only appears on that row when the variants repeat and their PARSE is almost all light: the cursor of each variant is opened and closed on every use.

Cause

The application prepares the SQL, executes it once and closes the cursor, and repeats the cycle on the next execution. Each cycle costs one more PARSE call and may cost one more network round trip. Even when the parse is light (soft parse, without mis=1), it burns CPU and contends for the library cache with the other sessions.

It is common in code that creates the statement inside the loop, in drivers without a statement cache and in data access layers that open and close the cursor on every call.

Resolution

1. In the application, prepare the SQL once and execute it many times: keep the prepared statement and reuse it, instead of creating and closing it on every execution.
2. Turn on the driver statement cache, which keeps the cursors open between uses. In Oracle JDBC, for example:

   OracleConnection oconn = conn.unwrap(OracleConnection.class);
   oconn.setImplicitCachingEnabled(true);
   oconn.setStatementCacheSize(50);

3. Check session_cached_cursors. In PL/SQL, it sets how many cursors PL/SQL keeps open, and the repeated PARSE disappears. In the client application, it makes each PARSE cheaper, but the call stays in the trace: what removes it are steps 1 and 2.

   select value from v$parameter where name = 'session_cached_cursors';

4. If the statement is dynamic SQL built with the values in the text, the problem is a different one: see literals instead of binds.

How to confirm

Generate a new trace of the same operation after the change and open the statement detail. In the Calls table, the PARSE Count should be well below the EXEC count, and the finding disappears.

In the profile, CPU: PARSE dbcalls should drop. With the statement cache in the application, the Occurrences of SQL*Net message from client may also drop.

In the report

In the sample report, statement #1 has 1 PARSE and 1 EXEC: one PARSE per execution, but in a single execution, without a finding. The case 1 report, from a real trace, has this finding (Diagnosis of case 1). The links below open the sections of the example:

• Diagnosis: the finding and the View statement #n link.
• Detail of statement #1: in the Calls table, the Count of PARSE and of EXEC side by side.
• Call types: PARSE and EXEC for the whole session, with Misses.
• Profile, CPU: PARSE dbcalls row: the CPU spent on parsing.

Waits such as library cache: mutex X and cursor: pin S, in the Concurrency class, also come with excessive PARSE: see contention for internal structures. The statement detail has its own page: statement detail.