Les histogrammes (2): Frequency

Dans l’article précédent nous avons vu que la cardinalité pour la colonne C_FREQ était mal estimée par le CBO car il lui manquait l’information sur la distribution des valeurs de cette colonne :

select * from T1 where C_FREQ=3; 
 
------------------------------------------------------------------------------------ 
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | 
------------------------------------------------------------------------------------ 
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 35 | 
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4913 | 10 |00:00:00.01 | 35 | 
------------------------------------------------------------------------------------

Cette information peut être apportée en calculant un histogramme sur la colonne. Jusqu’à la version 11G on avait 2 types d’histogrammes : FREQUENCY et HEIGHT-BALANCED. Les histogrammes de type FREQUENCY vont stocker le nombre de lignes (la fréquence) de chaque valeur ce qui permet d’obtenir des cardinalités très fiables. Cependant ces histogrammes sont limités à des colonnes ayant moins de 254 valeurs distinctes.

Voici comment on calcule des statistiques avec histogrammes pour la colonne C_FREQ :

exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns C_FREQ size 3');

Le chiffre 3 indique que je souhaite utiliser 3 buckets puisqu’on a que 3 valeurs distinctes. Même si j'utilisais une valeur supérieure Oracle ne prendrait en compte que 3 buckets pour cette colonne. 

On peut vérifier qu’un histogramme de type FREQUENCY a bien été calculé en questionnant la table USER_TAB_COL_STATISTICS :

select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram  
from user_tab_col_statistics  
where table_name='T1' and column_name='C_FREQ'; 
 
COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM 
------------------------------ ------------ ---------- ---------- ----------- ----------- ---------- 
C_FREQ                                    3 ,000033924          0           3       14739 FREQUENCY

On peut également visualiser le contenu de l’histogramme en requêtant la vue USER_TAB_HISTOGRAMS : 

select endpoint_value as column_value, 
endpoint_number as cummulative_frequency, 
endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency 
from user_tab_histograms 
where table_name = 'T1' and column_name = 'C_FREQ'; 
 
COLUMN_VALUE CUMMULATIVE_FREQUENCY  FREQUENCY 
------------ --------------------- ---------- 
           1                 13257      13257 
           2                 14729       1472 
           3                 14739         10

La colonne ENDPOINT_VALUE correspond à la valeur de la colonne et la colonne ENDPOINT_NUMBER correspond au nombre cumulé de lignes. Ainsi, pour obtenir le nombre de lignes (la fréquence) pour la valeur 2 il suffit de soustraire au ENDPOINT_NUMBER de la valeur 2 le ENDPOINT_NUMBER de la valeur 1 (14729-13257=1472), d’où l’utilisation de la fonction LAG dans la requête ci-dessus.
On avait vu dans l’article précédent que sans histogrammes le CBO n’arrivait pas à estimer la cardinalité pour C_FREQ=3:

select * from T1 where C_FREQ=3; 
 
------------------------------------------------------------------------------------ 
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |      35 | 
|*  1 |  TABLE ACCESS FULL| T1   |      1 |   4913 |     10 |00:00:00.01 |      35 | 
------------------------------------------------------------------------------------

Voyons donc maintenant ce qu’estime le CBO avec l’histogramme FREQUENCY en place pour cette colonne :

alter system flush shared_pool; 
select * from T1 where C_FREQ=3; 
 
-------------------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
-------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT            |          |      1 |        |     10 |00:00:00.01 |       5 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |     10 |     10 |00:00:00.01 |       5 | 
|*  2 |   INDEX RANGE SCAN          | IDX_FREQ |      1 |     10 |     10 |00:00:00.01 |       3 | 
--------------------------------------------------------------------------------------------------

L’estimation match cette fois parfaitement la réalité (E-Rows=A-Rows=10). Du coup, le CBO opte pour un accès indexé et le nombre de logical reads est divisé par 5.

Cet exemple montre bien qu’un histogramme Frequency permet d’obtenir une cardinalité égale ou proche de la réalité. Cette justesse au niveau de l’estimation vient du fait que pour les histogrammes FREQUENCY il existe autant de buckets que de valeurs distinctes, ainsi, toutes les valeurs présentes dans la table sont populaires à condition bien sûr qu’un calcul de statistiques ait eu lieu au moment où cette distribution non uniforme des données était présente. En effet, imaginons une nouvelle ligne insérée dans la table T1 avec C_FREQ = 4, la valeur 4 n’étant pas présente dans l’histogramme FREQUENCY le CBO ne peut estimer correctement la cardinalité pour cette valeur. Il est donc primordial d’avoir un calcul de statistiques qui tourne toujours au juste moment.

Dans le prochain article nous verrons comment Oracle calcule les histogrammes HEIGHT-BALANCED pour répondre aux problématiques des colonnes de plus 254 valeurs distinctes.

Les histogrammes (1): Introduction

J’ai remarqué que le sujet des histogrammes était souvent très mal assimilé même chez les DBAs et les développeurs les plus expérimentés alors qu’ils constituent un concept fondamental en matière de Tuning SQL.

Je vais tenter à travers une série d’articles de vous présenter ce qu’il est nécessaire de savoir sur le sujet : à quoi servent les histogrammes ? Comment on les calcule ? Quels sont les différents types d’histogrammes ? Comment sont-ils stockés en base ? Quels sont leurs avantages et inconvénients ?

Dans un premier temps je vais me limiter à la version 11g puis nous verrons dans un futur article que la 12c a apporté pas mal de changements. 

 Exemple:

create table T1 as  
select rownum id, decode(mod(rownum,10),0,2,1) c_freq, nvl(blocks,999) c_hb  
from dba_tables ; 
 
update T1 set c_freq=3 where rownum<=10; 
commit; 
 
create index idx_freq on T1(C_FREQ); 
create index idx_hb on T1(C_HB); 
 
select c_freq,count(*) from T1 group by c_freq order by 2 desc; 
 
    C_FREQ   COUNT(*) 
---------- ---------- 
         1      13257 
         2       1472 
         3         10 
 
          
select c_hb,count(*) from T1 group by c_hb order by 2 desc; 
 
      C_HB   COUNT(*) 
---------- ---------- 
       999       5710 
         0       4601 
         4       1853 
         5        237 
         8        195 
        19        169 
         1        152 
        16         84 
        13         70 
         6         47 
        32         34 
         7         29 
        20         26 
       256         24 
         9         23 
     .......................... 
      5046          1 
       
829 rows selected.

Notre exemple est basé sur une table de 14 739 lignes ayant 3 colonnes : une colonne ID qui est unique, une colonne C_FREQ possédant 3 valeurs distinctes et une colonne C_HB contenant 829 valeurs distinctes. On a également un index sur la colonne C_FREQ et un autre sur la colonne C_HB. Les deux requêtes de comptage mettent en évidence que la distribution des données des deux colonnes n’est pas répartie de manière uniforme, c’est-à-dire que certaines valeurs se répètent beaucoup plus que d’autres. Ainsi, selon la popularité de la valeur de la colonne utilisée dans la requête on utilisera soit un accès indexé soit un Full Scan de la table.

 

Estimations du CBO sans histogrammes

Dans un premier temps on collecte des statistiques pour la table T1 et ses colonnes mais sans calculer d’histogrammes :

exec dbms_stats.gather_table_stats (user, 'T1', method_opt=>'for all columns size 1'); 
 
select num_rows from user_tables where table_name='T1'; 
 
  NUM_ROWS 
---------- 
     14739 
 
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram  
from user_tab_col_statistics  
where table_name='T1' and column_name='C_FREQ'; 
 
COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM 
------------------------------ ------------ ---------- ---------- ----------- ----------- ----------- 
C_FREQ                                    3 ,333333333          0           1       14739 NONE

