sql plan discrepancy in DBMS_XPLAN.DISPLAY_CURSOR

Question

Running on Oracle 12.1 I am looking for SQL with full table scans in their plans.
When I look in:

select * from V$SQL_PLAN where sql_id = '89p47f9wnnwg9'

I get 21 rows back, one of them with TABLE ACCESS FULL

However, if I call this for the same sql _id with:

SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ('89p47f9wnnwg9', 0, 'ALL'))

I get results with only 13 rows from the plan table and the TABLE ACCESS FULL is missing.

Why the discrepancy?

check the column `CHILD_NUMBER` in V$SQL_PLAN. The above query shows all child cursors, the below only the cursor with child_number = 0 — Marmite Bomber, Feb 02 '18 at 13:51

Marmite Bomber · Answer 1 · 2018-02-04T13:51:07.850

One query string (represented by SQL_ID) can have several different execution plans. All are stored in V$SQL_PLAN and identified by different CHILD_NUMBER.

The function DBMS_XPLAN.DISPLAY_CURSOR has a second parameter, where you can pass the required child_number. If you omit the second parameter, the function returns all child cursors, so effectively you should see all rows from V$SQL_PLAN (but in different execution plans).

To see which child cursor is actually used, you may check the V$SESSION column SQL_CHILD_NUMBER.

Update for 12c

This obvious answer to the question why has V$SQL_PLAN more rows than DBMS_XPLAN.DISPLAY_CURSOR was the most probable one until 12c version of Oracle. 12c introduced Adaptive Query Optimization where some operation are marked by the optimizer as inactive. This choice can be switched by the execution engine. Such plans can be recognized by the operation STATISTICS COLLECTOR. The STATISTICS COLLECTOR tests on execution the actual number of rows and if it is higher than the from optimize calculated point of inflection switches the plan. (Example - nested loops work fine for few rows, but for a large number its "hanging"; contrary hash join work good for large number of rows, but for few rows it has a high overhead. Point of inflection should correspond to such number of row, where the costs are estimated the same).

Unfortunately there is no column in V$SQL_PLAN identifying the inactive operations.

This blog makes observation, that this information could be extracted the the column OTHER_XML using the element display_map attribute @skp

 <display_map>
    <row op="1" dis="1" par="0" prt="0" dep="1" skp="0"/>
    <row op="2" dis="2" par="1" prt="0" dep="2" skp="0"/>
    <row op="3" dis="2" par="2" prt="0" dep="2" skp="1"/>
    ....

V$SQL_PLAN.CHILD_NUMBER is 0, and that is what I passed in, and it still does not match. I cannot use V$Session because there may no longer be a record in there. Using V$SQLAREA, — OldProgrammer, Feb 02 '18 at 17:19
Posted same question on Oracle site, and got an answer that works: It's an adaptive plan - there are two inflection points (indicated by the STATISTICS COLLETOR) operations where Oracle can make a run-time decision. change 'ALL' to 'adaptive' in your call to display_cursor and you'll see all the lines and a note explaining how to identify the "inactive" lines Regards Jonathan Lewis — OldProgrammer, Feb 02 '18 at 17:23
@OldProgrammer very sorry for the inappropriate answer. I was mislead with the missing `CHILD_NUMBER` predicate in the query of `V$SQL_PLAN`. I extended my answer. — Marmite Bomber, Feb 04 '18 at 13:26

score 0 · Answer 2 · answered Feb 02 '18 at 17:39

Per Jonathan Lewis,

It's an adaptive plan - there are two inflection points (indicated by the STATISTICS COLLETOR) operations where Oracle can make a run-time decision.

change 'ALL' to 'adaptive' in your call to display_cursor and you'll see all the lines and a note explaining how to identify the "inactive" lines

Regards Jonathan Lewis

So

SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR ('89p47f9wnnwg9', null, 'ADAPTIVE'))

returns the row, but is flagged as "not used".

sql plan discrepancy in DBMS_XPLAN.DISPLAY_CURSOR

2 Answers2