Bind Variable Peeking

Un problème rencontré récemment sur une base d'un client m'a rappelé à quel point ce concept était fondamental en matière de tuning SQL.

Comme son nom peut le laisser entendre, le Bind Variable Peeking est lié à l'utilisation des variables bindées pour les requêtes SQL.

Petit rappel sur l'intérêt des binds variables:

Lorsqu'on exécute plusieurs fois une requête pour laquelle seule la valeur littérale d'une colonne est amenée à changer, le CBO doit générer un plan d'exécution pour chacune de ces requêtes. En effet, dans l'exemple ci-dessous comme le corps de chacune des 2 requêtes n'est pas (syntaxiquement) identique, Oracle considère qu'il s'agit d'une nouvelle requête (SQL_ID différent) et va effectuer un HARD PARSE (calcul de plan) pour chacune d'elles :
select * from toto where c1  = 1;
select * from toto where c1  = 2;

SQL> select sql_id,child_number from v$sql where sql_text like 'select * from toto where c1  =%';

SQL_ID        CHILD_NUMBER
------------- ------------
6dchwd6xf9kjq            0
0j5bytr8g767t            0

En utilisant, une variable bindée à la place de la valeur littérale on obtient un partage du curseur. Le plan d'exécution n'est calculé que lors de la première exécution. Les autres fois où la requête est exécutée le plan partagé dans la shared pool sera utilisé (le hard parse est évité):

SQL> variable n number;
SQL> execute :n := 1;
SQL> select * from toto where c1  = :n;
SQL> execute :n := 2;
SQL> select * from toto where c1  = :n;
SQL> select sql_id,child_number,executions from v$sql where sql_text like 'select * from toto where c1  = :n%';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
ffh26jcnjg03y            0          2 

Voyons maintenant en quoi consiste le Bind Peeking:

Dans le schéma SH d'Oracle il existe une table CUSTOMERS dont la colonne COUNTRY_ID (Foreign Key vers la table COUNTRIES) est skewed, c'est à dire que les données pour cette colonne ne sont pas uniformément réparties.

En effet, avec la requête suivante on s'aperçoit que l'essentiel des clients résident aux US alors que les clients les moins nombreux sont en Arabie Saoudite:

SQL> select country_name,cu.country_id,count(1) from customers cu,countries co
  2  where cu.country_id=co.country_id
  3  group by country_name,cu.country_id
  4  order by 3 desc;

COUNTRY_NAME                             COUNTRY_ID   COUNT(1)
---------------------------------------- ---------- ----------
United States of America                      52790      18520
Germany                                       52776       8173
Italy                                         52770       7780
United Kingdom                                52789       7557
France                                        52779       3833
Spain                                         52778       2039
Canada                                        52772       2010
Brazil                                        52775        832
Australia                                     52774        831
China                                         52771        712
Poland                                        52786        708
Japan                                         52782        624
Singapore                                     52769        597
Argentina                                     52773        403
Denmark                                       52777        383
New Zealand                                   52785        244
Turkey                                        52788         91
South Africa                                  52791         88
Saudi Arabia                                  52787         75

Créons un index sur la colonne COUNTRY_ID et collectons les stats sur la table:

SQL> create index idx_cust_country  on customers(country_id);

Index créé

SQL> exec dbms_stats.gather_table_stats(user,'CUSTOMERS',method_opt=>'for all columns size auto',no_invalidate=>FALSE);

Procédure PL/SQL terminée avec succès.

La clause "FOR ALL COLUMNS SIZE AUTO" laisse le choix au CBO de calculer des histogrammes pour les colonnes qu'il juge nécessaires et notamment les colonnes SKEWED.
On peut s'apercevoir grâce à la requête suivante qu'un histogramme a bien été crée pour la colonne COUNTRY_ID:

SQL> select column_name,num_distinct,histogram
  2  from user_tab_col_statistics where table_name='CUSTOMERS';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
