Once you know which component weighs most, the question is which statement spent that time. This section answers it by profile component and by wait class, with the statements that spent the most in each one.
What the section shows
The Top statements by component and by class section (Statements by component in the index) has two blocks:
- By profile component: one group for each of the largest profile components that have time in some statement, in profile order;
- By wait class: one group for each class with time in some statement.
Each group opens and closes with a click; the first one in each block is already open. The group row shows the name, the type (wait, cpu or unaccounted-for), the seconds, the share of total time and how many statements spent time in it.
The time of each statement comes from its exclusive profile: what the statement itself spent, without the recursive SQL it triggers. If a SELECT calls a function that reads another table, for example, the read counts in the SQL inside the function, which is recursive, and not in the SELECT.
How to read it
| Column | What it says |
|---|---|
| # | The position of the statement in SQL statements, with a link to its detail. |
| Time (s) | How much the statement spent in the component or class. |
| % of total | The statement's share of the group time, not of the total session time. The weight of the group in the total time is on the group row. |
| 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 statement text, with a link to the detail. On the row of a shape, which adds up statements that differ only in literals, the N variants badge comes before the text (variants in the lists). |
The % of total column of a group can add up to less than 100%. Part of the time belongs to no statement: the unaccounted-for time between calls and the waits with no cursor. Part can belong to statements outside the list.
Each group shows up to 10 statements, or the number chosen in Statements per list, at the top of the report. When there are more, the group ends with N more statements spent time here; raise the number at the top of the page to see them.
In the sample report, the db file sequential read group adds up to 0.001 s, 18.5% of total time: statement #1 has 76.9% of that time and statement #2, which is recursive, 23.1%.
When it is a concern
- One statement takes almost all the time of the largest component: go after that statement, through the detail and the plan.
- The component time is spread over many similar statements: the problem is an application pattern, not one SQL. Check whether the texts differ only in literals (shareable texts).
- A recursive statement shows up at the top of a read or CPU group: the cost is in the function or the trigger, not in the statement the application called (recursive SQL).
Where to go next
- Statements by component in the sample report and the db file sequential read group.
- Statement detail, the next step after choosing the statement.
- Response time profile, to choose the component.