SQL Patch : comment inhiber un hint d'un ordre SQL - SQL Patch : how to inhibit a hint of an SQL statement

21 février 2021

SQL Patch : comment inhiber un hint d'un ordre SQL - SQL Patch : how to inhibit a hint of an SQL statement

Introduction
Une des pires situations à laquelle vous serez confronté en tant que DBA Oracle est de tuner un SELECT ayant des hints inappropriés et sur lequel vous n'avez pas la main; c'est le cas d'une application développée par un éditeur de logiciel disparu par exemple. Je ne dis pas qu'il faut supprimer les hints de tous les SELECTs mais, mal utilisés, ils peuvent augmenter de façon exponentielle les temps d'exécution des requêtes et donc ils faut pouvoir inhiber ceux-là.

Une solution est d'associer à ce SELECT un SQL Patch avec le package DBMS_SQLDIAG. Ce patch utilisera le hint IGNORE_OPTIM_EMBEDDED_HINTS disant au CBO de ne pas tenir compte des hints inclus (EMBEDDED) du SELECT.

Attention, le package DBMS_SQLDIAG sert à la base à corriger un ordre SQL qui échoue pour cause de bug Oracle (https://docs.oracle.com/database/121/ARPLS/d_sqldiag.htm#ARPLS68286). Néanmoins on peut aussi l'utiliser de façon détournée pour insérer un hint dans une requête pour laquelle nous n'avons pas accès au code source.

A noter que ce package, SYS.DBMS_SQLDIAG et sa fonction create_sql_patch, sont documentés depuis la 12.2. Si vous êtes en 11g, vous devrez utiliser le package non documenté, SYS.DBMS_SQLDIAG_INTERNAL et sa fonction i_create_patch, pour utiliser la même fonctionnalité.

Points d'attention
N/A.

Base de tests
Une base Oracle 12c multi-tenants.

Exemples
============================================================================================
18c : le paramètre optimizer_ignore_hints
============================================================================================
Depuis la 18c un nouveau paramètre lié à l'optimiseur est apparu : optimizer_ignore_hints. Mis à TRUE, il ordonne au CBO de ne pas tenir compte des hints des requêtes. Néanmoins cette solution n'est pas satisfaisante car un ALTER SYSTEM ou ALTER SESSION sur ce paramètre aura des impacts sur les autres ordres SQL utilisant des hints. En tant que DBA, votre objectif est de faire de la microchirurgie : si c'est un ordre SQL et un seul qui pose problème, il faut corriger celui-ci et UNIQUEMENT celui-ci sans impacter les autres ordres, du moins si c'est possible.

Ma base n'est pas une 18c : c'est une 12.2.0.1 alors que la 18c est une 12.2.0.2 donc impossible d'utiliser cette fonctionnalité.
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

On voit que le paramètre optimizer_ignore_hints n'existe pas dans cette version.
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_adaptive_statistics boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 12.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_inmemory_aware boolean TRUE
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

============================================================================================
Base de test
============================================================================================
Je crée une table de 30 000 éléments avec une primary key (Oracle génèrera un index sur cette colonne) et je génère les stats.
SQL> create table ZZTEST(id number(8,2), name varchar2(50), first_name varchar2(50), CONSTRAINT zztest_pk PRIMARY KEY (id));
Table created.

SQL> INSERT INTO ZZTEST SELECT ROWNUM, 'PIERRE' || to_char(rownum), 'SMITH' || to_char(rownum) FROM dual CONNECT BY LEVEL <= 30000;
30000 rows created.

SQL> commit;
Commit complete.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS( 'HR');
PL/SQL procedure successfully completed.

Le CBO génère un FTS (Full Table Scan) suite à mon SELECT.
SQL> select * from zztest where id <> 1;
...
29999 rows selected.

SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 59pf44tyjgcqj, child number 0
-------------------------------------
select * from zztest where id <> 1
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| |
|* 1 | TABLE ACCESS FULL| ZZTEST | 29999 | 820K| 68 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<>1)
18 rows selected.

Maintenant j'utilise le hint INDEX pour changer le plan d'exécution. Il est bien pris en compte, le "TABLE ACCESS FULL" est remplacé par "INDEX FULL SCAN"
SQL> select index_name from user_indexes where table_name = 'ZZTEST';
INDEX_NAME
--------------------------------------------------------------------
ZZTEST_PK

SQL> select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1;
...
29999 rows selected.

SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 4ubxmw59gdrxc, child number 0
-------------------------------------
select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1
Plan hash value: 2617679375
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 189 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST | 29999 | 820K| 189 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | ZZTEST_PK | 29999 | | 57 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<>1)
19 rows selected.

============================================================================================
La solution SQL Patch
============================================================================================
Et c'est là que nous avons un problème! Le développeur a forcé dans le SELECT l'utilisation d'un index via un hint et, comme nous récupérons quasiment 100% de la table, un FTS est plus performant. Mais comment inhiber le hint INDEX du SELECT? Ce sera fait via un SQL Patch et le hint IGNORE_OPTIM_EMBEDDED_HINTS.

Pour créer le SQL Patch, se connecter avec un user qui a les bons droits, SYS par exemple. Il suffit de le créer, pas besoin de l'activer. Les paramètres a renseigner sont simples : le sql id, le hint à ajouter, un nom.
SQL> connect SYS@orcl as sysdba
Enter password:
Connected.

SQL> DECLARE
v_patch_name VARCHAR2(32767);
BEGIN
v_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
sql_id => '4ubxmw59gdrxc',
hint_text => 'IGNORE_OPTIM_EMBEDDED_HINTS',
name => 'sql_patch_zztest_ignore_hints');
END;
/
PL/SQL procedure successfully completed.

Je me reconnecte comme un user lambda.
SQL> connect HR
Enter password:
Connected.

Je relance le SELECT et, magique, même si on a un hint dans le SELECT, celui-ci n'est plus utilisé, le CBO a préféré faire un Full Table Scan (appelé "TABLE ACCESS FULL" ci-dessous). Et lisez bien la note tout en bas : un SQL Patch a été utilisé pour ce SELECT et c'est lui qui a inhibé le hint du SELECT et ce via via un autre hint. C'est amusant, on utilise un hint pour dire au CBO de ne pas utiliser les hints :-) Mais, si on réfléchit plus, on utilise une sorte de méta hint pour inhiber les embedded hints : ces hints ne sont pas au même niveau pour le CBO.
SQL> select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1;
...
29999 rows selected.

SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 4ubxmw59gdrxc, child number 0
-------------------------------------
select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| |
|* 1 | TABLE ACCESS FULL| ZZTEST | 29999 | 820K| 68 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<>1)

Note
-----
- SQL patch "sql_patch_zztest_ignore_hints" used for this statement
22 rows selected.

ATTENTION : ce patch fonctionne sur un SQL_ID précis; si on change l'ordre SQL, même s'il donne le même résultat, le patch ne s'applique plus. Par exemple j'ajoute la clause AND 1=1 dans le WHERE.
SQL> select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1 AND 1=1;
...
29999 rows selected.

SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 1su9w0y182rud, child number 0
-------------------------------------
select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1 AND
1=1
Plan hash value: 2617679375
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 189 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST | 29999 | 820K| 189 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | ZZTEST_PK | 29999 | | 57 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<>1)
20 rows selected.

Si on regarde dans la vue DBA_SQL_PATCHES : rien de passionnant.
SQL> select * from DBA_SQL_PATCHES;
NAME CATEGORY SIGNATURE SQL_TEXT CREATED LAST_MODIFIED DESCRIPTIO STATUS FOR TASK_ID TASK_EXEC_ TASK_OBJ_ID TASK_FND_ID TASK_REC_ID
---------------------------------------- ---------- ---------- -
sql_patch_zztest_ignore_hints DEFAULT 1.3882E+19 select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1 21-FEB-21 05.31.17.000000 AM 21-FEB-21 05.31.17.000000 AM ENABLED NO

Je droppe ce SQL patch.
SQL> connect SYS@orcl as sysdba
Enter password:
Connected.

SQL> exec DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'sql_patch_zztest_ignore_hints');
PL/SQL procedure successfully completed.

SQL> connect HR@orcl
Enter password:
Connected.

Le SQL Patch ayant été supprimé, le hint interne au SELECT est à nouveau pris en compte par l'optimiseur et il n'y a plus de note.
SQL> select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1;
...
29999 rows selected.

SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 4ubxmw59gdrxc, child number 0
-------------------------------------
select /*+ INDEX(zztest zztest_pk) */ * from zztest where id <> 1
Plan hash value: 2617679375
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 189 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST | 29999 | 820K| 189 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | ZZTEST_PK | 29999 | | 57 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<>1)
19 rows selected.