Skip to Main Content
  • Questions

  • Remove a hint with DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR failed

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, David.

Asked: October 22, 2019 - 3:35 pm UTC

Last updated: October 28, 2019 - 5:21 pm UTC

Version: 18

Viewed 1000+ times

You Asked


Hello Masters,

I am testing the two packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR and they work fine : for exemple I can remove an ORDER BY from a SELECT.
But, there is always an exception, I have problems with Hints : I want to remove them and it failed...

Here are my test cases.

A table with 10 000 rows, 9 900 with the name DUPONT and 100 with the name MARTIN.
    SQL> create table TEST_HINT (ID NUMBER, NAME VARCHAR2(30 CHAR));
    
    SQL> begin
              for i in 1..10000
              loop
                  insert into test_hint values (i, 'DUPONT');
              end loop;
              end;
              /
         PL/SQL procedure successfully completed.
    
    SQL> update test_hint set name = 'MARTIN' where mod(id, 100) = 0;
    100 rows updated.
    
    SQL> commit;
        
    SQL> select count(*) from test_hint where name = 'MARTIN';
    COUNT(*)
    ----------
    100


I create the index and collect the stats.
    SQL> create index idx_test_name on test_hint(name);
    SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'TEST_HINT', CASCADE=>true);


If I do a SELECT with NAME = 'MARTIN', Oracle use the index : it's OK. 
    SQL> set autotrace traceonly
    SQL> select * from test_hint where name = 'MARTIN';
    100 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3244087319
    
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      | 100 |  1100 |   2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT     | 100 |  1100 |   2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN      | IDX_TEST_NAME | 100 |     |   1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("NAME"='MARTIN')