CUST_ID                               55500 NONE
CUST_FIRST_NAME                        1300 NONE
CUST_LAST_NAME                          908 NONE
CUST_GENDER                               2 NONE
CUST_YEAR_OF_BIRTH                       75 NONE
CUST_MARITAL_STATUS                      11 NONE
CUST_STREET_ADDRESS                   49900 NONE
CUST_POSTAL_CODE                        623 NONE
CUST_CITY                               620 NONE
CUST_CITY_ID                            620 HEIGHT BALANCED
CUST_STATE_PROVINCE                     145 NONE
CUST_STATE_PROVINCE_ID                  145 FREQUENCY
COUNTRY_ID                               19 FREQUENCY
CUST_MAIN_PHONE_NUMBER                51344 NONE
CUST_INCOME_LEVEL                        12 NONE
CUST_CREDIT_LIMIT                         8 NONE
CUST_EMAIL                             1699 NONE
CUST_TOTAL                                1 NONE
CUST_TOTAL_ID                             1 FREQUENCY
CUST_SRC_ID                               0 NONE
CUST_EFF_FROM                             1 NONE
CUST_EFF_TO                               0 NONE
CUST_VALID                                2 NONE

On voit qu'Oracle a calculé des histogrammes pour certaines colonnes mais pas toutes.

Lorsque j'exécute une requête listant les clients américains, le CBO lors du parsing, sait qu'1/3 de la table va être retournée et va donc préférer effectuer un Full Table Scan (FTS):

SQL> SET AUTOT TRACE EXP
SQL> SELECT *
  2  FROM customers
  3  WHERE country_id = 52790;

18520 ligne(s) sélectionnée(s).

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 18510 |  3253K|   406   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 18510 |  3253K|   406   (1)| 00:00:05 |
-------------------------------------------------------------------------------

SQL> SET AUTOT OFF

Si je liste les clients saoudiens, le CBO choisira un accès via l'index sur COUNTRY_ID car il sait que je vais retourner un très faible pourcentage de la table:

SQL> SET AUTOT TRACE EXP
SQL> SELECT *
  2  FROM customers
  3  WHERE country_id = 52787;

75 ligne(s) sélectionnée(s).

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   111 | 19980 |    25   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS        |   111 | 19980 |    25   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_CUST_COUNTRY |   111 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

SQL> SET AUTOT OFF

Le CBO a cette connaissance sur les cardinalités retournées du fait qu'un histogramme a été calculé pour la colonne COUNTRY_ID.
Sans cet histogramme le CBO est incapable de savoir que selon le pays le nombre de clients peut être différent. En l'absence d'histogrammes le CBO estime que les données sont uniformément réparties et considère que le nombre de lignes à retourner correspond au nombre de lignes total de la table divisé par le nombre de valeurs distincts pour cette colonne:
NUM_ROWS/NUM_DISTINCT = 55500/19 = 2921 clients par pays

Maintenant que se passe-t-il quand on utilise une variable bindée pour la colonne COUNTRY_ID?
Ce qu'il faut savoir c'est qu'avant la 9i, le CBO n'avait aucune idée (au moment du parsing de la requête) de la valeur contenue dans la variable, et du coup pour faire ses estimations il utilisait des sélectivités constantes codées en dur (5% pour les clauses "<" ou ">").

Avec la 9i est arrivée la notion de BIND VARIABLE PEEKING qui est la fonctionalité permettant à Oracle de "peeker" la valeur des variables bindées au moment du parsing et de faire du coup une estimation des cardinalités comme s'il traitait une requête avec la valeur littéral dans la clause WHERE.
La preuve:

SQL> VARIABLE country_id number;

SQL> -- Récup des clients américains
SQL> execute :country_id := 52790;

SQL> SELECT *
  2  FROM customers
  3  WHERE country_id = :country_id;

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 18510 |  3253K|   406   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 18510 |  3253K|   406   (1)| 00:00:05 |
-------------------------------------------------------------------------------

