The execution plan shows the path the statement took to find the rows and how much each step cost. The report separates what belongs to each operation and highlights the ones that concentrate the work.
What the section shows
In the statement detail, each plan recorded in the trace becomes an Execution plan block followed by the plan hash. The plan comes from the STAT lines of the trace; without them, the block does not appear (what the trace contains). Below the title, From the STAT lines in the trace. or, when the same plan was recorded more than once, plan written N times, showing the last one. For a statement with the N variants badge, the plan adds up the recordings of the variants and shows the rows of the slowest recorded execution among them, and the note says plan written N times, showing the slowest recording.
When the plan has more than one operation, the Where the plan spends time block lists the highlighted operations, by each operation's own value, without that of the operations below it: the ones that concentrate the time or the logical reads of the plan, from the heaviest to the lightest.
Each item has the Id N · operation link, its share of time and of logical reads and, when there is one, an explanation of what usually lies behind it, such as Visits the table row by row from the index: unselective index or missing the columns the filter uses.
The number of each highlighted operation also appears on its row in the plan and in the statement text, marking the object it reads.
How to read it
STAT gives the time and the reads of each operation added to those of the operations below it, which are the ones that feed the operation with rows. The report shows these cumulative values and also the self values, which subtract the operations below.
| Column | What it says |
|---|---|
| Id | The id= of the operation. It is the target of the View in the plan of statement #n links in the findings. |
| Operation | The operation and the object, indented by depth in the plan. Click it to open the card (the card of each operation). |
| Exec. | How many times the operation ran (str=). Blank when the trace does not have this field. |
| Rows | The rows the operation returned (cnt=), added up over all the times it ran. |
| Rows/exec | Rows divided by Exec.: what each execution returned. |
| Estimated | The rows per execution the optimizer estimated (card=). |
| Self time (s) and % time | The time of the operation without that of the operations below, and its share of the sum of the self time of all operations. |
| Self LIO and % LIO | The logical reads of the operation without those of the operations below, and their share of the plan. |
| PR | Blocks read from disk (pr=), cumulative like CR. |
| PW | Blocks written (pw=), usually to TEMP, by a sort or hash join that did not fit in memory. |
| Time (s) | The cumulative time (time=), including the operations below. |
| CR | The cumulative logical reads (cr=), including the operations below. |
| Cost | The optimizer cost (cost=). |
| obj | The object_id of the operation's object. In the console, it leads to the object page; in the downloaded HTML, to the object detail. |
Oracle measures the time per operation by sampling, so the time of an operation may not add up with that of the operations below; logical reads are always counted. When the sample gives a negative self time, the report shows zero.
In the sample report, operation 1, TABLE ACCESS BY INDEX ROWID T, has CR 10 and operation 2, INDEX UNIQUE SCAN T_PK, right below it, has CR 2. The self LIO of operation 1 is 8, or 80.0% of the logical reads of the plan, and its self time is 98.0% of the time.
Estimated and actual rows
Rows/exec and Estimated turn red when the optimizer got the number of rows wrong by a factor of 10 or more, as the note below the plan says; an error in an operation with few rows does not turn red. Hovering over the estimated number shows the error in full, such as the optimizer estimated 1 row per execution and got 5,000 (5,000 times more). The comparison needs str= in the trace.
With the wrong estimate, the optimizer picks a poor join order and method. The wrong row estimate finding points, in the statements that weigh on total time, to the operation where the wrong estimate starts, in a branch of the plan that does real work: the error from below propagates upward.
The card of each operation
Click an operation to open its card. The console has a card for more than 50 operations, such as TABLE ACCESS FULL, INDEX RANGE SCAN, NESTED LOOPS, HASH JOIN and SORT ORDER BY; the Exadata STORAGE variant uses the card of the regular operation. An operation outside the catalog appears without a button. The card has:
- Good when
- When the operation is the right choice.
- Bad when
- The pattern that makes it expensive and its usual cause.
- In the plan, check
- Which columns to check, such as the rows of this operation compared with those of the index scan right below.
When it is a concern
- A highlighted operation with high self LIO and few rows: it reads too many blocks for what it returns. In an index access, the index is unselective or lacks the filter column.
- Exec in the thousands on the inner side of a
NESTED LOOPS: the operation runs once per row of the outer side, and the low cost of each execution becomes a high total cost. - PW above zero in a sort or a hash join: the operation wrote to TEMP because the session memory was not enough.
- Numbers in red: the wrong estimate usually comes from stale statistics, correlated columns or bind peeking.
TABLE ACCESS FULLon a large table to return few rows: an index for the filter is missing, or it is not used.
The operation that dominates the plan finding points to the first highlighted operation of the statements that weigh most on total time, with the View in the plan of statement #n link.
Where to go next
- Operation 1 of the plan of statement #1, in the sample report: click
TABLE ACCESS BY INDEX ROWID Tto open the card. - Objects, to query the table or index of the operation in the database.
- Statement detail, for the calls and the recursive SQL.