An adaptive query plan includes alternatives prepared by the optimizer. Runtime information helps Oracle choose among those alternatives. For an adaptive join, a statistics collector can observe the number of incoming rows and buffer enough rows to support the choice between nested loops and a hash join.
Oracle DBA Lesson 101 Part 1 — Read prepared adaptive alternatives
The practical task is to identify the method used by the completed execution from the correct child cursor, its adaptive notes, and operation markers. Then use execution statistics to investigate the work that method performed.
How the choice works
Consider filtered order items joined to product information on PRODUCT_ID. With a small driving row set and a suitable index on the other input, nested loops can make a small number of indexed lookups. With more driving rows, a hash join can be preferable. These are cost dependent choices, influenced by the inputs and the available access paths.
The optimizer prepares eligible alternative subplans and inserts a statistics collector at the decision point. In Oracle's documented join example, the collector buffers incoming rows. Below the optimizer's threshold, the plan uses nested loops. When the threshold is reached, it uses the hash alternative. The threshold is internal and cost dependent. There is no fixed count that applies to every query.
Once all adaptive choices are resolved, the final plan stays fixed through the end of that execution. Selection is limited to the alternatives prepared in that plan. The mechanism can continue from work already performed. It does not restart every operation. Later execution feedback and directive behavior are covered in Part 02.
See adaptive query plans and joins.
Read the selected operations
The video uses selected operation IDs and markers from Oracle's documented adaptive join example in Query Optimizer Concepts, under Adaptive Query Plans: Join Method Example. Its tables are ORDER_ITEMS and PROD_INFO. The display is condensed to the relevant operation names and annotations. It is the documented order-items plan, separate from the employees statement later in this lesson.
| Operation ID and marker | Operation | Interpretation in the documented resolved plan |
|---|---|---|
*1 | HASH JOIN | The selected join method. The asterisk points to predicate information. |
-2 | NESTED LOOPS | Inactive alternative. |
-3 | NESTED LOOPS | Inactive alternative. |
-4 | STATISTICS COLLECTOR | Its buffering is disabled after the initial choice. |
The plan's Note section identifies it as adaptive and explains that the dash marks inactive operations. The asterisk has a separate purpose: it associates an operation with predicates. Read both symbols using their documented meanings.
Read the collector's inactive marker in the resolved plan with the execution sequence. It gathered rows for the initial decision, then disabled buffering so rows could pass through. On a later execution of an already resolved child cursor, Oracle can reuse the chosen plan. An inactive collector line alone is insufficient evidence to reconstruct everything that occurred on a particular execution.
Display the correct cursor
Use the statement's actual SQL_ID and child number. Bind :sql_id and :child_number to the captured values before using this form:
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
:sql_id, :child_number,
'ALLSTATS LAST +ADAPTIVE +PREDICATE'));DISPLAY_CURSORreads a cursor currently present in the cursor cache.- The first two arguments identify the statement and its child cursor. A statement can have several children.
ALLSTATS LASTasks for execution statistics for the last execution of that child. The statistics must have been collected. Adding this display option does not collect them afterward.+ADAPTIVEexposes adaptive details. For a completed execution, inspect the final plan. For an execution still in progress, the display can show the current plan.+PREDICATEretains access and filter predicate details. Keep the Note section as part of the evidence.
Check the statement text and the child header as well as the output rows. A missing cursor, missing privileges, or missing runtime statistics leaves the evidence incomplete. DISPLAY_CURSOR reads V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL, and V$SQL. The caller needs SELECT or READ on those views. Another session may execute a shared child cursor, so a shared cursor's LAST statistics need care when you associate them with one test.
See DBMS_XPLAN.
Match the statement to its child cursor
This statement is distinct from the documented order-items plan. In SQL*Plus, set the display, run the join, and fully fetch its rows before displaying the plan:
SET LINESIZE 180
SET PAGESIZE 0
SET SERVEROUTPUT OFF
SELECT /*+ GATHER_PLAN_STATISTICS */ /* dba101_join */
e.employee_name, d.department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary >= 60000;
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
NULL, NULL, 'ALLSTATS LAST +ADAPTIVE +PREDICATE'));GATHER_PLAN_STATISTICS requests runtime plan statistics for that query and adds measurement overhead. The comment dba101_join marks the statement so its child can be found later. Fully fetch the target query's rows before displaying its plan. In this immediate form, NULL for sql_id selects the session's last cursor. A null child argument can display all matching children. Run no intervening SQL, including SQL the client tool generates, and verify the returned statement header. For retained evidence, capture the actual SQL_ID and child number and use the explicit form above.
Find candidate children by the distinctive comment:
SELECT sql_id, child_number, executions, end_of_fetch_count,
is_resolved_adaptive_plan, sql_text
FROM v$sql
WHERE sql_text LIKE
'SELECT /*+ GATHER_PLAN_STATISTICS */ /* dba101_join */%'
ORDER BY sql_id, child_number;Match the full target statement and the actual execution. The first returned row can belong to a different child. The search requires the SQL text to begin as shown. Adapt the pattern if the client changes whitespace or inserts text. Use the chosen values in the explicit cursor display. END_OF_FETCH_COUNT counts complete executions of that child and may be smaller than EXECUTIONS after partial fetches. These are cumulative cursor counters, so use before and after observations when one execution must be separated from earlier ones.
IS_RESOLVED_ADAPTIVE_PLAN provides additional classification:
| Value | Meaning |
|---|---|
NULL | The cursor plan is not adaptive. |
N | Some adaptive choices are not yet resolved. |
Y | All adaptive choices are resolved. |
Correlate this field with the correct cursor's plan and notes. A resolved status by itself does not identify the join method.
See V$SQL.
Interpret Starts, E-Rows and A-Rows
Starts records how many times an operation started. E-Rows is its estimated output cardinality, and A-Rows is the actual output rows for the displayed statistics scope. For an operation with repeated starts, compare actual rows with estimates while accounting for the starts. Dividing actual rows by starts can provide an average per start when that interpretation fits the operation. An average may hide uneven work.
Inspect the relevant driving input. The final output row count does not, by itself, explain the adaptive threshold. Missing runtime statistics remain missing evidence. Zero output rows can arise because an active operation found no matches, a branch was skipped, or an inactive alternative was not used. Classify prepared alternatives from the adaptive markers and the Note section, then interpret their execution statistics.
A small query may produce an ordinary plan. That is a valid result. Classify it from the actual evidence, and annotate the documented adaptive example separately. Keep the default optimizer settings. Do not change hidden parameters to force an adaptive plan.
A difference from EXPLAIN PLAN can also reflect bind values and types, optimizer settings, schema or object differences, statistics, or cursor selection. Use the actual cursor's adaptive notes and operation markers to support a switching claim.
See explaining and displaying execution plans and OPTIMIZER_ADAPTIVE_PLANS.
Recap
An adaptive join selects an eligible alternative prepared in its plan. A statistics collector can buffer incoming rows and choose nested loops or a hash join at an internal, cost dependent threshold. There is no fixed row count for every query. After the choices are resolved, that execution keeps the final plan and can continue from work already performed. In the documented resolved plan, an asterisk ties an operation to predicates and a dash marks an inactive alternative. An active hash join with dashed nested loops means hash join was selected. The collector can appear inactive after the initial decision, once buffering is disabled. Display the correct child with DBMS_XPLAN.DISPLAY_CURSOR and ALLSTATS LAST +ADAPTIVE +PREDICATE. IS_RESOLVED_ADAPTIVE_PLAN shows whether every adaptive choice is resolved. It does not name the join method. Read Starts, E-Rows, and A-Rows on the driving input. An ordinary plan is a valid result. A difference from EXPLAIN PLAN still needs the cursor's adaptive notes and markers.
No comments:
Post a Comment