Le fait d’avoir écrit « FOR ALL COLUMNS SIZE 1 » pour le paramètre METHOD_OPT indique qu’on veut calculer des statistiques pour toutes les colonnes mais sans histogrammes (c’est ce que signifie le SIZE 1). La colonne HISTOGRAM de la vue USER_TAB_COL_STATISTICS est à NONE ce qui confirme bien qu’aucun histogramme n’a été calculé pour la colonne C_FREQ de la table T1.

Voyons maintenant ce qu’estime le CBO lorsqu’on requête les lignes de la table T1 avec C_FREQ = 3 :

select * from T1 where C_FREQ=3; 
 
------------------------------------------------------------------------------------ 
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |      35 | 
|*  1 |  TABLE ACCESS FULL| T1   |      1 |   4913 |     10 |00:00:00.01 |      35 | 
------------------------------------------------------------------------------------

En comparant les colonnes E-Rows et A-Rows du plan on s’aperçoit que le CBO s'est trompé puisqu’il a estimé une cardinalité de 4913 lignes au lieu de 10 réellement retournées. Cette erreur (qui n’en est pas vraiment une) s’explique par le fait qu’en l'absence d'histogrammes le CBO suppose que les lignes sont uniformément réparties dans la table et il applique donc la formule suivante pour estimer la cardinalité retournée:

NUM_ROWS/NUM_DISTINCT = 14739/3 = 4913

A cause du nombre important de lignes estimées, le CBO a opté pour un FULL TABLE SCAN alors qu’un accès via l’index aurait été plus efficace.

Voyons maintenant ce qu’estime le CBO lorsqu’on filtre sur la colonne C_HB qui contient un nombre bien plus important de valeurs distinctes que la colonne C_FREQ :

select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram  
from user_tab_col_statistics  
where table_name='T1' and column_name='C_HB'; 
 
COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM 
------------------------------ ------------ ---------- ---------- ----------- ----------- ---------- 
C_HB                                    829 ,001206273          0           1       14739 NONE 
 
select * from T1 where C_HB=999; 
 
------------------------------------------------------------------------------------------------ 
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT            |        |      1 |        |   5710 |00:00:00.01 |     806 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |      1 |     18 |   5710 |00:00:00.01 |     806 | 
|*  2 |   INDEX RANGE SCAN          | IDX_HB |      1 |     18 |   5710 |00:00:00.01 |     394 | 
------------------------------------------------------------------------------------------------

Là aussi le nombre de lignes estimées est loin de la réalité (18 vs 5710). Le nombre 18 a été obtenu en divisant le nombre de lignes de la table par le nombre de valeurs distinctes de la colonne (E-Rows = 14739/829 = 17.79 = 18). Du coup le CBO a choisi un accès indexé alors qu’un Full Table Scan aurait sûrement été plus judicieux.

On se rend donc compte avec ces exemples que lorsque les données sont biaisées en termes de distribution de valeurs, les statistiques classiques ne suffisent plus à l’optimiseur pour estimer des cardinalités proches de la réalité. C’est là que les histogrammes rentrent en jeu.

Nous allons voir dans les prochains articles comment calculer des histogrammes sur les deux colonnes en question et analyser ce que donnent les estimations du CBO grâce à ces nouvelles données.

Calcul de statistiques et mode concurrent

Beaucoup d’entre vous utilisent surement le paramètre DEGREE dans les procédures du package DBMS_STATS afin de paralléliser leur traitement de calcul de statistiques. Mais ce que beaucoup ignorent c’est que le parallélisme ne se fait qu’au niveau du segment. Il ne peut y avoir un calcul de statistiques sur plusieurs segments à la fois. Donc, le parallélisme appliqué au calcul de statistiques n’est intéressant que lorsqu’on a affaire à de très grosses tables. Heureusement, depuis la 11.2.0.2 il est possible d’avoir un calcul de statistiques pouvant s’effectuer sur plusieurs tables et partitions en même temps, c’est ce qu’on appelle le mode CONCURRENT. Ce mode permet d’accélérer formidablement le calcul de statistiques en profitant pleinement des ressources de la machine d’autant plus qu’on peut le combiner avec le mode parallel.

J’ai récemment pu améliorer le temps de calcul de statistiques d’une des bases de mon client grâce au mode concurrent. Pour se rendre compte de l’amélioration apportée voyons d’abord ce que donne le calcul de statistiques effectué sans le mode concurrent sur un schéma de 800GB et dont les plus grosses tables font plusieurs dizaine de GB.

Test du calcul de stats séquentiel :

     
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where owner='SCHEMA1';

SUM(BYTES)/1024/1024/1024

-------------------------

               801,098389

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 12:55:42.16

Le calcul de statistiques sur le schéma a mis un peu de moins de 13 heures.

Pendant que le calcul de statistiques tournait j’ai lancé plusieurs requêtes à des moments différents pour voir les sessions actives sur ma base et on constate bien que le calcul de statistiques se faisait en mode séquentiel car seule une session était en train de travailler:

SQL> @sql_cur_stmt

  SID    SERIAL# PROG       ADDRESS          HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME EVENT                SQL_TEXT

