Make Oracle 12c Adaptive query plan visible in Tree View

Toad for Oracle Idea Pond
1

Currently, an adaptive query plan can only be made visible in TOAD for Oracle when using DBMS_Xplan Display mode of the Explain Plan tab in the editor, and even then only for cached plans.

Within Oracle, it is possible to view an Adaptive Query Plan for any written SQL, even when not executed. So it should be possible to get this functionality in TOAD as well.

If not possible to get this into the tree view in a practical way, at a very minimum, the tree view should be altered as to not include all the steps of an adaptive plan as the output now can be very confusing when inlcuding all possibilities within an adaptive plan, rather than only those active or preferred.

2

It is available for non-cached plans in the current beta.   I'll investigate the possibility of getting it into tree view.

3

The tree uses the EXPLAIN PLAN command.    DBMS_XPlan use the DBMS_XPLAN package, of course.    As far as I can tell from the Oracle docs, "Adaptive" is only an option in DBMS_XPLAN, not EXPLAIN PLAN.    

It sounds like in the last paragraph of your post, you are getting adaptive plan output mingled into your Explain Plan tree in Toad?      I'm not sure how that would happen.   Can you send me a screen shot and spool sql output?  Send it to john.dorlon@quest.com.   Thanks.

4

Looking a little further...I see that I could get adaptive data into the tree if I used a combination of EXPLAIN PLAN and DBMS_XPLAN in Oracle 12.1 by using the undocumented GET_PLAN_ROWS function (example below), but the problem with that is that there is no column that identifies the steps as active or inactive.  

EXPLAIN PLAN

SET STATEMENT_ID = 'abc'

into SYS.PLAN_TABLE$

FOR select * from all_all_tables;

select * from table(dbms_xplan.get_plan_rows(

table_name   => 'SYS.PLAN_TABLE$',

statement_id => 'abc',

format      => 'ADAPTIVE'

));

5

Hi John

Apologies for the late response, had a few busy weeks.

I created a test case on an Oracle 12.1.0.2 as detailed below.

CREATE TABLE TST1

NOLOGGING AS

      SELECT ROWNUM id, DBMS_RANDOM.STRING (OPT => 'X', LEN => 40) txt

        FROM DUAL

  CONNECT BY ROWNUM < 1001;

CREATE TABLE TST2

NOLOGGING AS

      SELECT ROWNUM id, DBMS_RANDOM.STRING (OPT => 'X', LEN => 40) txt

        FROM DUAL

  CONNECT BY ROWNUM < 10001;

CREATE INDEX IX_ID

  ON TST1 (ID)

  NOLOGGING;

EXPLAIN PLAN

  SET STATEMENT_ID = 'ADAPT'

  FOR

     SELECT tst1.txt

       FROM tst1 JOIN tst2 USING (Id)

      WHERE tst2.txt LIKE 'A%';

SELECT plan_table_output

 FROM TABLE (DBMS_XPLAN.DISPLAY (NULL, 'ADAPT', '+adaptive'));

Plan hash value: 2583778186

-----------------------------------------------------------------------------------------

|   Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT              |       |   238 | 21420 |    21   (0)| 00:00:01 |

|  *  1 |  HASH JOIN                    |       |   238 | 21420 |    21   (0)| 00:00:01 |

|-    2 |   NESTED LOOPS                |       |   238 | 21420 |    21   (0)| 00:00:01 |

|-    3 |    NESTED LOOPS               |       |       |       |            |          |

|-    4 |     STATISTICS COLLECTOR      |       |       |       |            |          |

|  *  5 |      TABLE ACCESS FULL        | TST2  |   238 | 10710 |    17   (0)| 00:00:01 |

|- *  6 |     INDEX RANGE SCAN          | IX_ID |       |       |            |          |

|-    7 |    TABLE ACCESS BY INDEX ROWID| TST1  |     1 |    45 |     4   (0)| 00:00:01 |

|     8 |   TABLE ACCESS FULL           | TST1  |  1000 | 45000 |     4   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  1 - access("TST1"."ID"="TST2"."ID")

  5 - filter("TST2"."TXT" LIKE 'A%')

  6 - access("TST1"."ID"="TST2"."ID")

Note

-----

  - this is an adaptive plan (rows marked '-' are inactive)

This is the tree view in Toad (12.10.0.30)

Plan

SELECT STATEMENT  ALL_ROWSCost: 21  Bytes: 21.420  Cardinality: 238  

8 HASH JOIN  Access Predicates: "TST1"."ID"="TST2"."ID"  Cost: 21  Bytes: 21.420  Cardinality: 238  

6 NESTED LOOPS  Cost: 21  Bytes: 21.420  Cardinality: 238  

4 NESTED LOOPS  

2 STATISTICS COLLECTOR  

1 TABLE ACCESS FULL TABLE WEKIR.TST2 Filter Predicates: "TST2"."TXT" LIKE 'A%'  Cost: 17  Bytes: 10.710  Cardinality: 238  

3 INDEX RANGE SCAN INDEX WEKIR.IX_ID Access Predicates: "TST1"."ID"="TST2"."ID"  

5 TABLE ACCESS BY INDEX ROWID TABLE WEKIR.TST1 Cost: 4  Bytes: 45  Cardinality: 1  

7 TABLE ACCESS FULL TABLE WEKIR.TST1 Cost: 4  Bytes: 45.000  Cardinality: 1.000  

I'll send a mail with the above details as well as screenshots and SQL spool

6

Hi Kirsten,

 The tree view gets its information directly from the plan table.   Unfortunately, I don't see anything in the plan table to indicate the active/inactive rows there of an adaptive plan.    I could look for the "statistics collector" comment and make a note that the plan is adaptive, but that's about it.   I think your best solution is to set Toad's explain plan display to use DBMS_XPLAN.

-John