Regardez les cardinalités estimées dans la colonne ROWS. On a la même cardinalité que dans le plan où on mettait directement le numéro du pays dans la requête.

Maintenant je fais la même chose mais pour les clients saoudiens:

SQL> -- Récup des clients saoudiens
SQL> execute :country_id := 52787;

SQL> SELECT *
  2  FROM customers
  3  WHERE country_id = :country_id;

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 18510 |  3253K|   406   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 18510 |  3253K|   406   (1)| 00:00:05 |
-------------------------------------------------------------------------------

Cette fois je n'ai pas d'accès via l'index alors que je suis censé récupérer un faible pourcentage de la table.
Que s'est-il donc passé? Pourquoi le CBO s'est-il trompé ici?
En fait, il ne s'est pas trompé. Le CBO a simplement pris le plan déjà dans la shared pool et l'a appliqué pour cette requête. Comme syntaxiquement la requête est identique le HARD PARSE n'est effectué que pour la première exécution. Ensuite, le plan calculé la première fois sera toujours pris en compte pour les autres exécutions. C'est le but des variables bindées, mais ici on découvre aussi quel est leur inconvénient.

Les bind variables sont à privilégier dans vos applications mais si vous avez des requêtes sur des colonnes dont la distribution des données n'est pas uniforme alors il vaut mieux ne pas utiliser de bind variable pour ces requêtes car comme dans l'exemple précédent vous risquez de vous retrouver avec un plan qui n'est pas optimal pour certaines exécutions. Le premier plan calculé sera toujours pris en compte jusqu'à ce qu'il soit vidé de la shared pool ou que le curseur ait été invalidé.

Le bind Peeking et la météo:
On m'a raconté une fois une anecdote qui permet d'illustrer les inconvénients du Bind Peeking.
Voici en résumé le problème qui a déjà fait l'objet d'une SR auprès du support Oracle:

Nous avons constaté que lorsqu'il fait beau notre application ne rencontre aucun problème de performance alors que lorsqu'il pleut nous faisons face à de gros problèmes de lenteur sur nos applications.

Ce problème a vraiment été remonté par un client d'Oracle au support.
Mais quel est donc le rapport entre la météo et le Bind Peeking?
En fait chez ce client on a des utilisateurs qui lancent plusieurs batch au cours de la journée. Un de ces utilisateurs en particulier lance un batch dont certaines requêtes font l'objet de Full Table Scan car ses requêtes récupèrent un grand pourcentage de données. Les autres utilisateurs lancent plutôt des batch qui génèrent des accès indéxés.
Que se passe-t-il lorsqu'il fait beau?
L'utilisateur dont le batch génère des FTS arrive en vélo le matin et donc arrive après les autres utilisateurs qui ont déjà lancé leur batch. Du coup, les plans avec les accès indéxés sont déjà dans la shared pool et partagés aux autres utilisateurs. Lorsqu'il pleut cet utilisateur préfère venir en bus, arrive donc avant tout le monde et lance son batch en premier.
Vous commencez à comprendre? Les requêtes de ce batch sont parsés en premier et donc on se retrouve avec des plans pleins de Full Table Scan en cache. Les autres utilisateurs, au moment où ils lancent leur batch, se mettent à récupérer ces plans d'exécution qui ne sont pas optimaux pour leurs requêtes. D'où le problème de performance lorsqu'il pleut.

CONCLUSION:
Lorsque vous utilisez des binds variables (mettre le paramètre CURSOR_SHARING à force par exemple a le même effet) ayez bien conscience des avantages mais aussi des effets dramatiques que ça peut engendrer. La première requête parsée conditionnera le plan qui sera utilisé pour toutes les autres requêtes.
Avec la 11g, Oracle a trouvé un moyen de remédier plus ou moins à ce problème via la fonctionnalité Adaptive Cursor Sharing (ACS). Mais ceci fera l'objet d'une autre post.