----- ---------- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------

    2      45373 sqlplus.ex 0000002D356943A8 3121805777 85j1xbkx15yfj      0       226853730          1      692.32 db file sequential r select /*+  no_parallel_index(t, "HISCRN1

Test du calcul de stats en parallel :

J’ai ensuite testé le calcul de stats en mode parallel avec un DEGREE de 16

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', degree=> 16, method_opt => 'for all columns size 1');

Elapsed: 11:15:20.68

Cette fois le calcul de statistiques a mis un peu plus de 11 heures. C’est mieux que le mode séquentiel mais ça reste néanmoins toujours trop long. Pendant que le calcul de statistiques en mode parallèle tournait j’ai également lancé quelques requêtes pour voir ce que j’avais dans mes sessions actives :

   SID    SERIAL# PROG       ADDRESS          HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME EVENT                SQL_TEXT

----- ---------- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------

    5      12814 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

 1163      42516 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  235      40059 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  237      46728 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  314      18466 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  391      30356 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  468      20257 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  540      28533 sqlplus.ex 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 PX Deq: Execute Repl /* SQL Analyze(0) */ select /*+  full(t)

  547      15123 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  623      29688 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  702      62908 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  774      31336 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  852      17484 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  931       1917 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

 1005      45861 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

 1081      37925 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 db file scattered re /* SQL Analyze(0) */ select /*+  full(t)

  163      26835 oracle@eqd 0000002DDAA90A90 3664547584 9k9zph3d6t3s0      0      2617663258          1       50.30 ON CPU               /* SQL Analyze(0) */ select /*+  full(t)

On constate bien que le calcul de statistiques se faisait en parallèle puisque j’ai bien 16 sessions correspondant à mes process slaves et une session correspondant à ma session principale. On s’aperçoit également que bien que j’ai plusieurs sessions en parallèles elles travaillent toutes sur un seul objet qui est la table T. Cela rejoint donc ce que je disais en préambule de mon article : le mode parallel ne peut s’effectuer qu’au niveau segment et non pas sur plusieurs tables à la fois.

Test du calcul de stats en mode concurrent :

 

Pour utiliser le mode CONCURRENT il suffit de positionner le paramètre global CONCURRENT qui est désactivé par défaut (même en 12c). Le nombre de sessions concurrentes va dépendre de la valeur du paramètre job_queue_processes.

Voyons ce que le mode CONCURRENT donne pour mon schéma SCHEMA1 :

exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', method_opt => 'for all columns size 1');

ORA-20000: Unable to gather statistics concurrently: Resource Manager is not enabled.

ORA-06512: at "SYS.DBMS_STATS", line 35980

ORA-06512: at line 1

Le message d’erreur m’indique qu’il n’y a pas de Resource Plan de défini, or pour que le calcul de statistiques se fasse en mode concurrent il faut que Ressource Manager puisse gérer les ressources consommées par chaque job. On peut soit définir son propre Resource Plan soit utiliser celui fourni par Oracle qui est le DEFAULT_PLAN :

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;

exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.
 
Elapsed: 02:25:16.99

Cette fois ci le calcul de stats a pris 2h25 au lieu de 12h55 en mode séquentiel et 11h15 en mode parallèle. Le gain est énorme.

Voici ce qu’on pouvait voir dans V$SESSION pendant que les jobs concurrents tournaient :

SQL> @sql_cur_stmt
 

  SID    SERIAL# PROG       ADDRESS          HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME EVENT                SQL_TEXT

----- ---------- ---------- ---------------- ---------- ------------- ------ --------------- ---------- ----------- -------------------- -----------------------------------------

  623       9359 oracle@eqd 0000002D32EF62F8 2948485349 3g5vrh6rvwn75      0       968244646          1       12.76 db file scattered re /* SQL Analyze(1) */ select /*+  full(t)

 1085      50128 oracle@eqd 0000002D3518A618 4209280689 4abxz8gxf91pj      1      1515091901          1      113.57 db file sequential r select /*+  no_parallel_index(t, "IHSPOS1

  701       8020 oracle@eqd 0000002CD975A2D8 3982881736 4qj5dp7qqbwy8      1      2053917943          1    1,079.45 db file sequential r select /*+  no_parallel_index(t, "IAVOPEI

   86      30708 oracle@eqd 0000002D148BDE88 1780980331 59p1yadp2g6mb      0               0         36         .02 Streams AQ: waiting  call DBMS_AQADM_SYS.REGISTER_DRIVER (  )

    2      45373 sqlplus.ex 0000002DFC7337D0 3466778119 8kruwyz7a5ph7      0               0          2   23,393.51 Streams AQ: waiting  BEGIN dbms_stats.gather_schema_stats(ownn

  930      47139 oracle@eqd 0000002E0DE75D10 2559624494 96u7bryc91j9f      1       614356722          1    1,905.43 db file sequential r select /*+  no_parallel_index(t, "HISMVC1

  548      12639 oracle@eqd 0000002CD8900280 1387000160 auv55cp9arwb0      1       981273022          1       65.32 db file sequential r select /*+  no_parallel_index(t, "ITPCRO9

  775      22196 oracle@eqd 0000002C7FC02950 2839555010 d92sd5qnn0ay2      1      3067632440          1       42.85 db file sequential r select /*+  no_parallel_index(t, "RTPSPR1

  773      32465 oracle@eqd 0000002CD3983ED0 1227027996 dh8dxq14k5xhw      0      3635621864          1      294.04 db file scattered re /* SQL Analyze(1) */ select /*+  full(t)

  317      19697 oracle@eqd 0000002C7E6227C0 2780047690 dtkjh72kv8aaa      0      3588248285          1         .01 db file scattered re /* SQL Analyze(1) */ select /*+  full(t)

  469      42289 oracle@eqd 0000002CDB8A0F60 1695986908 fzp1yupkjdd6w      1        43487272          1      862.32 db file sequential r select /*+  no_parallel_index(t, "AVICNF8

Comme le paramètre job_queue_processes est positionné à 10 j’ai uniquement 10 sessions concurrentes, et on s’aperçoit que contrairement au calcul de statistiques en parallèle chaque session travaille sur des tables différentes.

On peut penser qu’avec un paramètre job_queue_processes supérieur à 10 il aurait été possible d’obtenir un meilleur résultat.

Il est également possible de combiner le mode concurrent avec le calcul de statistiques en parallèle. Je l’ai testé pour mon schéma mais le gain n’est que de 2 minutes :

-- test calcul de stats concurrent+parallel

exec DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

alter system set parallel_adaptive_multi_user=false;

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA1', degree=> 16, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 02:23:41.27

Direct path reads et "enq KO – fast object checkpoint"

Bien que cet event commence par le mot « enq » il ne s’agit pas d’un enqueue au sens attente sur un lock. Je dis ça car récemment un développeur est venu me voir pour se plaindre de locks au niveau de sa base car il avait vu que certaines de ses requêtes étaient en attente sur cet event.

En réalité, si vous rencontrez cet event c’est que le moteur Oracle a choisi d’effectuer son full scan en utilisant du Direct Path Read et donc de bypasser le buffer cache en lisant les blocks directement depuis les datafiles. Cet event se déclenche si, parmi les blocs à lire, certains sont dirty (blocs modifiés dans le buffer cache mais pas encore envoyés par le processus DB Writer dans le disque). Pour avoir une lecture consistante un checkpoint doit être effectué pour mettre à jour les dirty blocs de la table à lire au niveau du disque. Pendant cette opération le process server en charge du direct path read attend sur l’event « enq KO – fast object checkpoint ».

Un exemple valant mieux qu’un long discours je me suis amusé à faire le petit test suivant sur ma base 12.1.0.2:

SQL>  create table T1 as select * from dba_objects;

Table created.

SQL> update T1 set created=sysdate where owner<>'SYS';

784976 rows updated.

Dans une session 1 j’ai créé une table T1 puis j’ai lancé un update de 78976 lignes pour avoir des dirty blocs dans le buffer cache.

La requête ci-dessous nous indique qu’on a 16 925 blocks qui sont dirty :

SQL> SELECT count(*)
  2  FROM v$bh b, dba_objects o
  3  WHERE b.objd = o.data_object_id and o.object_name='T1' and dirty='Y';
 

  COUNT(*)
----------
     16925

Dans une autre session je fais un count de la table en forçant le moteur SQL à choisir un Direct Path read en jouant sur le paramètre caché _serial_direct_read :

-- session 2
SQL> alter session set "_serial_direct_read"=always; 

Session altered. 

SQL> @10046.sql

SQL> set echo on feed on

SQL> ALTER SESSION SET  max_dump_file_size = UNLIMITED; 

Session altered. 

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'ahmed_10046_sql_trace'; 

Session altered.

SQL> -- enables SQL trace at level 12 for the session executing it.

SQL> ALTER SESSION SET events '10046 trace name context forever, level 12'; 

Session altered.

SQL> select count(*) from T1;

  COUNT(*)
----------
     1465568  

SQL> @dis_10046.sql

SQL> set echo on feed on

SQL> -- disables SQL trace for the session executing it.

SQL> ALTER SESSION SET events '10046 trace name context off';

Session altered.

J’ai préalablement activé une trace 10046 level 12 pour capturer les wait event et voici un extrait de la trace :

WAIT #427376872: nam='enq: KO - fast object checkpoint' ela= 769931 name|mode=1263468550 2=65568 0=1 obj#=-1 tim=131292719765

WAIT #427376872: nam='Disk file operations I/O' ela= 10331 FileOperation=2 fileno=1 filetype=2 obj#=104369 tim=131292730309

WAIT #427376872: nam='direct path read' ela= 3831 file number=1 first dba=99529 block cnt=55 obj#=104369 tim=131292734495

WAIT #427376872: nam='direct path read' ela= 903 file number=1 first dba=102528 block cnt=72 obj#=104369 tim=131292737068

WAIT #427376872: nam='direct path read' ela= 2035 file number=1 first dba=102656 block cnt=128 obj#=104369 tim=131292741080

WAIT #427376872: nam='direct path read' ela= 716 file number=1 first dba=102784 block cnt=128 obj#=104369 tim=131292745168

Jetez un oeil à la première ligne: avant de voir les wait events correspondant à l’accès disque en direct path on voit que le process server a attendu 0.76 seconde sur l’event « enq: KO - fast object checkpoint » car il a fallu que le process DB writer flush les dirty blocks de la table sur disque avant qu'il puisse faire ses direct I/Os.

D’ailleurs lorsque j'interroge de nouveau la vue v$bh je constate qu’il n’existe plus de dirty blocks en mémoire pour la table T1 :

SQL> SELECT count(*)
  2  FROM v$bh b, dba_objects o
  3  WHERE b.objd = o.data_object_id and o.object_name='T1' and dirty='Y'; 

  COUNT(*)
----------
         0

Par contre on a toujours des dirty blocks appartenant à d’autres segments que ma table T1 cela prouve que le checkpoint engendré par le direct path read n’est pas globale au buffer cache mais local aux blocs de la table requête (ce qui est plutôt rassurant pour la performance des requêtes en direct path reads, je pense notamment à l'exadata):

SQL> SELECT count(*)
  2  FROM v$bh b, dba_objects o
  3  WHERE b.objd = o.data_object_id and o.object_name<>'T1' and dirty='Y'; 

  COUNT(*)
----------
         37

Oracle 12c : ORA-65096 lors de la création d’un user

Si vous obtenez l’erreur ORA-65096 après une commande CREATE USER c’est que vous êtes sur une instance oracle 12c multinenant et que vous tentez de créer un user local dans le root container. Le multitenant de la 12c intègre une nouvelle différenciation entre les users dits "communs" et les users dits "locaux". Le local user est celui qui est créé sous une pluggable database (PDB) et dont les privilèges sont limités à cette PDB alors que le common user est celui créé sous le container root et dont les privilèges peuvent s’appliquer sur toutes les PDBs.

Voici un exemple pour bien comprendre :

Disons que j’ai besoin de créer un user AHMED dans ma base de données 12c multitenant. En 11g ou avec une base non-multitenant je n’ai qu’à me connecter en sys et exécuter une commande de type CREATE USER mais si j’agis de la même manière sur ma base multitenant voilà ce que j’obtiens : 

C:\Ahmed\Scripts\sqlplus>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Jeu. Juil. 23 09:21:41 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connecté :

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user

USER est "SYS"

SQL> create user ahmed identified by "ahmed#1" container=current;

create user ahmed identified by "ahmed#1" container=current

 *

ERROR at line 1:

ORA-65049: creation of local user or role is not allowed in CDB$ROOT
 

SQL> create user ahmed identified by "ahmed#1" container=all;

create user ahmed identified by "ahmed#1" container=all

 *

ERROR at line 1:

ORA-65096: invalid common user or role name

J’obtiens un message d’erreur qui me dit qu’il m’est impossible de créer un local user ou un role sous le container root. En effet, si j’affiche le nom du container je m’aperçois que je suis bien sous le ROOT :

SQL> show con_name
 

CON_NAME
------------------------------
CDB$ROOT

Si je souhaite créer un user pour une PDB en particulier alors je dois me connecter à cette PDB et créér un user qui sera local :

 SQL> alter session set container=PDB1;

Session altered. 


SQL> show con_name

CON_NAME
--------------------
PDB1


SQL> create user ahmed identified by "ahmed#1";

User created.

Cette fois-ci mon user AHMED a bien été créé.

Si j’avais voulu créé un common user c’est-à-dire un user se trouvant dans le container ROOT, j’aurais dû préfixer le nom du user par c## : 

SQL> show user

USER est "SYS"

SQL> sho con_name

CON_NAME
----------------------
CDB$ROOT

SQL> create user c##ahmed identified by "ahmed#1";

User created.

Disjunctive Subquery

Il y’a quelques semaines j’ai eu à analyser la requête suivante sur une base de production:

SELECT Entities.EntityId, 
  Entities.EntityName, 
  Entities.LegalName 
FROM Entities 
WHERE (Entities.EntityId IN 
  (SELECT Agreements.PrincipalId 
  FROM Agreements 
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3) 
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16)) 
  AND Agreements.AgreementGroupId            IS NULL) 
  AND Agreements.BusinessLine NOT            IN (4)) 
  ) 
OR Entities.EntityId IN 
  (SELECT Agreements.CounterpartyId 
  FROM Agreements 
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3) 
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16)) 
  AND Agreements.AgreementGroupId            IS NULL) 
  AND Agreements.BusinessLine NOT            IN (4))   )) ;

Cette requête s’exécutait en un peu moins de 21 minutes pour retourner 258 lignes. Quand on regarde la requête de plus près on se rend compte qu’elle contient un bloc principal accédant à la table ENTITIES et une double sous requête séparée par une clause OR.

Voici son plan d’exécution :

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |    258 |00:20:52.02 |     210M|
|*  1 |  FILTER            |            |      1 |        |    258 |00:20:52.02 |     210M|
|   2 |   TABLE ACCESS FULL| ENTITIES   |      1 |  64326 |  64368 |00:00:00.08 |    1400 |
|*  3 |   TABLE ACCESS FULL| AGREEMENTS |  64368 |      1 |     19 |00:10:22.58 |     105M|
|*  4 |   TABLE ACCESS FULL| AGREEMENTS |  64349 |      1 |    239 |00:10:28.89 |     104M|
------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(( IS NOT NULL OR  IS NOT NULL))

   3 - filter(("AGREEMENTS"."PRINCIPALID"=:B1 AND

              INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE")

              AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   4 - filter(("AGREEMENTS"."COUNTERPARTYID"=:B1 AND

              INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE")

              AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

La première chose qui nous marque dans le plan c’est que l’optimiseur (CBO) n’a pas unnesté la subquery pour effectuer une opération de semi-jointure (voir mon article sur les semi-joins) plus performante que l’opération FILTER qu’on voit dans le plan. En effet, les statistiques d’exécution du plan montrent que la table ENTITIES parcouru via un Full Table Scan retourne 64368 lignes (cf. colonne A-ROWS de l’opération 2) et la table AGREEMENTS est accédée à 2 reprises autant de fois qu’il y’a de lignes dans la table ENTITIES (cf. colonne STARTS du plan). Le nombre de logical I/Os qui en résulte est très important (210M). On est ici dans un cas de Disjunctive Subquery, c’est-à-dire qu’à cause de la clause OR le CBO ne peut unnester la sous-requête. Le seul moyen d’obtenir un plan efficace pour cette requête est de la réécrire afin de remplacer le OR par un UNION ALL. 

Voici la requête réécrite :

SELECT
  Entities.EntityId,
  Entities.EntityName,
  Entities.LegalName
FROM Entities
WHERE (Entities.EntityId IN
  (SELECT Agreements.PrincipalId
  FROM Agreements
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3)
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16))
  AND Agreements.AgreementGroupId            IS NULL)
  AND Agreements.BusinessLine NOT            IN (4))
  ))
UNION all
SELECT
  Entities.EntityId,
  Entities.EntityName,
  Entities.LegalName
FROM Entities
WHERE (Entities.EntityId NOT IN
  (SELECT Agreements.PrincipalId
  FROM Agreements
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3)
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16))
  AND Agreements.AgreementGroupId            IS NULL)
  AND Agreements.BusinessLine NOT            IN (4))
  ))
AND (Entities.EntityId IN
  (SELECT Agreements.CounterpartyId
  FROM Agreements
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3)
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16))
  AND Agreements.AgreementGroupId            IS NULL)
  AND Agreements.BusinessLine NOT            IN (4))
  )) ;

L’idée est de supprimer la clause OR en écrivant 2 requêtes distinctes séparées par un UNION ALL. 

Le plan qui en résulte est le suivant :

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |    258 |00:00:00.04 |    4308 |       |       |          |
|   1 |  SORT UNIQUE                   |             |      1 |    466 |    258 |00:00:00.04 |    4308 | 38912 | 38912 |34816  (0)|
|   2 |   UNION-ALL                    |             |      1 |        |    536 |00:00:00.04 |    4308 |       |       |          |
|   3 |    NESTED LOOPS                |             |      1 |        |    268 |00:00:00.02 |    2134 |       |       |          |
|   4 |     NESTED LOOPS               |             |      1 |    233 |    268 |00:00:00.02 |    1866 |       |       |          |
|*  5 |      TABLE ACCESS FULL         | AGREEMENTS  |      1 |    233 |    268 |00:00:00.02 |    1634 |       |       |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_ENTITIES |    268 |      1 |    268 |00:00:00.01 |     232 |       |       |          |
|   7 |     TABLE ACCESS BY INDEX ROWID| ENTITIES    |    268 |      1 |    268 |00:00:00.01 |     268 |       |       |          |
|   8 |    NESTED LOOPS                |             |      1 |        |    268 |00:00:00.01 |    2174 |       |       |          |
|   9 |     NESTED LOOPS               |             |      1 |    233 |    268 |00:00:00.01 |    1906 |       |       |          |
|* 10 |      TABLE ACCESS FULL         | AGREEMENTS  |      1 |    233 |    268 |00:00:00.01 |    1634 |       |       |          |
|* 11 |      INDEX UNIQUE SCAN         | PK_ENTITIES |    268 |      1 |    268 |00:00:00.01 |     272 |       |       |          |
|  12 |     TABLE ACCESS BY INDEX ROWID| ENTITIES    |    268 |      1 |    268 |00:00:00.01 |     268 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter((INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND "AGREEMENTS"."BUSINESSLINE"<>4 AND

              INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE") AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   6 - access("ENTITIES"."ENTITYID"="AGREEMENTS"."PRINCIPALID")

  10 - filter((INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND "AGREEMENTS"."BUSINESSLINE"<>4 AND

              INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE") AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

  11 - access("ENTITIES"."ENTITYID"="AGREEMENTS"."COUNTERPARTYID") 

On constate que le nombre de logical reads est passé de 210M à seulement 4308 et que la requête répond de manière quasi instantané au lieu de 20 minutes. Dans le nouveau plan on voit que l’opération FILTER a disparu pour laisser place à de vraies jointures (NESTED LOOP en l’occurrence).

L’inconvénient pour mon client était que cette requête était générée par un progiciel et qu’il n’était donc pas possible de la réécrire. On a donc laissé la requête telle quelle en espérant que l’éditeur nous fournisse rapidement une nouvelle version du progiciel intégrant la réécriture de la requête telle que je l’avais préconisée. Quelques semaines plus tard j’apprends que la base en question a migré de 11.2.0.2 à 11.2.0.4 et que les perfs s’en trouvent largement améliorées. En comparant les bases je me suis rendu compte que la requête précédente s’exécutait désormais très bien sans que le code n'ait été touchée mais avec le plan suivant :

 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |     41 |00:00:00.01 |    3354 |       |       |          |
|   1 |  NESTED LOOPS                |             |      1 |     40 |     41 |00:00:00.01 |    3354 |       |       |          |
|   2 |   NESTED LOOPS               |             |      1 |     40 |     41 |00:00:00.01 |    3313 |       |       |          |
|   3 |    VIEW                      | VW_NSO_1    |      1 |     40 |     41 |00:00:00.01 |    3268 |       |       |          |
|   4 |     HASH UNIQUE              |             |      1 |     40 |     41 |00:00:00.01 |    3268 |  2170K|  2170K| 1340K (0)|
|   5 |      UNION-ALL               |             |      1 |        |    618 |00:00:00.01 |    3268 |       |       |          |
|*  6 |       TABLE ACCESS FULL      | AGREEMENTS  |      1 |     20 |    309 |00:00:00.01 |    1634 |       |       |          |
|*  7 |       TABLE ACCESS FULL      | AGREEMENTS  |      1 |     20 |    309 |00:00:00.01 |    1634 |       |       |          |
|*  8 |    INDEX UNIQUE SCAN         | PK_ENTITIES |     41 |      1 |     41 |00:00:00.01 |      45 |       |       |          |
|   9 |   TABLE ACCESS BY INDEX ROWID| ENTITIES    |     41 |      1 |     41 |00:00:00.01 |      41 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND "AGREEMENTS"."BUSINESSLINE"=1 AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   7 - filter(("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND "AGREEMENTS"."BUSINESSLINE"=1 AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   8 - access("ENTITIES"."ENTITYID"="COUNTERPARTYID")

On constate que désormais l’optimiseur a pu merger la sous requête pour pouvoir la joindre avec la table ENTITIES. On voit dans le plan les opérations suivantes :

- VIEW VW_NSO_1
- UNION-ALL

On comprend que le CBO a transformé la requête en instanciant une vue qu’il a nommé VW_NSO_1 et qu’il a créé 2 blocs dans la vue séparés par un UNION. D’ailleurs dans la trace 10053 on voit que la requête transformée par le CBO est la suivante :

 SELECT "ENTITIES"."ENTITYID" "ENTITYID",
  "ENTITIES"."ENTITYNAME" "ENTITYNAME",
  "ENTITIES"."LEGALNAME" "LEGALNAME"
FROM (
  (SELECT "AGREEMENTS"."COUNTERPARTYID" "COUNTERPARTYID"
  FROM "ALGOV5_CIB"."AGREEMENTS" "AGREEMENTS"
  WHERE "AGREEMENTS"."BUSINESSLINE"             =1
  AND "AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162
  AND "AGREEMENTS"."AGREEMENTGROUPID"          IS NULL
  AND "AGREEMENTS"."BUSINESSLINE"              <>4
  )
UNION
  (SELECT "AGREEMENTS"."PRINCIPALID" "PRINCIPALID"
  FROM "ALGOV5_CIB"."AGREEMENTS" "AGREEMENTS"
  WHERE "AGREEMENTS"."BUSINESSLINE"             =1
  AND "AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162
  AND "AGREEMENTS"."AGREEMENTGROUPID"          IS NULL
  AND "AGREEMENTS"."BUSINESSLINE"              <>4
  )) "VW_NSO_1",
  "ALGOV5_CIB"."ENTITIES" "ENTITIES"
WHERE "ENTITIES"."ENTITYID"="VW_NSO_1"."COUNTERPARTYID";

Dans la trace on voit également les éléments suivants :

Dans la trace on voit également les éléments suivants :
Registered qb: SET$7FD77EFD 0xd9e73be0 (SUBQ INTO VIEW FOR COMPLEX UNNEST SET$E74BECDC)
SU:   Checking validity of unnesting subquery SET$E74BECDC (#6)
*** 2015-06-01 10:49:47.061
SU:   Passed validity checks.
SU:   Transform an ANY subquery to semi-join or distinct.

Je pense que le "SUBQ INTO VIEW FOR COMPLEX UNNEST" correspond à la nouvelle fonctionnalité du CBO à l’origine de cette réécriture intelligente. Pour m’assurer que le nouveau plan était bien lié au code du CBO en 11.2.0.4 j’ai testé la requête en settant le parameter optimizer_features_enable à “11.2.0.2” et j’ai effectivement obtenu le mauvais plan à savoir celui sans le unnest de la subquery:

alter session set optimizer_features_enable='11.2.0.2'

Plan hash value: 289829209

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |     41 |00:07:24.66 |     214M|
|*  1 |  FILTER            |            |      1 |        |     41 |00:07:24.66 |     214M|
|   2 |   TABLE ACCESS FULL| ENTITIES   |      1 |  65613 |  65637 |00:00:00.02 |    1447 |
|*  3 |   TABLE ACCESS FULL| AGREEMENTS |  65637 |      1 |      3 |00:01:56.25 |     107M|
|*  4 |   TABLE ACCESS FULL| AGREEMENTS |  65634 |      1 |     38 |00:05:28.24 |     107M|
------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(( IS NOT NULL OR  IS NOT NULL))

   3 - filter(("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND

              "AGREEMENTS"."PRINCIPALID"=:B1 AND "AGREEMENTS"."BUSINESSLINE"=1 AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   4 - filter(("AGREEMENTS"."COUNTERPARTYID"=:B1 AND

              "AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND "AGREEMENTS"."BUSINESSLINE"=1

              AND "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

A vrai dire la rééecriture par le CBO de la requête s’effectue dès la version 11.2.0.3. et non pas à partir de la version 11.2.0.4. J’ai pu valider ça en mettant le parametre optimizer_features_enable à “11.2.0.3” et observer que le plan observé en 11.2.0.4 était choisi.

Pour plus d’informations concernant le Disjunctive Subquery, je vous invite à lire ces 2 articles écrits par Mohamed Houri:
http://www.toadworld.com/platforms/oracle/w/wiki/11081.tuning-a-disjunctive-subquery.aspx  
https://hourim.wordpress.com/2014/05/12/disjunctive-subquery/

Forcer un plan d'une requête à partir d'une autre requête

Dans un article précèdent, j'avais montré comment il était possible grâce aux SQL profiles d'ajouter un hint à une requête identifiée par son SQL_ID sans avoir à toucher au code de la requête.

Cette fois il s'agit d'une autre problématique. J'ai deux requêtes similaires mais chacune avec un SQL_ID différent et je veux que la première utilise le plan de la 2ème. Grâce à un script de Kerry Osborne et l'utilisation de la procédure DBMS_SQLTUNE il est possible d'attacher le plan d'une requête A à une requête B.

Pour améliorer les performances d'une requête d'un client, j'ai récemment eu à utiliser cette technique. Je vais tenter dans cet article de vous expliquer comment j'ai procédé.

Mon client m'a envoyé un email la semaine dernière car il se plaignait d'une requête s'exécutant lentement en PROD alors qu'elle était plutôt rapide en recette. En me connectant sur les 2 bases et en exécutant la requête  j'ai pu m'apercevoir qu'effectivement en PROD le plan différait de celui en RECETTE. 

Voici un extrait du plan en PROD. On voit qu'il génère 1992K logical I/Os:

Plan hash value: 685980531


------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |     18 |00:01:18.50 |    1992K|      2 |       |       |          |
|   1 |  SORT AGGREGATE                        |               |     18 |      1 |     18 |00:00:00.01 |      74 |      0 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | CI_FT         |     18 |      4 |     44 |00:00:00.01 |      74 |      0 |       |       |          |

Le plan en recette ne génère quant à lui que 2K logical reads.

Plan hash value: 3994356561

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |     18 |00:00:01.40 |    2158 |     41 |       |       |          |
|   1 |  SORT AGGREGATE                        |               |     18 |      1 |     18 |00:00:00.01 |      55 |      0 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | CI_FT         |     18 |      4 |     44 |00:00:00.01 |      55 |      0 |       |       |          |

Tout d'abord ma première idée a été de tester le plan de la base de recette en prod. Pour ce faire j'ai récupéré l'outline (c'est à dire l'ensemble des hints qui constituent le plan) du plan de la base de rectte en utilisant l'opion ADVANCED de la fonction DBMS_XPLAN:

SQL> explain plan for
........

SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'advanced'));

.......

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$16")
      INDEX_RS_ASC(@"SEL$16" "FT2"@"SEL$16" ("CI_FT"."BILL_ID"))
      PUSH_SUBQ(@"SEL$16")
      NLJ_BATCHING(@"SEL$15" "PAY"@"SEL$15")
      USE_NL(@"SEL$15" "PAY"@"SEL$15")
      USE_NL(@"SEL$15" "FT"@"SEL$15")
      LEADING(@"SEL$15" "BILL2"@"SEL$15" "FT"@"SEL$15" "PAY"@"SEL$15")
      INDEX(@"SEL$15" "PAY"@"SEL$15" ("CI_PAY"."PAY_ID"))
      INDEX_RS_ASC(@"SEL$15" "FT"@"SEL$15" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$15" "BILL2"@"SEL$15" ("CI_BILL"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$17")
      USE_NL(@"SEL$17" "PAY"@"SEL$17")
      LEADING(@"SEL$17" "BILL2"@"SEL$17" "PAY"@"SEL$17")
      INDEX_RS_ASC(@"SEL$17" "PAY"@"SEL$17" ("CI_PAY"."ACCT_ID"))
      INDEX_RS_ASC(@"SEL$17" "BILL2"@"SEL$17" ("CI_BILL"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$0D753FAC")
      NLJ_BATCHING(@"SEL$0D753FAC" "FT"@"SEL$18")
      USE_NL(@"SEL$0D753FAC" "FT"@"SEL$18")
      USE_NL(@"SEL$0D753FAC" "FT2"@"SEL$19")
      LEADING(@"SEL$0D753FAC" "BILL2"@"SEL$18" "FT2"@"SEL$19" "FT"@"SEL$18")
      INDEX(@"SEL$0D753FAC" "FT"@"SEL$18" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$0D753FAC" "FT2"@"SEL$19" ("CI_FT"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$0D753FAC" "BILL2"@"SEL$18" ("CI_BILL"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$267CE17A" "A1"@"SEL$11" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$7B312CD2" "MATCH"@"SEL$12")
      USE_NL(@"SEL$7B312CD2" "MATCH"@"SEL$12")
      USE_NL(@"SEL$7B312CD2" "FT2"@"SEL$12")
      LEADING(@"SEL$7B312CD2" "A1"@"SEL$13" "FT2"@"SEL$12" "MATCH"@"SEL$12")
      INDEX(@"SEL$7B312CD2" "MATCH"@"SEL$12" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7B312CD2" "FT2"@"SEL$12" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7B312CD2" "A1"@"SEL$13" ("CI_FT"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$10")
      USE_NL(@"SEL$10" "TST"@"SEL$10")
      LEADING(@"SEL$10" "BILL2"@"SEL$10" "TST"@"SEL$10")
      NO_ACCESS(@"SEL$10" "TST"@"SEL$10")
      INDEX_RS_ASC(@"SEL$10" "BILL2"@"SEL$10" ("CI_BILL"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$2" "CI_FT"@"SEL$2" ("CI_FT"."BILL_ID"))
      INDEX(@"SEL$3" "CI_BSEG"@"SEL$3" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$4" "CI_BSEG"@"SEL$4" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$5" "CI_BSEG"@"SEL$5" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$6" "CI_BSEG"@"SEL$6" ("CI_BSEG"."BILL_ID"))
      NLJ_BATCHING(@"SEL$7" "ME"@"SEL$7")
      USE_NL(@"SEL$7" "ME"@"SEL$7")
      LEADING(@"SEL$7" "FT"@"SEL$7" "ME"@"SEL$7")
      INDEX(@"SEL$7" "ME"@"SEL$7" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7" "FT"@"SEL$7" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$8" "L"@"SEL$8")
      USE_NL(@"SEL$8" "L"@"SEL$8")
      LEADING(@"SEL$8" "BCHAR"@"SEL$8" "L"@"SEL$8")
      INDEX(@"SEL$8" "L"@"SEL$8" ("CI_CHAR_VAL_L"."CHAR_TYPE_CD" "CI_CHAR_VAL_L"."CHAR_VAL"
              "CI_CHAR_VAL_L"."LANGUAGE_CD"))
      INDEX_RS_ASC(@"SEL$8" "BCHAR"@"SEL$8" ("CI_BILL_CHAR"."BILL_ID" "CI_BILL_CHAR"."CHAR_TYPE_CD"
              "CI_BILL_CHAR"."SEQ_NUM"))
      NO_ACCESS(@"SEL$9" "MA_BALANCE"@"SEL$9")
      NO_ACCESS(@"SEL$14" "ELEMENTS"@"SEL$14")
      INDEX_RS_ASC(@"SEL$20" "CI_FT"@"SEL$20" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$21" "ME"@"SEL$21")
      USE_NL(@"SEL$21" "ME"@"SEL$21")
      LEADING(@"SEL$21" "FT"@"SEL$21" "ME"@"SEL$21")
      INDEX(@"SEL$21" "ME"@"SEL$21" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$21" "FT"@"SEL$21" ("CI_FT"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$1" "BILL"@"SEL$1" ("CI_BILL"."ACCT_ID"))
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$19")
      OUTLINE(@"SEL$18")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SET$1")
      MERGE(@"SEL$13")
      OUTLINE(@"SEL$61262C81")
      OUTLINE(@"SEL$11")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$21")
      OUTLINE_LEAF(@"SEL$20")
      OUTLINE_LEAF(@"SEL$14")
      OUTLINE_LEAF(@"SET$2")
      UNNEST(@"SEL$19")
      OUTLINE_LEAF(@"SEL$0D753FAC")
      OUTLINE_LEAF(@"SEL$17")
      OUTLINE_LEAF(@"SEL$15")
      OUTLINE_LEAF(@"SEL$16")
      OUTLINE_LEAF(@"SEL$9")
      OUTLINE_LEAF(@"SEL$10")
      PUSH_PRED(@"SEL$10" "TST"@"SEL$10" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$7B312CD2")
      OUTLINE_LEAF(@"SEL$267CE17A")
      OUTLINE_LEAF(@"SEL$8")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('optimizer_index_caching' 50)
      OPT_PARAM('optimizer_index_cost_adj' 30)
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
...................

(Je vous ai épargné ce qui n'était pas utile dans l'output)

Ensuite j'ai copié cet outline pour le mettre sous forme de hint dans la requête et je l'ai exécutée en PROD. J'ai obtenu le même plan avec des stats d'exécution équivalents à celle de la recette.

L'idéal aurait été de faire une analyse approfondie pour comprendre pourquoi un mauvais plan était choisi en PROD mais le temps ne nous le permettait pas et mon client était satisfait du plan en recette d'autant plus que la requête n'est pas censé être modifiée et que les tables ont une volumétrie stable.

La solution la plus efficace dans ce cas était donc de forcer le bon plan en demandant au client d'ajouter l'ensemble des hints constituant l'outline du bon plan dans le code de la requête. L'inconvénient c'est que mon client n'avait pas la possibilité de modifier cette requête et il n'y avait pas dans l'historique d'exécution de la requête en PROD le bon plan ou un plan avec des statistiques d'exécution satisfaisantes.

Et c'est là que le script de Kerry Osborne entre en jeu:

----------------------------------------------------------------------------------------
--
-- File name:   move_sql_profile.sql
--
-- Purpose:     Moves a SQL Profile from one statement to another.
-
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              profile_name: the name of the profile to be attached to a new statement
--
--              sql_id: the sql_id of the statement to attach the profile to
--
--              category: the category to assign to the new profile 
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: This script is based on a script originally written by Randolf Giest. 
--              It's purpose is to allow a statements text to be manipulated in whatever
--              manner necessary (typically with hints) to get the desired plan. Then 
--              once a SQL Profile has been created on the new statement, it's SQL Profile
--              can be moved (or attached) to the orignal statement with unmodified text.
--
-- Mods:        This script should now work wirh all flavors of 10g and 11g.
--              
--
--              See kerryosborne.oracle-guy.com for additional information.
----------------------------------------------------------------------------------------- 

accept profile_name -
       prompt 'Enter value for profile_name: ' -
       default 'X0X0X0X0'
accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (false): ' -
       default 'false'

----------------------------------------------------------------------------------------
--
-- File name:   profile_hints.sql
--
---------------------------------------------------------------------------------------
--
set sqlblanklines on


declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
 select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select attr_val as outline_hints '||
   'from dba_sql_profiles p, sqlprof$attr h '||
   'where p.signature = h.signature '||
   'and name like (''&&profile_name'') '||
   'order by attr#'
   bulk collect 
   into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select hint as outline_hints '||
   'from (select p.name, p.signature, p.category, row_number() '||
   '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
   '      extractValue(value(t), ''/hint'') hint '||
   'from sys.sqlobj$data sd, dba_sql_profiles p, '||
   '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
   '                               ''/outline_data/hint''))) t '||
   'where sd.obj_type = 1 '||
   'and p.signature = sd.signature '||
   'and p.name like (''&&profile_name'')) '||
   'order by row_num'
   bulk collect 
   into ar_profile_hints;

end if;

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => '&&category'
, name => 'PROFILE_'||'&&sql_id'||'_moved'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&force_matching
);
end;
/
undef profile_name
undef sql_id
undef category
undef force_matching

L'idée de ce script est d'attacher un plan d'une requête (qu'on a réussi à obtenir d'une manière ou d'une autre) à une requête s'exécutant avec un plan non satisfaisant et qu'on ne peut modifier.

L'exécution de ce script est en fait la dernière étape d'un plan en 3 étapes:

1) Exécuter la requête avec le bon plan (en ajoutant les hints de l'outline)

2) Création d'un SQL profile pour y coller le plan obtenu en (1)

3) Coller le SQL Profile créé en (2) à la requête exécutée par l'application

La 3ème étape correspond en fait à l'exécution du script de Kerry Osborne.

L'étape 1 je l'ai réalisée lorsque j'ai exécuté la requête avec l'outline.

L'étape 2 consiste à exécuter un autre script de kerry Osborne que j'avais expliqué dans un de mes tous premiers articles. A l'étape 1 j'ai obtenu un SQL_ID dccyz592gpzpq pour lequel je veux créer un SQL profile qui va me permettre de figer le plan obtenu:

SQL> @sp_create_sql_profile.sql
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      create_sql_profile.sql
SQL> --
SQL> -- Purpose:        Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
SQL> --
SQL> -- Author: Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
SQL> --
SQL> --              child_no: the child_no of the statement from v$sql
SQL> --
SQL> --              profile_name: the name of the profile to be generated
SQL> --
SQL> --              category: the name of the category for the profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description:
SQL> --
SQL> --              Based on a script by Randolf Giest.
SQL> --
SQL> -- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> ---------------------------------------------------------------------------------------
SQL> --
SQL>
SQL> -- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'
SQL>
SQL> set feedback off
SQL> set sqlblanklines on
SQL>
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: dccyz592gpzpq
SQL> accept child_no -
>        prompt 'Enter value for child_no (0): ' -
>        default '0'
Enter value for child_no (0):
SQL> accept profile_name -
>        prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
>        default 'X0X0X0X0'
Enter value for profile_name (PROF_sqlid_planhash):
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL> accept force_matching -
>        prompt 'Enter value for force_matching (FALSE): ' -
>        default 'false'
Enter value for force_matching (FALSE): TRUE
SQL>
SQL> declare
  2  ar_profile_hints sys.sqlprof_attr;
  3  cl_sql_text clob;
  4  l_profile_name varchar2(30);
  5  begin
  6  select
  7  extractvalue(value(d), '/hint') as outline_hints
  8  bulk collect
  9  into
 10  ar_profile_hints
 11  from
 12  xmltable('/*/outline_data/hint'
 13  passing (
 14  select
 15  xmltype(other_xml) as xmlval
 16  from
 17  v$sql_plan
 18  where
 19  sql_id = '&&sql_id'
 20  and child_number = &&child_no
 21  and other_xml is not null
 22  )
 23  ) d;
 24
 25  select
 26  sql_fulltext,
 27  decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
 28  into
 29  cl_sql_text, l_profile_name
 30  from
 31  v$sql
 32  where
 33  sql_id = '&&sql_id'
 34  and child_number = &&child_no;
 35
 36  dbms_sqltune.import_sql_profile(
 37  sql_text => cl_sql_text,
 38  profile => ar_profile_hints,
 39  category => '&&category',
 40  name => l_profile_name,
 41  force_match => &&force_matching
 42  -- replace => true
 43  );
 44
 45    dbms_output.put_line(' ');
 46    dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
 47    dbms_output.put_line(' ');
 48
 49  exception
 50  when NO_DATA_FOUND then
 51    dbms_output.put_line(' ');
 52    dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
 53    dbms_output.put_line(' ');
 54
 55  end;
 56  /
