OPTIMIZER_IGNORE_HINTS Oracle Database 18C

In 18C release optimizer parameter OPTIMIZER_IGNORE_HINTS(was underscore parameter) is now documented.

If set to TRUE the optimizer ignores embedded hints. Can be changed on system or session level.

Let’s create test table to see how it works. Important note the table has got primary key constraint so it means index.

CREATE TABLE test_tbl
(
  id1 PRIMARY KEY,
  id2
) 
AS 
SELECT rownum,rownum 
  FROM dual 
CONNECT BY LEVEL < 1000;

SELECT count(*) FROM test_tbl;

  COUNT(*)
----------
       999

Embedded hint /*+ index(test_tbl) */ forces to use index

ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=FALSE;

EXPLAIN PLAN FOR
SELECT /*+ index(test_tbl) */ sum(id2) FROM test_tbl;

SELECT * FROM dbms_xplan.display(format=>'BASIC');

Plan hash value: 1092693181
 
----------------------------------------------------------
| Id  | Operation                            | Name      |
----------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |
|   1 |  SORT AGGREGATE                      |           |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TBL  |
|   3 |    INDEX FULL SCAN                   | SYS_C0012 |
----------------------------------------------------------

and here embedded hints are ignored

ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE;

EXPLAIN PLAN FOR
SELECT /*+ index(test_tbl) */ sum(id2) FROM test_tbl;

SELECT * FROM dbms_xplan.display(format=>'BASIC');

Plan hash value: 765159746
 
---------------------------------------
| Id  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  SORT AGGREGATE    |          |
|   2 |   TABLE ACCESS FULL| TEST_TBL |
---------------------------------------

It’s very useful hint to see if embedded hints can be removed in legacy code :). I really love it.

Have a fun 🙂
Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.