The statement list shows how much of the total time each SQL statement and each PL/SQL block explains. It is where you start for the detail and the plan of a statement.
What the section shows
The SQL statements by contribution section (SQL statements in the index) has one row per distinct statement in the trace, or per shape, as explained below, from the largest contribution to the smallest and, on a tie, by inclusive time. The text at the top defines the two measures: Contribution: duration of the statement's depth-0 calls plus the waits between calls on its cursor. Inclusive: sum of all the statement's calls, at any depth.
The list shows 10 statements, or the number chosen in Statements per list at the top of the report. Three lines can appear below it:
- Showing N of M statements, when the choice cut the list;
- N other statements were left off the list, with their combined contribution: the report details only the statements with the largest contribution, and the rest go into that sum. When the count is estimated, the line starts with About N other statements;
- Time not attributed to any statement: the total time minus the contribution of all statements, such as the unaccounted-for time between calls and the waits with no cursor.
Statements that differ only in literals (SQL with the values in the text, without binds) become a single row, the shape, with the totals of the variants added up and the N variants badge before the text. Below the list, a note explains: Rows marked with variants add up statements that differ only in literals; in the text, :s and :n take the place of the literals. The list of shapes is in shareable texts, and the variants that cost the most are in the shape detail.
How to read it
| Column | What it says |
|---|---|
| # | The position of the statement, with a link to the detail. The same number identifies the statement in the other sections and in the findings. |
| Contribution (s) | The duration of the statement's level-0 calls plus the waits between calls on its cursor, such as the SQL*Net message from client between one FETCH and the next. The contributions plus the unattributed time add up to the total time. |
| % of total | The contribution divided by the total time. |
| Inclusive (s) | The sum of the duration (e=) of all the statement's calls, at any depth. It includes the recursive SQL the statement triggers, because the duration of a call already contains that of its children, and it does not include the waits between calls. |
| Type | The statement type, from oct= in the trace: SELECT, INSERT, UPDATE, DELETE, MERGE, PL/SQL and others. Without it, the first word of the text. |
| Depth | The depth (dep=): 0 for a statement called by the application, 1 or more for recursive SQL. |
| Exec and Fetch | How many EXEC and FETCH calls the statement had. |
| Rows | The sum of rows (r=) over all calls. |
| LIO | Logical reads: the sum of cr= and cu= over the calls. |
| PIO | Physical reads: the sum of p=. |
| SQL ID | The sql_id of the statement. Blank on the row of a shape, because each variant has its own. |
| Text | The beginning of the text, with a link to the detail. When two statements start the same way, each one shows the beginning, a … and the part where it differs from the others. Without the text in the trace, the statement key appears. |
In the sample report, statement #1 contributes 0.007 s (95.9% of total) and has an inclusive time of 0.002 s. The difference is the SQL*Net message from client wait on its cursor, which counts in the contribution and not in the inclusive time. Statement #2 has depth 1: it is recursive, and its contribution is zero.
Trace with many statements
In a trace with many distinct statements, such as one from an application that builds each SQL with the values in the text, the analysis hits limits and starts summing instead of keeping the detail of each statement. Each limit reached produces a warning at the top of the report:
- limit of N statements with their own detail reached: the new statements were added up by the shape of the text, the same SQL with the literals replaced by
:sand:n; - limit of N statement shapes reached: statements with new shapes were added up in a single row, (other statements beyond the limit);
- limit of N remembered variants reached: from then on, the variant count of each shape is estimated, and the new variants do not enter the table of those that cost the most.
With the estimated count, the numbers appear as about N: in Distinct statements in the Summary, in the variants badge, in the title of the literals instead of binds finding, in the Texts column of shareable texts and in the line for the statements left off the list. The total time, the profile and the sum of the contributions do not change: what shrinks is the per-statement detail.
When it is a concern
- A few statements take almost all the total time: start with the first one, in the detail and in the plan.
- LIO far above Rows: the plan reads many blocks for each row it returns.
- Exec in the thousands in a recursive statement, with one row per execution: a loop in PL/SQL (row by row).
- Fetch close to Rows in a level-0 statement: the application fetches one row at a time (many FETCH calls with few rows).
- Contribution much larger than the inclusive time: the statement time is in the waits between calls, usually the application (session waiting for the application).
- Similar statements that together add up to a lot of time: check whether they differ only in literals (shareable texts).
Where to go next
- SQL statements in the sample report.
- Statement detail, when you click the number or the text.
- Statements by component, to see the time of each statement by profile component.