Two views complete the profile: the same time grouped by Oracle wait class, which shows what kind of wait it is, and the time of recursive SQL, which shows how much a statement spends in the statements it triggers.
What the section shows
Right below the profile, the By wait class table adds up the components by Oracle class (wait_class), with CPU and unaccounted-for time as classes of their own. The rows also add up to the total time. The class of each event follows v$event_name in Oracle 19c; the gc events go under Cluster, and an event WaitScope cannot classify goes under Unclassified.
| Class | What it groups |
|---|---|
| Idle | The session waiting for work. In a session trace, it is almost always SQL*Net message from client: application, network and user time. |
| Network | Data sent and received over SQL*Net, with the application or with another database over a dblink. |
| User I/O | Data reads and writes done by the session itself. |
| System I/O | I/O by background processes that the session waited for. |
| Commit | The redo write at COMMIT (log file sync). |
| Application | A row or table held by another session, locks taken by the application itself, errors returned. |
| Concurrency | Contention on internal structures: latches, mutexes, buffers. |
| Configuration | A configuration limit: redo log, buffer cache, ITL, sequence cache, space. |
| Scheduler | Resource Manager holding the session back. |
| Cluster | Blocks and messages exchanged between RAC instances. |
| Administrative and Other | Administrative commands and internal events with no more specific class. |
| CPU and Unaccounted-for | The CPU components and the two unaccounted-for times of the profile. |
| Unclassified | Events WaitScope cannot classify. |
How to read it
| Column | What it says |
|---|---|
| Class | The class name. Click it to open the card, with Normal when, A concern when and Where to look, as in the profile. |
| Duration (s) | The sum of the components of the class. |
| % of total | The share of total time, with the bar next to it. |
| Occurrences | The sum of the occurrences of the components. Blank for Unaccounted-for. |
| Top components | The components that weigh most in the class, in profile order. |
For an event under Unclassified, its class in the database is in v$event_name:
select name, wait_class
from v$event_name
where name = 'event name';Recursive SQL
Recursive SQL is a statement that another statement runs, not the application: the SQL inside PL/SQL functions and procedures, the SQL in triggers and the queries the database itself runs against the dictionary. In the trace, it has dep= greater than 0. The report shows this time in four places:
- In SQL statements, the Depth column is 0 for a statement called by the application and 1 or more for a recursive one. A recursive statement almost always has zero contribution: its time is already in the level-0 call that triggered it. Its own time is in Inclusive (s).
- In the statement detail, the Depth card says called by the application or Recursive SQL.
- In the Statement profile, the Recursive SQL (child statements) row, of type recursive, adds up the duration of the calls the statement triggered. Its card says it is normal in a PL/SQL statement that runs other SQL and that it is a concern when it adds up to a lot in a simple SELECT or DML: a PL/SQL function called per row, or a heavy trigger.
- At the end of the detail, Recursive SQL executed by this statement lists the child statements that took the longest, from longest to shortest, with a link to the detail of each one.
In Statements by component, the time of each component goes to the statement that spent it: a read done by recursive SQL counts in the child statement, not in the one that triggered it.
When it is a concern
When these classes weigh on the total time, the Diagnosis shows a finding of its own, with medium severity, with the View profile by class link. Those for Application, Concurrency and Commit go up to high when the class weighs a lot.
| Class | Finding |
|---|---|
| Application | Waiting on a lock held by another session |
| Concurrency | Contention on internal database structures |
| Commit | A lot of time waiting for redo writes at commit |
| Configuration | Waits tied to database configuration |
| Cluster | Block transfers between RAC instances |
| Scheduler | Session throttled by Resource Manager |
| CPU | CPU dominates response time |
In recursive SQL, the concern is a high Recursive SQL (child statements) row in a simple statement, and a recursive statement executed thousands of times with one row per execution: that is a loop in PL/SQL (row by row in PL/SQL).
Where to go next
- By-class table of the sample report: Idle with 71.3% of total time, User I/O with 18.5%.
- Statement #1 of the sample: in the statement profile, the Recursive SQL (child statements) row adds up to 0.000390 s, and the child appears at the end. Statement #2 is that child, with depth 1.
- Statements by component and by class, to see which statements spent the time of each class.