When the application writes the values into the SQL text instead of using bind variables, each different value becomes a new statement for the database, with its own PARSE and, usually, its own hard parse. This section groups those statements by the shape of the text and shows how much they cost together.
What the section shows
The Shareable texts section appears when the trace has statements that differ only in literals. The text at the top sums it up: Statements that differ only in literals. With bind variables, a single cursor would serve them all and hard-parse PARSE calls would drop.
The shape is the statement text with each literal replaced by a marker: :s for quoted text and :n for numbers. Names in double quotes and binds, such as :1 and :b1, stay as they are; numbers inside comments and hints also become :n. The statements with the same shape are its variants. These two statements:
select nome from exemplo where id = 101
select nome from exemplo where id = 102are two variants of this shape:
select nome from exemplo where id = :nThe section lists the shapes with two or more variants that added up the most time, from most to least.
How to read it
| Column | What it says |
|---|---|
| Texts | How many variants the shape has: how many different statements the trace has with that shape. When the count is estimated, it appears as about N (trace with many statements). |
| PARSE | The sum of the PARSE calls of the variants. |
| PARSE (s) | The combined duration of those PARSE calls. |
| Total (s) | The sum of the inclusive time of the variants: the duration of all their calls. |
| SQL IDs | Some sql_id values of variants, to look up in the database. With Report privacy on, the column shows hidden by the mask. |
| Example | The text of the shape, with :s and :n in place of the literals. When the shape is among the statements of the report, the text comes with its number, such as #3, and leads to the detail, where the variants that cost the most are. When two shapes start the same way, each one shows the beginning, an … and the part where it differs from the others. |
Variants in the statement lists
In the report lists, each shape with two or more variants is a single row, with the N variants badge before the text: in SQL statements, in Statements by component, in the recursive SQL of each statement, in Longest calls and in the Where it appeared table of the errors. The row text is the shape, and the contribution, the calls and the time are the sum of the variants: the profile and the total time do not change. The shape row has no SQL ID, because each variant has its own. When the variant count is estimated, the badge says about N variants. PARSE ERROR, which has no cursor, is still identified by its own text.
In the shape detail, the Variants that cost the most table shows the variants with the most time in their own calls, with the SQL ID, the calls, the time and the literals of each one (variants that cost the most).
With Report privacy turned on in My account, the literals of the variants come out as *** according to the checked options, and the SQL IDs of the variants do not appear with any of them, neither in this section nor in the detail: the sql_id is computed from the text with the values and would allow the hidden literal to be recovered (hidden literals).
When it is a concern
The Diagnosis shows the Statement #n: n variants of the same SQL, differing only in literals finding (LITERAIS_SEM_BIND) for shapes with many variants in which PARSE weighs on the session time, with the View statement #n link. The finding text says how many PARSE calls the variants made, how many of them were hard parses, how long they took and how much the variants add up with all calls and recursive SQL.
The severity is low or medium: it goes up when the PARSE of the variants weighs more on the session. A shape can appear in this section without a finding, for example when there are few variants or their PARSE costs almost nothing.
The fix is to use bind variables in the application. CURSOR_SHARING=FORCE in the database reduces hard parses while the application does not change, but it is only a stopgap (literals instead of binds).
Other sections give the same signal: PARSE with Misses close to Count in Call types (many hard parses) and, in the profile, PARSE CPU and Concurrency waits on the shared pool and the library cache, whose cards point to this section.
Where to go next
The synthetic trace of the sample has no statements that differ only in literals, and the sample report does not have this section. The reports of the real cases do: see the section in case 1 and in case 2, and the literals finding in the Diagnosis of case 1.
- Literals instead of binds, for the cause and the fix.
- Many hard parses, the most common effect on time.
- SQL statements, where the shape appears as a single row.