Search

Top 60 Oracle Blogs

Recent comments

18c and the ignoring of hints

 

One of the new features in 18c is the ability to ignore any optimizer hints in a session or across the entire database. A motivation for this feature is obviously our own Autonomous Data Warehouse, where we want to optimize queries without the potential “baggage” of user nominated hints strewn throughout the code.

This would seem a fairly easy function to implement, namely, as we parse the SQL, simply rip out anything that is a comment structured as a hint. At the Perth Oracle User Group conference yesterday, I had an interesting question from an attendee – namely, if all optimizer hints are being ignored, then does this mean that every hint will be ignored. In particular, what about the (very useful) QB_NAME hint? If we are just stripping out anything that is in a hint text format, we will lose those as well?

So it’s time for a test!

I’ll start with the default of optimizer hints being respected.


SQL> show parameter optimizer_ignore_hints

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_ignore_hints               boolean     FALSE

Using the standard SCOTT.EMP table, we’ll do a query that by default, will use an indexed access path (after all, we’re doing a simply primary key lookup).


SQL> select * from scott.emp s
  2  where empno = 123;

no rows selected

To prove that an index was used, we’ll examine the just executed query via DBMS_XPLAN.DISPLAY_CURSOR.


SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  353khf08wdpq1, child number 0
-------------------------------------
select * from scott.emp s where empno = 123

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=123)


19 rows selected.

The next test is to validate that I’m correctly specifying any hints, so I’ll run the same SQL with a FULL hint and double check that the hint is being respected by the optimizer.


SQL> select /*+ FULL(s) */ * from scott.emp s
  2  where empno = 123;

no rows selected

SQL>
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  fynghufd48tkr, child number 0
-------------------------------------
select /*+ FULL(s) */ * from scott.emp s where empno = 123

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=123)


18 rows selected.

So far so good. Now to see the effect of the new 18c parameter ‘optimizer_ignore_hints’. I’ll set it to true for this session, and then re-execute the previous query that had the FULL hint specified.


SQL> alter session set optimizer_ignore_hints = true;

Session altered.

SQL>
SQL> select /*+ FULL(s) */ * from scott.emp s
  2  where empno = 123;

no rows selected

SQL>
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  fynghufd48tkr, child number 1
-------------------------------------
select /*+ FULL(s) */ * from scott.emp s where empno = 123

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=123)


19 rows selected.

You can see that the FULL hint was ignored, and the original index access path has been restored.

Which brings us to the final test that motivated this blog post. What happens when the hint text contains a QB_NAME hint (which we would like to be preserved) and a FULL hint (which we would like to be ignored)?


SQL>
SQL> select /*+ QB_NAME(MY_QUERY_NAME) FULL(s) */ * from scott.emp s
  2  where empno = 123;

no rows selected

SQL>
SQL> select * from dbms_xplan.display_cursor(format=>'ALL');

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  9kg1866kgdmhs, child number 0
-------------------------------------
select /*+ QB_NAME(MY_QUERY_NAME) FULL(s) */ * from scott.emp s where
empno = 123

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

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

   1 - MY_QUERY_NAME / S@MY_QUERY_NAME
   2 - MY_QUERY_NAME / S@MY_QUERY_NAME

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=123)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPNO"[NUMBER,22], "S"."ENAME"[VARCHAR2,10], "S"."JOB"[VARCHAR2,9],
       "S"."MGR"[NUMBER,22], "S"."HIREDATE"[DATE,7], "S"."SAL"[NUMBER,22],
       "S"."COMM"[NUMBER,22], "S"."DEPTNO"[NUMBER,22]
   2 - "S".ROWID[ROWID,10], "EMPNO"[NUMBER,22]


34 rows selected.

Using the extended format of “ALL”, you can see that the QB_NAME information was preserved even though the FULL hint was ignored as desired.

Thus it is not just a simple “rip out all of the hint text” implementation.