The detail of a statement brings together everything the trace says about it: its share of total time, the text, where its time went, the calls, the plan and the statements it triggered.
What the section shows
Click the number or the text of a statement in any list of the report, or the link of the finding about it, such as View statement #2. In the console, the detail opens on a page of its own, with the path Analyses / analysis name / Statement #n. In the downloaded HTML, the detail exists for the statements that appear in some list or finding and is built only when you click: it takes the place of the report on the page, and Back to the report or the browser's Back button take you back to the list. In the PDF, it is at the end, under Statement details.
The title gives the position and the type of the statement, such as #1 · SELECT, with the sql_id and the hash value (hv). For a shape, which adds up statements that differ only in literals, the title shows the N variants badge instead, because each variant has its own (variants in the lists). Right below come the numbers:
- Share of total time: the contribution of the statement, in seconds and as a share of total time (SQL statements);
- Inclusive: the duration of all the statement's calls;
- Depth: 0 with called by the application, or 1 or more with Recursive SQL. For a shape, it is the lowest depth among the variants; when it is 0, the number comes with the lowest among the variants;
- Errors: how many ERROR lines the statement cursor had, when it had any (errors).
Then come the Text, Variants that cost the most (only for a shape), Statement profile and Calls blocks, one Execution plan for each recorded plan (execution plan) and Recursive SQL executed by this statement.
How to read it
Text
The statement text as the trace recorded it. When the plan has highlighted operations, their objects are marked in the text with the number of the highlighted operation, and a note below says which operation uses each one. For an index access, the mark goes on the table of the index, because the index does not appear in the SQL. When the object is not in the text, the note says it comes from a view, a synonym or recursive SQL.
Two messages can appear in place of the text or below it: Text not available: the cursor was opened before the trace started. and Text truncated: the trace declares N characters., when the stored text is shorter than the declared one. For a shape, the text is the SQL with the literals replaced, and the note Shape text: the literals became :s (text) and :n (number). appears below.
With Report privacy turned on in My account, the SQL comes out with the quoted text, the numbers used as values, or both, replaced by ***, according to the options checked (hidden literals).
Variants that cost the most
For a shape, the table lists the variants that spent the most time in their own calls, from the one that spent the most to the one that spent the least:
| Column | What it says |
|---|---|
| SQL ID | The sql_id of the variant or, without it, the hash value, such as hv 123. The column disappears when no variant has either one. |
| Calls | How many calls the variant had. |
| Inclusive (s) | The duration of all the variant's calls. |
| Literals | The values of the variant, in the order they appear in the text, separated by commas: what changes from one variant to another. |
Use the literals to find the part of the application that builds the SQL and the SQL ID to look up the variant in the database. When the variant count is estimated, the variants the analysis did not remember one by one are left out of the table (trace with many statements). With Report privacy turned on, the literals come out as *** according to the options checked, and the SQL ID column disappears with any of them: the sql_id is computed from the text with the values and would allow the hidden literal to be recovered.
Statement profile
The exclusive profile of the statement: the waits inside its calls and between them on its cursor, the CPU of each call type, the unaccounted-for time within calls and the Recursive SQL (child statements) row, with the duration of the calls the statement triggered.
The columns are Component, Duration (s), % (the share of the statement's own time, not of the total time), the bar and Occurrences. The name of each component opens the same card as in the profile.
Calls
| Column | What it says |
|---|---|
| Call | The call type, in tkprof order: PARSE, EXEC, FETCH, UNMAP, SORT UNMAP, CLOSE and the others. |
| Count | How many calls of that type the statement had. |
| CPU (s) and Duration (s) | The sum of c= and of e=, in seconds with six decimal places. |
| Disk (PIO) | Blocks read from disk (p=). |
| Consistent (CR) | Logical reads in consistent mode (cr=), those of queries. |
| Current (CU) | Logical reads in current mode (cu=), typical of DML. |
| Rows | Rows returned or processed (r=). |
| Misses | Library cache misses (mis=). In PARSE, each miss is a hard parse. |
Recursive SQL executed by this statement
The child statements that took the longest, from longest to shortest, with #, Duration (s) and Text. The number leads to the detail of the child when it is among the statements of the report. A child that is a shape shows the N variants badge before the text.
When it is a concern
- Share of total time much larger than the inclusive time: the time is between the calls, and the statement profile shows which wait, almost always
SQL*Net message from client(session waiting for the application). - PARSE with a Count close to that of EXEC: the cursor is opened and closed on every use (one PARSE per execution). Misses close to the PARSE Count: hard parse (many hard parses).
- FETCH with a Count close to Rows: one row per call (many FETCH calls with few rows).
- Consistent (CR) far above Rows: the plan reads too many blocks for what it returns. Go to the highlighted operations of the plan.
- The Recursive SQL (child statements) row weighs on a simple SELECT or DML: a function called per row or a heavy trigger (row by row in PL/SQL).
Where to go next
- Statement #1 of the sample report: text with table T marked, profile with the recursive SQL, and the plan.
- Execution plan, to find the operation that spends the time.
- Objects, to go from the operation to the table or index in the database.