A single component holding most of the time is the simplest case of the method: almost all the possible gain is there. The cause depends on which component dominates.
Symptoms
In the Diagnosis, the finding component dominates response time (COMPONENTE_DOMINANTE) appears, with the component name at the start, for example db file scattered read dominates response time. The text says how much of the total time comes from that component, as a percentage and in seconds, and ends with View in profile.
The finding appears when the first component of the profile holds most of the total time. The severity is always medium. It does not appear when another finding already covers the same time:
SQL*Net message from clienthas the session waiting for the application finding;- the unaccounted-for time within calls has a finding of its own;
- CPU and the wait classes with a finding of their own (Application, Concurrency, Commit, Configuration, Cluster and Scheduler) appear with the class finding, such as dominant CPU or locking.
In practice, it appears for events of the User I/O, Network, System I/O, Other and Administrative classes, for Idle waits other than SQL*Net message from client, for unclassified events and for the unaccounted-for time between calls.
Cause
Click the component name in the profile to open the card with what it measures, when it is normal and where to look. The most common cases:
| Component | What it usually is | Where to go next |
|---|---|---|
db file sequential read, cell single block physical read | Reading one block at a time: index access that reads too many blocks, or a slow disk | Many single-block reads and slow reads |
db file scattered read, direct path read, cell smart table scan | Full scan of a large table or index to return few rows | FULL operations in the execution plan and operation that dominates the plan |
direct path read temp, direct path write temp | A sort or hash join that did not fit in the session memory (PGA) and spilled to TEMP | PW column in the execution plan |
SQL*Net more data to client, SQL*Net message from dblink | A large response to the application, or a wait for the remote database of a database link | Rows per FETCH and the statement on the remote database |
PL/SQL lock timer | A pause requested by the code (DBMS_LOCK.SLEEP or DBMS_SESSION.SLEEP) | The PL/SQL block that makes the pause, in Statements by component |
PX Deq: Execute Reply | The session coordinates a parallel query and waits for the parallel execution processes | The work is in the traces of those processes |
| unaccounted-for time between calls | A trace without waits, turned on and off in the middle of the operation, or an incomplete collection | Time gaps between calls |
Resolution
- Open View in profile and check the component row: Occurrences, Avg (s) and Max (s) tell whether there are many short waits or few long ones.
- Find the component in the cause table. If another finding of the Diagnosis covers the same component, such as slow reads or many reads, start with it.
- In Top statements by component, open the group of the component and find the statement that spent the most on it.
- In the statement detail, check the Statement profile and the plan: the highlighted operation is usually the one that generates the component.
- If the component is a wait for a resource (disk, network, remote database), also see Objects with the most wait time and the load on that resource in the same period, outside the report.
How to confirm
After the change, generate a new trace of the same operation and compare: the Duration (s) of the component and the Total time should drop together. The finding disappears when no component holds most of the total time anymore. If the total time dropped only a little, the time moved to another component: attack the new largest one.
In the report
In the sample report, the largest component is SQL*Net message from client, which has a finding of its own, so this finding does not appear. The case 1 report, from a real trace, has this finding for the unaccounted-for time between calls (case 1 Diagnosis). The links below open the sections of the example:
- Diagnosis: the finding and the View in profile link.
- Response time profile: the component is the first row, with the highest % of total.
- By wait class: the class of the component and its other components, in Top components.
- Top statements by component and by class: the statements that spent the time of the component.
- Latency distribution: the duration buckets of the waits of each event.
Also read the pages on the profile, the wait classes and the statements by component.