Injecter un hint dans du SQL - SQL PATCH

Hello,
Aujourd'hui on va faire du patch de sql ;) Un petit case pour débuter.
 

SQL>
SQL> create table lamidba as select * from dba_objects;

Table created.

SQL>

Histoire de... rapidement un calcul des stats sur la table

SQL>
SQL> exec dbms_stats.gather_table_stats (USER,'LAMIDBA');

PL/SQL procedure successfully completed.

SQL>

Et maintenant j'identifie une requête applicative pas bien compliqué qui fait un select * from lamidba

Rien de bien anormal, nous avons un FULL SCAN.
Cependant pour cette requête, j'aimerai pouvoir paralléliser, sans pour autant toucher aux propriétés de la table. Idéalement, je pourrai positionner un hint de type /*+ parallel (lamidba,2)*/ dans la requête; Mais c'est la que j'ai un soucis. Je n'ai pas accès au code source de la requête, et donc je ne peux pas y mettre mon hint.
Que faire... Abandonner...  !! non ! Never GIve Up !
Oracle nous donne la possibilité de faire de l'injection de hint via le package dbms_sqldiag_internal...
La preuve par l'exemple !

SQL>
SQL>
SQL> connect  / as sysdba
Connected.
SQL>
SQL> variable x varchar2(100);
variable v_query clob;
variable v_hint  clob;

exec   :v_query := 'select * from lamidba';
exec   :v_hint  := 'PARALLEL(@"SEL$1" "LAMIDBA"@"SEL$1" 2)';
exec :x:=dbms_sqldiag_internal.i_create_patch(sql_text => :v_query,hint_text =>:v_hint,creator=>'LAO',name=> 'Hint_Parallel_2');SQL> SQL> SQL> SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>

PL/SQL procedure successfully completed.

SQL>

Nous pouvons vérifier que le patch a bien été crée & est activé.

SQL>
SQL> column name format a20
SQL> column status format a20
SQL>
SQL> select name,status from dba_sql_patches;

NAME                 STATUS
-------------------- --------------------
Hint_Parallel_2      ENABLED

SQL>

Je vais donc me reconnecter et relancer ma requête.

Magique ! Notre requête est bien executé avec un parallélisme de deux
D'ailleurs en dessous de notre plan d’exécution, on a bien l'information que notre patche a été utilisé.

Note
-----
   - Degree of Parallelism is 2 because of table property
   - SQL patch "Hint_Parallel_2" used for this statement

Il ne vous aura pas échappé que pour injecter le hint j'ai utilisé la syntaxe PARALLEL(@"SEL$1" "LAMIDBA"@"SEL$1" 2) et non parallel (lami,2)... On en parle bientôt dans un nouvel article..
Avant de quitter, on supprime notre patch.

SQL>
SQL>
SQL> exec sys.dbms_sqldiag.drop_sql_patch('Hint_Parallel_2');
PL/SQL procedure successfully completed.
SQL>

Remarque 1: Pour injecter le hint, j'ai passé en paramètre le sql_text, cependant depuis la 12.2 il est possible de passer le sql_id (plus pratique pour les grosses requêtes).

Remarque 2: A noter également que pour une fois, je ne vais pas terminer en disant que c'est pour l'Enterprise... Et oui... sql patch, c'est aussi pour la standard Edition !!!(en 12c).

Remarque 3: En 12c, on peut utiliser dbms_sqldiag.create_sql_patch

Par ailleurs, il y a des modifications par rapport aux versions précédentes; Je vous invite a en lire un peu plus sur le blog de Jonathan Lewis:
https://jonathanlewis.wordpress.com/2017/06/12/dbms_sqldiag/

Enjoy !