If I do a SELECT with NAME = 'DUPONT', Oracle do a FTS : it's OK. 
    SQL> select * from test_hint where name = 'DUPONT';
    9900 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 11101196
    
    -------------------------------------------------------------------------------
    | Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |  9900 |   106K|     9 (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TEST_HINT |  9900 |   106K|     9 (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("NAME"='DUPONT')


If I use an INDEX hint, there is a problem because, with that SELECT, the cost raise from 9 to 72.
    SQL> select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';
    9900 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3244087319
    
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |  9900 | 106K|  72   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT     |  9900 | 106K|  72   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN      | IDX_TEST_NAME |  9900 |     |  26   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("NAME"='DUPONT')


So, if I have a SELECT order generated by a vendor with a hint, I want to remove this one. But it doesn't work...
First test with DBMS_SQL_TRANSLATOR.
    SQL> EXEC DBMS_SQL_TRANSLATOR.CREATE_PROFILE (profile_name =>'translate_hint');
    
    SQL> EXEC DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION ( profile_name => 'translate_hint', sql_text => 'select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = ''DUPONT''', translated_text => 'select * from test_hint where name = ''DUPONT''', enable => TRUE);
    
    SQL> alter session set sql_translation_profile = translate_hint;


Here, it is OK, the hint is removed. 
    SQL> DECLARE
        translated_text CLOB;
    BEGIN
        DBMS_SQL_TRANSLATOR.TRANSLATE_SQL(
        sql_text => 'select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = ''DUPONT''',
        translated_text => translated_text);
        DBMS_OUTPUT.PUT_LINE(translated_text );
    END;
    /
    select * from test_hint where name = 'DUPONT'
    
    SQL> alter session set events = '10601 trace name context forever, level 32'; 
    
    SQL> set autotrace traceonly


Oops, problem, the index is used. Why?
    SQL> select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';
    9900 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3244087319
    
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |  9900 | 106K|  72   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT     |  9900 | 106K|  72   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN      | IDX_TEST_NAME |  9900 |     |  26   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("NAME"='DUPONT')


What I want is that : 
    SQL> select * from test_hint where name = 'DUPONT';
    9900 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 11101196
    
    -------------------------------------------------------------------------------
    | Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |  9900 |   106K|     9 (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TEST_HINT |  9900 |   106K|     9 (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("NAME"='DUPONT')


OK, now I try with DBMS_ADVANCED_REWRITE to remove this hint.
My user has the role DBA.
    SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
    SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FORCE;
    
    SQL> SHOW PARAMETER REWRITE
    NAME         TYPE  VALUE
    ------------------------------------ ----------- ------------------------------
    query_rewrite_enabled       string  FORCE
    query_rewrite_integrity       string  TRUSTED


Ouch, impossible again to remove it. Oracle says that both orders are equals.
    SQL> exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', q'[select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT']', q'[select * from test_hint where name = 'DUPONT']', false);
    BEGIN sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', q'[select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT']', q'[select * from test_hint where name = 'DUPONT']', false); END;
    *
    ERROR at line 1:
    ORA-30394: source statement identical to the destination statement
    ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
    ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
    ORA-06512: at line 1


OK, I change the second order by adding ' and 1=1' to force Oracle to rewrite the order.
    SQL> exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', q'[select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT']', q'[select * from test_hint where name = 'DUPONT' and 1= 1]', false);
    PL/SQL procedure successfully completed.
    
    SQL> select * from DBA_REWRITE_EQUIVALENCES;
    OWNER
    --------------------------------------------------------------------------------------------------------------------------------
    NAME
    --------------------------------------------------------------------------------------------------------------------------------
    SOURCE_STMT          DESTINATION_STMT          REWRITE_MO
    -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
    HR
    NEW_QUERY
    select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPON select * from test_hint where name = 'DUPONT' and 1= 1      TEXT_MATCH
    
    SQL> set autotrace trace


And now, if I use the hint... It failed, the index is still used!
    SQL> select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';
    9900 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3244087319
    
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |  9900 | 106K|  72   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT     |  9900 | 106K|  72   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN      | IDX_TEST_NAME |  9900 |     |  26   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("NAME"='DUPONT')


If I execute the rewrited order : it is OK. 
    SQL> select * from test_hint where name = 'DUPONT' and 1= 1;
    9900 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 11101196
    
    -------------------------------------------------------------------------------
    | Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |  9900 |   106K|     9 (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| TEST_HINT |  9900 |   106K|     9 (0)| 00:00:01 |
    -------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("NAME"='DUPONT')


Well, can you tell me how to remove a hint from a SELECT with the both packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR?

Thank you very much for your answer.

David D from Paris (France)

and Chris said...

You say you're on 18c. So there's a MUCH easier technique available:

Set optimizer_ignore_hints to true!

The database then parses and optimizes the query as-if the hints didn't exist:

set serveroutput off
select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT                                                          
EXPLAINED SQL STATEMENT:                                                    
------------------------                                                    
select /*+ index(test_hint idx_test_name)*/ * from test_hint where name     
= 'DUPONT'                                                                  
                                                                            
Plan hash value: 3244087319                                                 
                                                                            
-------------------------------------------------------------               
| Id  | Operation                           | Name          |               
-------------------------------------------------------------               
|   0 | SELECT STATEMENT                    |               |               
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT     |               
|   2 |   INDEX RANGE SCAN                  | IDX_TEST_NAME |               
-------------------------------------------------------------  

alter session set 
  optimizer_ignore_hints = true;
  
select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT                                                          
EXPLAINED SQL STATEMENT:                                                    
------------------------                                                    
select /*+ index(test_hint idx_test_name)*/ * from test_hint where name     
= 'DUPONT'                                                                  
                                                                            
Plan hash value: 11101196                                                   
                                                                            
-----------------------------------------------                             
| Id  | Operation                 | Name      |                             
-----------------------------------------------                             
|   0 | SELECT STATEMENT          |           |                             
|   1 |  TABLE ACCESS STORAGE FULL| TEST_HINT |                             
-----------------------------------------------

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yes!

David DUBOIS, October 23, 2019 - 8:24 pm UTC


Excellent Chris, like always :-) Thank you very much for your answer.

But do you agree with me that there are limitations about hints for these two packages? Both are very good but I was disappointed to see that I cannot remove hints with these packages (it is because I like to use them).

Your solution is very good but if there are many hints in the SELECT, we erase all the hints, is it impossible (maybe I am wrong) to remove only one hint?

Have a nice day.

David
Chris Saxon
October 24, 2019 - 8:32 am UTC

But do you agree with me that there are limitations about hints for these two packages?

You're kinda abusing these features!

Being able to remove hints would be nice. But really these are intended for migrations to Oracle Database. So you can map functions/features in the other RDBMS to the Oracle equivalent.

If you'd like to see these support hints, submit an ER. Or add it to the database ideas forum and see if you can gather community support:

https://community.oracle.com/community/groundbreakers/database/database-ideas

Your solution is very good but if there are many hints in the SELECT, we erase all the hints, is it impossible (maybe I am wrong) to remove only one hint?

Correct, setting optimizer_ignore_hints ignores ALL hints. You can't selectively disable some of the hints with this.

David DUBOIS, October 26, 2019 - 9:12 am UTC

Hi Chris,

Thanks for the clarification, I did not see in the doc that these two packages were created specifically for migrations.

What I love with these is that we have direct access to SQL orders, regardless of how they were launched (batch, third party program ...) and by whom : I call that microsurgery.

Regarding the solution of making an ALTER SESSION, it is sometimes complicated to set up. If to launch the problematic SQL statement I log in, I run the program, so OK, I can do an ALTER SESSION before and after the batch launch (but it impacts ALL SQL orders of the session).
On the other hand, if the program is launched anytime by anybody (it happens ...), then I have one solution: to make a trigger AFTER LOGON and hope to find via SYS_CONTEXT AND USERENV a data allowing of identify who launches the program. So yes, I can do an ALTER SESSION to not impact other users.

If it's impossible, then I have these solutions but they are not satisfactory because they impact all users and all orders
- an ALTER SESSION in an AFTER LOGON trigger for all sessions
- an ALTER SYSTEM

That's why I'm disappointed that these two packages do not manage the hints because, with them, we can do microsurgery and processed a SQL order and one without impacting the rest of the database.

Last thing, I found it odd that even with DBMS_SQL_TRANSLATOR we can, apparently, remove the hint BUT it does not work at the actual execution.
I copy part of my test: we can see that
 select / * + index (test_hint idx_test_name) * / * from test_hint where name = 'DUPONT'
is rewritten in
    select * from test_hint where name = 'DUPONT'
but the execution failed.

SQL> DECLARE
        translated_text CLOB;
    BEGIN
        DBMS_SQL_TRANSLATOR.TRANSLATE_SQL (
        sql_text => 'select / * + index (test_hint idx_test_name) * / * from test_hint where name =' 'DUPONT' '',
        translated_text => translated_text);
        DBMS_OUTPUT.PUT_LINE (translated_text);
    END;
    /
    select * from test_hint where name = 'DUPONT'


Like you told me, I created a thread to see if it is a good idea to enhance these packages : https://community.oracle.com/ideas/26208

Good day and thank you again for all your help,

David

Chris Saxon
October 28, 2019 - 5:21 pm UTC

Thanks!

make a trigger AFTER LOGON and hope to find via SYS_CONTEXT AND USERENV

Yep, you can do that. This also allows you to see v$session attributes such as program, module, etc. which enable you to narrow down who you set the parameter for.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.