CODEWITHSUNDEEP
sqloracle-databaseexplain
OldProgrammer
OldProgrammer

Reputation: 12169

sql plan discrepancy in DBMS_XPLAN.DISPLAY_CURSOR

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'

enter image description here

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.

enter image description here

Why the discrepancy?

Upvotes: 2

Views: 1213

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21095

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"/>
    ....

Upvotes: 3

OldProgrammer
OldProgrammer

Reputation: 12169

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".

Upvotes: 0

Related Questions