The Diagnosis gathers the patterns WaitScope recognizes in the trace that often explain the profile. Each finding has a code and a page in this documentation, with the cause, the resolution and how to confirm it.
What the section shows
The section comes right after the Summary and appears only when the trace has at least one finding. The text at the top sums up the rule: Patterns that often explain what the trace shows, in order of severity. They are leads for the analysis, not conclusions.
Each finding is a block with the severity, the title, the share of total time when it applies, a text with the trace numbers and the most common cause, and a link to the place in the report that supports the finding.
How to read it
| Part | What it says |
|---|---|
| Severity | high, medium, low or info. Findings come in that order and, within the same severity, from the largest share of total time to the smallest. The same finding can come out with different severities depending on the weight of what it points to: slow reads in an event that barely counts in the total time, for example, come out as low. |
| Title | The pattern, with the component, the statement or the count that triggered it, such as Statement #1: plan operation 1 accounts for 98.0% of the time. |
| % of total time | The weight of what the finding points to: the component, the wait class, the PARSE time or, in findings about a statement, the inclusive time of the statement. Count findings, such as errors, rollbacks, slow calls and deadlock, have no percentage. |
| Text | The trace numbers and what usually lies behind them. |
| Link | Leads to the evidence: View in profile, View profile by class, View latency distribution, View call types, View shareable SQL, View longest calls, View errors, View summary or View deadlock. In findings about a statement, it leads to the statement, with View statement #n, or to the plan operation, with View in the plan of statement #n. |
The findings and the page for each one
The code does not appear on screen: it is the name of the rule, which the API returns in the code field. In the title, the parts in italics change with the trace.
| Code | Title in the report | Page |
|---|---|---|
TEMPO_NA_APLICACAO | The session spent most of its time waiting for the application | Session waiting for the application |
COMPONENTE_DOMINANTE | component dominates response time | Dominant component |
CPU_DOMINANTE | CPU dominates response time | Dominant CPU |
NAO_CONTABILIZADO_DENTRO | A lot of time inside calls with no CPU or recorded wait | Unaccounted-for time within calls |
NAO_CONTABILIZADO_ENTRE | Time gaps between calls | Gaps between calls |
CPU_ACIMA_DO_DECORRIDO | Measured CPU above elapsed time | CPU above elapsed time |
LEITURA_LENTA | Slow single-block reads in event, or Single-block reads slower than expected in event | Slow reads |
MUITAS_LEITURAS | Many single-block reads (event) | Many reads |
HARD_PARSE | Many hard parses | Many hard parses |
PARSE_POR_EXECUCAO | Statement #n: one PARSE per execution (n PARSE, n EXEC) | One PARSE per execution |
LITERAIS_SEM_BIND | Statement #n: n variants of the same SQL, differing only in literals, or, with the estimated count, Statement #n: about n variants of the same SQL, differing only in literals | Literals instead of binds |
FETCH_PEQUENO | Many FETCH calls with few rows | Many FETCH calls with few rows |
LINHA_A_LINHA | Statement #n executed n times, or Statement #n read row by row: n FETCH calls | Row by row in PL/SQL |
BLOQUEIO | Waiting on a lock held by another session | Lock held by another session |
CONCORRENCIA | Contention on internal database structures | Internal contention |
COMMIT | A lot of time waiting for redo writes at commit | Commit waits |
CONFIGURACAO | Waits tied to database configuration | Database configuration |
CLUSTER | Block transfers between RAC instances | Block transfers in RAC |
RESOURCE_MANAGER | Session throttled by Resource Manager | Resource Manager |
PLANO_PONTO_QUENTE | Statement #n: plan operation id accounts for x% of the time, or x% of logical reads | Operation that dominates the plan |
ESTIMATIVA_ERRADA | Statement #n: wrong row estimate in plan operation id | Wrong row estimate |
CHAMADAS_LENTAS | n calls at level 0 above 0.100 s | Slow calls |
ERROS_ORACLE | n Oracle errors in the trace | Oracle errors |
FIM_DE_DADOS | n ORA-01403 (no data found) | No data found (ORA-01403) |
ROLLBACK | n rollbacks | Rollback |
DEADLOCK | deadlock type between sessions a and b, such as Row deadlock (TX in mode X) between sessions 12 and 34 | Deadlock |
TRACE_VAZIO | Trace with no time span | Empty trace |
When it is a concern
- Start with the high-severity findings and with those that take the largest share of total time: no tuning saves more than the time the finding points to.
- Before acting, open the finding link and check the number in the report. The finding is a lead; the profile and the statement are what confirm it.
- Info findings, such as slow calls and no data found, record a fact that is usually normal. Read them, but do not start with them.
- An empty Diagnosis does not mean a trace without problems: the rules cover known patterns. The profile is still the basis of the analysis.
Where to go next
- Diagnosis of the sample report: two medium-severity findings, the wait for the application with 71.3% of total time and operation 1 of the plan of statement #1, with 24.6%.
- The method, to know in which order to tackle the findings.
- Response time profile, where most findings are confirmed.