old  19: sql_id = '&&sql_id'
new  19: sql_id = 'dccyz592gpzpq'
old  20: and child_number = &&child_no
new  20: and child_number = 0
old  27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
new  27: decode('X0X0X0X0','X0X0X0X0','PROF_dccyz592gpzpq'||'_'||plan_hash_value,'X0X0X0X0')
old  33: sql_id = '&&sql_id'
new  33: sql_id = 'dccyz592gpzpq'
old  34: and child_number = &&child_no;
new  34: and child_number = 0;
old  39: category => '&&category',
new  39: category => 'DEFAULT',
old  41: force_match => &&force_matching
new  41: force_match => TRUE
old  52:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
new  52:   dbms_output.put_line('ERROR: sql_id: '||'dccyz592gpzpq'||' Child: '||'0'||' not found in v$sql.');

On vérifie qu'un SQL profile nommé PROF_dccyz592gpzpq_3994356561 a bien été créé:

SQL> @sp_list_sql_profiles.sql
SQL> col category for a15
SQL> col sql_text for a70 trunc
SQL> select name, category, status, sql_text, force_matching
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text','%')
  4  and name like nvl('&name',name)
  5  order by last_modified
  6  /
Enter value for sql_text:
old   3: where sql_text like nvl('&sql_text','%')
new   3: where sql_text like nvl('','%')
Enter value for name:
old   4: and name like nvl('&name',name)
new   4: and name like nvl('',name)

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_dccyz592gpzpq_3994356561  DEFAULT         ENABLED  SELECT                                                         YES

