Many hard parses

The HARD_PARSE finding appears when a good part of the session PARSE calls are hard parses, or when the PARSE of SQL with literals weighs on the time. The most common cause is SQL without bind variables.

Applies to: 10046 traces, with or without waits

A hard parse is the full parse of a SQL statement that the database did not find ready in the shared pool. Done in bulk, it burns CPU and contends for structures with the other sessions; the most common cause is SQL with the values in the text, without bind variables.

Symptoms

In the Diagnosis, the finding Many hard parses (HARD_PARSE) appears. The text says how many PARSE calls were hard parses, for example 1,800 of 2,000 PARSE calls were hard parses, and, when there are statements that differ only in literals, adds Found 12 groups of statements that differ only in literals: use bind variables.

The finding appears in two cases. In the first, the session makes many PARSE calls and a good part of them are hard parses (the Misses column of the PARSE row in Call types). In the second, the hard parses come from many SQL variants with different literals only, and their PARSE weighs on the total time, even if the rest of the session does soft parses. The percentage next to the title is the PARSE time over the total time: of all of them in the first case, only those of the variants in the second. The severity is always medium. The link is View shareable SQL when the report has that section, and View call types when it does not.

The group count in the text covers all the SQL shapes with literals in the trace, including those left out of the Shareable texts section, which lists only those that added up the most time. The shapes that weigh the most also get their own finding of literals instead of binds, with the link to the statement of the shape.

Cause

On PARSE, the database looks for the SQL in the shared pool. When it finds a cursor that fits, the parse is light (soft parse). When it does not, it does a hard parse: it checks the text, the objects and the privileges and optimizes the plan. The trace marks the hard parse with mis=1 on the PARSE line. Common causes, from the most to the least frequent:

SQL with literals instead of bind variables: each different value produces a new text, and each new text requires a hard parse;
invalidated cursors: statistics gathering, DDL or GRANT on the objects of the SQL take the cursor out of use, and the next execution parses again;
small or fragmented shared pool: cursors are aged out of memory and must be loaded again;
versions of the same cursor: differences in the session environment or in the type and size of the binds create new child cursors.

Resolution

Check whether there are statements that differ only in literals: follow the View shareable SQL link of the finding (Shareable texts). If there are, the fix is to use bind variables in the application (literals instead of binds).

To find in the database the same SQL with different literals, group the shared pool by the signature that ignores the literals:

select force_matching_signature, count(*) as textos, min(sql_id) as exemplo
  from v$sql
 where force_matching_signature <> 0
 group by force_matching_signature
having count(*) > 1
 order by textos desc
 fetch first 20 rows only;

While the application does not change, CURSOR_SHARING=FORCE makes the database replace the literals with binds and reduces hard parses. It is a stopgap and can change plans: test it first and prefer turning it on only in the application session.
```
alter session set cursor_sharing = force;
```

If there are no literals, look for cursor invalidations and reloads:

select sql_id, child_number, loads, invalidations, parse_calls, executions
  from v$sql
 order by invalidations desc
 fetch first 20 rows only;

With many child cursors of the same SQL, see the reason for each one in v$sql_shared_cursor.
Statistics gathering with no_invalidate => false immediately invalidates the cursors that use the object: in production, pick the time.

How to confirm

Generate a new trace of the same operation after the change. In Call types, on the PARSE row, Misses should drop relative to Count, and so should CPU: PARSE dbcalls in the profile. The finding disappears when hard parsing is no longer the rule and the PARSE of the variants with literals no longer weighs on the total time.

The first execution after a restart or a shared pool flush always hard parses: measure with the database under normal use.

In the report

In the sample report, the only PARSE is a hard parse, but a single PARSE does not weigh on the session: no finding. The links open the sections of the example:

Diagnosis: the finding and the link to Shareable texts or Call types.
Call types: PARSE row, Count and Misses.
Profile, CPU: PARSE dbcalls row: the CPU spent on parsing.
PARSE latency distribution: the duration of each PARSE call.
Shareable texts: the section only appears when the trace has statements that differ only in literals, so it is not in the example.

Waits in the shared pool, such as latch: shared pool and cursor: pin S wait on X, belong to the Concurrency class and have their own page: contention for internal structures.

Updated on 2026-10-29. Report an error on this page