Impossible de supprimer un hint avec DBMS_ADVANCED_REWRITE et DBMS_SQL_TRANSLATOR - Canno't delete a hint with PL/SQL packages
Introduction
Les deux packages DBMS_ADVANCED_REWRITE et DBMS_SQL_TRANSLATOR sont fantastiques. Ils permettent de remplacer des ordres SQL par d'autres, soit dans le cadre d'une migration d'un SGBD vers un autre, soit pour rendre un ordre plus performant. Malheureusement ils ont tous les deux une grosse limitation : ils ne permettent pas d'enlever les hints d'un SELECT.
Liens vers mes autres articles sur ces deux packages :
- Remplacer un ordre SQL par un autre avec DBMS_SQL_TRANSLATOR
- DBMS_ADVANCED_REWRITE : intercepter et remplacer un ordre SQL
Points d'attention
N/A.
Base de tests
Une base Oracle 18c.
Exemples
============================================================================================
Environnement de test
============================================================================================
Table de test
Ma table fait 10 000 lignes dont 9 900 avec le nom DUPONT et 100 avec le nom 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
Création d'un index et collecte des 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);
SELECTs de test
Si je fais une recherche avec NAME = 'MARTIN', Oracle utilise l'index : OK car il y a seulement 100 lignes sur 10 000 soit une sélectivité très forte.
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')
Si je fais un SELECT avec NAME = 'DUPONT', Oracle fait un Full Table Scan : OK car il y a 9 900 lignes sur 10 000 soit une sélectivité très faible.
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')
Si j'utilise un index via un hint pour le SELECT avec NAME = 'DUPONT', il y a un problème car le coût explose, il passe de 9 à 72. Donc utiliser l'index pour cette recherche est une très mauvaise chose!
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')
============================================================================================
Utilisation des packages
============================================================================================
Dans le cas où justement un SELECT avec ce hint s'exécute sur ma base MAIS que ce SELECT est généré par un logiciel tiers sur lequel je n'ai pas la main, comment l'enlever? J'avais pensé aux deux packages DBMS_ADVANCED_REWRITE et DBMS_SQL_TRANSLATOR mais, manque de chance, ils ne fonctionnent pas dans ce cas précis : ils n'agisent pas sur les hints et c'est cela qu'on va voir.
Test avec DBMS_SQL_TRANSLATOR
On crée un profile de translation.
SQL> EXEC DBMS_SQL_TRANSLATOR.CREATE_PROFILE (profile_name =>'translate_hint');
On enregistre quel ordre SQL doit remplacer tel autre ordre : c'est la translation proprement dite.
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);
Et on indique bien dans cette session quel est le profil de translation actif.
SQL> alter session set sql_translation_profile = translate_hint;
Si on teste via la procédure DBMS_SQL_TRANSLATOR.TRANSLATE_SQL, à priori c'est ok, le hint est enlevé dans l'ordre que j'affiche avec DBMS_OUTPUT.PUT_LINE.
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'
On déclenche l'évènement 10601 pour forcer Oracle à croire que SQL*Plus est une application non Oracle. De la sorte, Oracle va déclencher le module SQL Translator quand un ordre SQL est saisi sous SQL*Plus.
SQL> alter session set events = '10601 trace name context forever, level 32';
SQL> set autotrace traceonly
On fait le test : zut, le hint est utilisé... Zut de zut!
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')
Ce que je veux c'est cela : un FTS quand on fait une recherche sur DUPONT car on a vu que c'rétait plus performant que si on utilise un index (le coût passe de 9 à 72, rappelez-vous!).
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')
Preuve est faite que DBMS_SQL_TRANSLATOR ne peut pas suppprimer un hint d'un SELECT.
Test avec DBMS_ADVANCED_REWRITE
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FORCE;
Aïe, impossible! Oracle dit que les deux ordres sont identiques.
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, je change le deuxième ordre en ajoutant ' and 1=1' pour forcer Oracle à réécrire l'ordre sans pour autant modifier sa logique.
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
Et maintenant, si j'utilise le hint... Echec à nouveau, l'index est encore utilisé!
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')
Pourtant, si j'exécute l'ordre réécrit, le plan d'exécution est 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')
Preuve est faite que DBMS_ADVANCED_REWRITE ne peut pas suppprimer un hint d'un SELECT.
============================================================================================
Conclusion
============================================================================================
Ces deux packages fonctionnent bien pourtant (voir les deux liens du début de l'article) MAIS dans le cas précis des hints, c'est un échec ! J'ai posé la question à Oracle via AskTom et voici leur réponse : ces deux packages n'ont pas été développés pour enlever les hints ou même réécrire des ordres venant d'Oracle mais pour les migrations d'applications d'un SGBD vers un autre; le fait de réécrire des ordres Oracle vers Oracle est un peu une utilisation "abusive" de ces packages.
On me propose la solution avec le paramètre optimizer_ignore_hints à TRUE mais cela me pose plusieurs problèmes que je détaille dans le lien suivant : https://asktom.oracle.com/pls/apex/asktom.search?tag=remove-a-hint-with-dbms-advanced-rewrite-and-dbms-sql-translator-failed
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)