Posted by
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.
Learning is not a spectator sport 
Hi
We have used hint mechanism to identify sql-statements when looking at them from v$sql.
e.g.
select –+ package_name.procedure_name
…
Helps a lot in identifiying and correcting sql-statements.
Same behaviour could be accomplished by dbms_application_info.set_module, but this is more complicated to use (stack behaviour, functions in sql-statements etc.).
What will happen to these ‘not actual hints’.
lh