SQL with literals instead of bind variables

When the application writes the values into the SQL text, each different value becomes a new statement for the database, with its own hard parse and cursor. The report groups these statements into a shape, and the Diagnosis points to the shapes whose PARSE weighs.

Symptoms

In the Diagnosis, the finding Statement #n: n variants of the same SQL, differing only in literals (LITERAIS_SEM_BIND) appears, for example Statement #2: 4,312 variants of the same SQL, differing only in literals. When the variant count is estimated, the number comes after about, as in Statement #2: about 4,312 variants of the same SQL, differing only in literals (trace with many statements).

The text explains that the application writes the values into the SQL instead of using bind variables and shows the cost: how many PARSE calls the variants made, how many of them were hard parses, how long they took and what share of the total time that is; then, how many calls the variants made in all and how much they add up to with the recursive SQL. It ends with the fix, using bind variables in the application, with CURSOR_SHARING=FORCE in the database only as a stopgap. The link is View statement #n, which opens the detail of the shape.

The percentage next to the title is the time of these PARSE calls over the total time: it is the part that bind variables save, because the execution stays the same with binds. That is why the finding follows the PARSE of the variants, not the total time of the shape.

The finding appears for shapes with many variants whose PARSE weighs, on the total time or on the hard parses of the session, starting with those that spent the most on PARSE. The severity is low and becomes medium when that PARSE weighs more on the session. It does not appear where a bind variable does not help: a statement that does not accept binds, such as DDL and ALTER SESSION; Oracle's own dynamic sampling, which copies the literals of the parent statement; and a number that only appears in a comment or a hint. When the PARSE of the variants is almost all light, the case is a cursor reopened on every use: see one PARSE per execution.

Cause

With a bind variable, there is a single SQL text, and the cursor serves every value. With the value in the text, each value produces a new text in the shared pool, a hard parse and a cursor that is almost never reused. The cost shows up as parse CPU, as contention in the shared pool and, often, as the many hard parses finding.

The report recognizes these statements by their shape: the same text with :s in place of each quoted literal and :n in place of each number (shareable texts). In the statement lists, the shape is a single row, with the N variants badge and the totals added up, and its detail shows the variants that cost the most. These three statements, for example:

select * from exemplo where id = 101
select * from exemplo where id = 102
select * from exemplo where id = 103

are variants of the shape:

select * from exemplo where id = :n

Resolution

1. Open View statement #n. In the detail of the shape, the Variants that cost the most table gives the SQL ID, the calls, the time and the literals of each variant: with the text of the shape and these values, find the code in the application that builds the SQL (variants that cost the most).
2. Replace the value in the text with a bind variable. In JDBC, use PreparedStatement with ?:

   PreparedStatement ps = conn.prepareStatement("select * from exemplo where id = ?");
   ps.setLong(1, id);

   In PL/SQL, static SQL already uses binds; in dynamic SQL, pass the value with using:

   -- value in the text: each id becomes a new SQL
   execute immediate 'select nome from exemplo where id = ' || v_id into v_nome;
   
   -- bind: the same SQL for every id
   execute immediate 'select nome from exemplo where id = :id' into v_nome using v_id;

3. While the application does not change, CURSOR_SHARING=FORCE makes the database replace the literals with binds. 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;

4. A literal on purpose can be the right choice in a rare query over very skewed data, where each value deserves its own plan: with few executions, the hard parse of each one costs little.

How to confirm

Generate a new trace of the same operation after the change. The shape should disappear or keep only a few variants, and in Call types, on the PARSE row, Misses should drop. The finding disappears when the PARSE of the variants no longer weighs; the many hard parses finding, if there was one, usually disappears with it.

In the report

The sample report has no statements with repeated literals, so this finding and the Shareable texts section do not appear in it. The case 1 report, from a real trace, has both: see the finding in the Diagnosis of case 1 and the shape in Shareable texts of case 1. Where to look:

• Diagnosis: the finding and the View statement #n link.
• Statement detail: the shape, with the text in :s and :n, and the Variants that cost the most table.
• SQL statements by contribution: the shape is a single row, with the N variants badge and the variants added up.
• Shareable texts: the shapes with two or more variants; the section only appears when the trace has them.
• Call types: PARSE row, Count and Misses.