Plan operation that concentrates the work

The plan of a heavy statement usually spends almost everything in one or two operations. The finding points to which one, gives a hint of what is usually behind it and takes you straight to the plan row.

Symptoms

In the Diagnosis, the finding Statement #n: plan operation id accounts for p% of the time (PLANO_PONTO_QUENTE) appears. When the operation's share of logical reads is larger than its share of time, the title ends in of logical reads. The statement number is the one in the SQL statement list, the operation number is its Id in the plan, and the percentage is the operation's own share of the plan. In the sample report, the finding is Statement #1: plan operation 1 accounts for 98.0% of the time.

Next to the title, the of total time percentage is the statement's weight, by inclusive time (all its calls, at any depth). The text starts with the operation name, gives the hint, lists the other operations that also weigh (Also significant) and ends with View in the plan of statement #n.

The finding appears for the statements that weigh on the total time, by inclusive time, when the plan, built from the trace STAT lines, has more than one operation and one of them concentrates the time or the logical reads. A single-operation plan has nothing to point to. There is one finding per statement, from its first plan with a highlighted operation, starting with the statements that weigh the most.

The severity is low and becomes medium when the statement weighs more on the total time.

Cause

The self value of an operation subtracts that of the operations below it, which feed it. The highlighted operations are the ones that weigh the most in the plan by this self value. The finding's hint depends on the type of operation:

The hint can combine the one for the operation type with those for writes to TEMP, many executions and a wrong estimate.

Resolution

1. Click View in the plan of statement #n.

   Result: the statement detail opens on the operation's row. In Where the plan spends time, the highlighted operations appear numbered, from the heaviest to the lightest, with each one's share of time and of logical reads.

2. Click the operation name to open the card with what it does, when it is good, when it is bad and which plan columns to check.
3. Compare the operation's Rows column with that of the operation right below it. If the table returns far fewer rows than the index brought, the filter is on the table and the index lacks its column.
4. Look at the access and filter predicates, which the trace STAT lines do not carry. With the cursor still in the shared pool, use the SQL ID at the top of the statement detail:

   select *
     from table(dbms_xplan.display_cursor('&sql_id', null, 'TYPICAL'));

5. Apply the fix for the operation type:
   • access by ROWID with a filter on the table: an index with the filter columns, equality columns first;
   • FULL read with a selective filter: an index for the filter;
   • skip scan: an index that starts with the filtered column;
   • wide index range: the equality column before the range column;
   • writes to TEMP: more memory for the workarea (pga_aggregate_target) or fewer rows reaching the sort or the hash join;
   • many executions: the operation is the inner side of a NESTED LOOPS. Fix the estimate of the outer side or change the join to HASH JOIN.
6. To see the table's columns and indexes, click the number in the plan's obj column. The object page, with ready-made queries, exists only in the console; in the downloaded HTML, the number opens the object detail, without the queries.

If the Diagnosis also shows a wrong row estimate for the same statement, start with it: with the right estimate, the optimizer may choose another plan on its own.

On a row with the N variants badge, which adds up SQL that differs only in literals, the statement's weight is the sum of the variants. In the detail, each different plan appears once, with the variants' records added up and the rows of the slowest recorded execution among them: this is the plan in which the finding points to the operation.

How to confirm

1. Apply one change at a time.
2. Turn on tracing for the same operation. With the DBMS_MONITOR default, the trace records the plan of the first execution of each cursor; to record it for all of them, use plan_stat => 'ALL_EXECUTIONS':

   begin
     dbms_monitor.session_trace_enable(session_id => &sid, serial_num => &serial,
                                       waits => true, binds => false,
                                       plan_stat => 'ALL_EXECUTIONS');
   end;
   /

3. Upload the new file and open the detail of the same statement.
4. Compare the operation's Self time (s) and Self LIO and the statement's Inclusive, at the top of the detail.

Result: the operation no longer dominates the plan, or the plan changes (another number in Execution plan). When the statement no longer weighs on the total time, it leaves the finding.

In the report

The sample report has this finding, made with the synthetic trace; the links open each part of it.

• Diagnosis: the finding Statement #1: plan operation 1 accounts for 98.0% of the time, with the TABLE ACCESS BY INDEX ROWID hint and operation 2 in Also significant.
• Detail of statement #1: Share of total time, Inclusive, statement profile and Where the plan spends time (statement detail).
• Plan operation 1: the highlighted row, with Self time (s), % time, Self LIO and % LIO (execution plan).
• SQL statements by contribution: the statement number and its weight (SQL statements).

In the console report, the finding's link opens the statement page; in the downloaded HTML, it opens the statement detail in place of the report, right at the plan operation.

The reports of real traces also have this finding: see the Diagnosis of case 1 and that of case 2.