Et c'est ce SQL profile qu'on veut coller à la requête exécutée par l'application et pour ce faire on utilise le script que j'ai affiché plus haut:

SQL> @sp_move_sql_profile.sql
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      move_sql_profile.sql
SQL> --
SQL> -- Purpose:        Moves a SQL Profile from one statement to another.
SQL> -
> -- Author:      Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              profile_name: the name of the profile to be attached to a new statement
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to
SQL> --
SQL> --              category: the category to assign to the new profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description: This script is based on a script originally written by Randolf Giest.
SQL> --              It's purpose is to allow a statements text to be manipulated in whatever
SQL> --              manner necessary (typically with hints) to get the desired plan. Then
SQL> --              once a SQL Profile has been created on the new statement, it's SQL Profile
SQL> --              can be moved (or attached) to the orignal statement with unmodified text.
SQL> --
SQL> -- Mods:        This script should now work wirh all flavors of 10g and 11g.
SQL> --
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> -----------------------------------------------------------------------------------------
SQL>
SQL> accept profile_name -
>        prompt 'Enter value for profile_name: ' -
>        default 'X0X0X0X0'
Enter value for profile_name: PROF_dccyz592gpzpq_3994356561
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 0496r075a27c8
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL> accept force_matching -
>        prompt 'Enter value for force_matching (false): ' -
>        default 'false'
Enter value for force_matching (false): true
SQL>
SQL>
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      profile_hints.sql
SQL> --
SQL> ---------------------------------------------------------------------------------------
SQL> --
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  ar_profile_hints sys.sqlprof_attr;
  3  cl_sql_text clob;
  4  version varchar2(3);
  5  l_category varchar2(30);
  6  l_force_matching varchar2(3);
  7  b_force_matching boolean;
  8  begin
  9   select regexp_replace(version,'\..*') into version from v$instance;
 10
 11  if version = '10' then
 12
 13  -- dbms_output.put_line('version: '||version);
 14     execute immediate -- to avoid 942 error
 15     'select attr_val as outline_hints '||
 16     'from dba_sql_profiles p, sqlprof$attr h '||
 17     'where p.signature = h.signature '||
 18     'and name like (''&&profile_name'') '||
 19     'order by attr#'
 20     bulk collect
 21     into ar_profile_hints;
 22
 23  elsif version = '11' then
 24
 25  -- dbms_output.put_line('version: '||version);
 26     execute immediate -- to avoid 942 error
 27     'select hint as outline_hints '||
 28     'from (select p.name, p.signature, p.category, row_number() '||
 29     '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
 30     '      extractValue(value(t), ''/hint'') hint '||
 31     'from sys.sqlobj$data sd, dba_sql_profiles p, '||
 32     '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
 33     '                               ''/outline_data/hint''))) t '||
 34     'where sd.obj_type = 1 '||
 35     'and p.signature = sd.signature '||
 36     'and p.name like (''&&profile_name'')) '||
 37     'order by row_num'
 38     bulk collect
 39     into ar_profile_hints;
 40
 41  end if;
 42
 43
 44  /*
 45  declare
 46  ar_profile_hints sys.sqlprof_attr;
 47  cl_sql_text clob;
 48  begin
 49  select attr_val as outline_hints
 50  bulk collect
 51  into
 52  ar_profile_hints
 53  from dba_sql_profiles p, sqlprof$attr h
 54  where p.signature = h.signature
 55  and name like ('&&profile_name')
 56  order by attr#;
 57  */
 58
 59  select
 60  sql_fulltext
 61  into
 62  cl_sql_text
 63  from
 64  v$sqlarea
 65  where
 66  sql_id = '&&sql_id';
 67
 68  dbms_sqltune.import_sql_profile(
 69  sql_text => cl_sql_text
 70  , profile => ar_profile_hints
 71  , category => '&&category'
 72  , name => 'PROFILE_'||'&&sql_id'||'_moved'
 73  -- use force_match => true
 74  -- to use CURSOR_SHARING=SIMILAR
 75  -- behaviour, i.e. match even with
 76  -- differing literals
 77  , force_match => &&force_matching
 78  );
 79  end;
 80  /
