ENHANCEMENTS TO DBMS EXPLAIN PLAN

Hint Usage Reporting

Hints used in SQL statements can be difficult to 'debug'. For example, it is not always easy to understand why a particular hint is not being used. There is no error reporting for hints, so if they are incorrectly specified in any way, they will be ignored silently.

Hint usage reporting addresses this. It provides detailed information like invalid hints, conflicting hints, ignored hints (e.g. because of the query block location specified) and hints that successfully affected the final plan. Here is an example:

EXPLAIN PLAN FOR SELECT /*+ INDEX(t1) FULL(@sel$2 t1) */ COUNT(*)

FROM jobs t1

WHERE t1.job_id IN (SELECT /*+ FULL(t1) */ job_id FROM employees t1);

SELECT * FROM TABLE(DBMS_XPLAN.display(format => 'typical'));

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

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

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

| 0 | SELECT STATEMENT | | 1 | 17 | 3 (34)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 17 | | |

| 2 | NESTED LOOPS | | 19 | 323 | 3 (34)| 00:00:01 |

| 3 | SORT UNIQUE | | 107 | 963 | 2 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 963 | 2 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | 8 | 0 (0)| 00:00:01 |

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

...

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 3 (U - Unused (1))

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

4 - SEL$5DA710D3 / "T1"@"SEL$2"

U - FULL(t1) / hint overridden by another in parent query block

- FULL(@sel$2 t1)

5 - SEL$5DA710D3 / "T1"@"SEL$1"

- INDEX(t1)

New format specifiers have been created to support hint reporting:

 HINT_REPORT

 HINT_REPORT_USED

 HINT_REPORT_UNUSED

For example:

SELECT * FROM TABLE(DBMS_XPLAN.display(format => 'basic +hint_report'));