در اوراکل 21c تابعی با نام compare_explain به dbms_xplan اضافه شد که امکان مقایسه بین explain planهای دو دستور را فراهم می کند. در قسمت انتهایی گزارش(Comparison Results) این تابع، تفاوت دو plan نمایش داده خواهد شد.

مثال زیر را ببینید.

SQL> create table mytbl as select * from dba_objects;

Table created

SQL> create index ind1_object_id on mytbl(object_id);

Index created

SQL> explain plan  set statement_id = ‘Plan1’  for select /*+ full(mytbl) */ * from mytbl where object_id=9;

Explained

SQL> explain plan  set statement_id = ‘Plan2’ for select /*+ index(mytbl) */ * from mytbl where object_id=9;   

Explained

SQL> VARIABLE varvar1 varchar2(9000)

SQL> exec :varvar1 := dbms_xplan.compare_explain(‘Plan1′,’Plan2’);

PL/SQL procedure successfully completed.

SQL> select :varvar1 from dual;

COMPARE PLANS REPORT

———————————————————————————————

  Current user           : USEF

  Total number of plans  : 2

  Number of findings     : 1

———————————————————————————————

COMPARISON DETAILS

———————————————————————————————

 Plan Number            : 1 (Reference Plan)

 Plan Found             : Yes

 Plan Source            : Plan Table

 Plan Table Owner       : USEF

 Plan Table Name        : PLAN_TABLE

 Statement ID           : Plan1

 Plan ID                : 13

 Plan Database Version  : 21.0.0.0

 Parsing Schema         : “USEF”

 SQL Text               : No SQL Text

Plan

—————————–

Plan Hash Value  : 659371492

———————————————————————-

| Id  | Operation           | Name  | Rows | Bytes | Cost | Time     |

———————————————————————-

|   0 | SELECT STATEMENT    |       |    1 |   142 |  428 | 00:00:01 |

| * 1 |   TABLE ACCESS FULL | MYTBL |    1 |   142 |  428 | 00:00:01 |

———————————————————————-

Predicate Information (identified by operation id):

——————————————

* 1 – filter(“OBJECT_ID”=9)

———————————————————————————————

 Plan Number            : 2

 Plan Found             : Yes

 Plan Source            : Plan Table

 Plan Table Owner       : USEF

 Plan Table Name        : PLAN_TABLE

 Statement ID           : Plan2

 Plan ID                : 14

 Plan Database Version  : 21.0.0.0

 Parsing Schema         : “USEF”

 SQL Text               : No SQL Text

Plan

—————————–

Plan Hash Value  : 2344436349

————————————————————————————————-

| Id  | Operation                             | Name           | Rows | Bytes | Cost | Time     |

————————————————————————————————-

|   0 | SELECT STATEMENT                      |                |    1 |   142 |    2 | 00:00:01 |

|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MYTBL          |    1 |   142 |    2 | 00:00:01 |

| * 2 |    INDEX RANGE SCAN                   | IND1_OBJECT_ID |    1 |       |    1 | 00:00:01 |

————————————————————————————————-

Predicate Information (identified by operation id):

——————————————

* 2 – access(“OBJECT_ID”=9)

Comparison Results (1):

—————————–

1. Query block SEL$1, Alias “MYTBL”@”SEL$1”: Access path is different –

    reference plan: FULL (line: 1), current plan: INDEX_RS_ASC (lines: 1, 2).