old  18:    'and name like (''&&profile_name'') '||
new  18:    'and name like (''PROF_dccyz592gpzpq_3994356561'') '||
old  36:    'and p.name like (''&&profile_name'')) '||
new  36:    'and p.name like (''PROF_dccyz592gpzpq_3994356561'')) '||
old  55: and name like ('&&profile_name')
new  55: and name like ('PROF_dccyz592gpzpq_3994356561')
old  66: sql_id = '&&sql_id';
new  66: sql_id = '0496r075a27c8';
old  71: , category => '&&category'
new  71: , category => 'DEFAULT'
old  72: , name => 'PROFILE_'||'&&sql_id'||'_moved'
new  72: , name => 'PROFILE_'||'0496r075a27c8'||'_moved'
old  77: , force_match => &&force_matching
new  77: , force_match => true

PL/SQL procedure successfully completed.

Ce script prend notamment en paramètre le nom du SQL profile qu'on veut attacher et le SQL_ID de la requête pourlaquelle on veut forcer le plan. Dans mon cas la requête en question avait pour SQL_ID 0496r075a27c8.

Si j'affiche les SQL profiles de ma base je vois que j'ai maintenant un 2ème SQL Profile nommé PROFILE_0496r075a27c8_moved et qui est attaché au SQL_ID 0496r075a27c8:

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_dccyz592gpzpq_3994356561  DEFAULT         ENABLED  SELECT                                                         YES
PROFILE_0496r075a27c8_moved    DEFAULT         ENABLED  SELECT   DECODE (bill.alt_bill_id, 0, ' ', alt_bill_id) alt_bill_id,   YES

Maintenant lorsque mon client lance son application c'est le bon plan qui est exécuté. D'ailleurs lorsque j'affiche le plan exécuté désormais pour cette requête je vois la note suivante à la fin plan qui m'indique que c'est bien grâce au SQL profile que ce plan a été généré:

Note
-----
   - SQL profile PROFILE_0496r075a27c8_moved used for this statement

Dans le même thème, voir aussi les articles suivants:

Forcer un plan d'exécution via un SQL profile

Ajouter un hint sans toucher à la requête

Ajouter un hint sans toucher à la requête (grâce au SQL patch)

Voir également l' article de mon ami Mohamed Houri expliquant comment utiliser SPM pour obtenir à peu près le même résultat que moi avec les SQL profiles:

How to attach a hinted SPM baseline to a non hinted sql query?