Jonathan Lewis philosophies : french translation

Les lecteurs francophones peuvent trouver, ci-dessous, une traduction de presque toutes les philosophies de Jonathan Lewis.

Philosophie 1

Il existe certains concepts Oracle très importants si bien qu’ils doivent être gravés dans votre mémoire voltigeant  autour de vos yeux à chaque fois que vous voulez investiguer un problème de performance en SQL. Voici un de ces concepts :

Les histogrammes et les variables de liaison existent pour des raisons diamétralement opposées ; ils ne vont pas fonctionner ensemble sans aide.

Vous utilisez les variables de liaison parce que vous voulez que tout le monde partage le même curseur enfant (child cursor) d’une instruction SQL ; cette instruction SQL, allant être utilisée très fréquemment, tout le monde va accomplir la même petite charge de travail en l’exécutant. Le même plan d’exécution devrait être idéal pour tous, et vous ne voulez pas ré-optimiser cette instruction SQL à chaque réutilisation parce que le coût de son optimisation va être probablement plus élevé que ne le serait celui des ressources nécessaires pour son exécution. Typiquement, on souhaite une large (mais pas exclusive) utilisation des variables de liaison dans des systèmes OLTP.

Vous créez des histogrammes parce que des instructions SQL, qui sont virtuellement identiques, génèrent des charges de travail qui diffèrent énormément, nécessitent des plans d’exécutions différents, le travail fait lors de leur optimisation est négligeable lorsqu’il est comparé à celui fait lors de leur exécution, et, si elles utilisent un mauvais plan d’exécution, elles conduiraient à une énorme perte de ressources. Typiquement, on a besoin de générer des histogrammes dans des systèmes DataWareHouse ou d’aide à la décision où les requêtes peuvent être brutales et très coûteuses.

Notez bien la contradiction : une technologie est supposée vous donner un plan d’exécution unique utilisable par tout le monde  alors que l’autre technologie est supposée donner à chacun son meilleur plan d’exécution

Garder bien cela en mémoire et vous allez être rappelé à être vigilant quant à la création des histogrammes dans des systèmes OLTP. Ainsi,  vous n’allez pas être tenté de transformer absolument chaque valeur littérale en une variable de liaison.

Philosophie 2

Voici une autre idée parmi celles qui sont tellement fondamentales que vous devez toujours les avoir à l’esprit lorsque vous travaillez avec une base de données Oracle

La stratégie fondamentale incorporée dans l’Optimisateur est basée uniquement sur deux points clés:

• Combien de données vous voulez rapatrier ?
• Où les avez-vous stockées ?

Si vous tentez d’améliorer la performance d’une requête SQL en jouant avec son contenu jusqu’à ce qu’elle s’exécute assez rapidement, alors vous n’êtes pas sur la bonne voie et vous perdez du temps inutilement.

Si, par contre, vous débutez votre tâche de ’’Tuning’’ en pensant à la taille du volume de données que vous êtes supposé rapatrier et à la nature de la dispersion aléatoire de ces données, alors vous allez réduire le temps nécessaire à trouver la meilleure manière d’acquérir efficacement ces données.

Philosophie 3

La performance d’une requête SQL devrait être liée à la taille du volume de données qui vous intéresse et non à la taille de la base de données.

Si ceci n’est pas votre cas alors vous avez fait une erreur dans l’implémentation physique de votre base de données. Typiquement ceci peut être lié à une pauvre indexation, mais cela peut être aussi dû à des erreurs dans le choix d’autres structures telles que le partitionnement, le clustering, IOT’s ou les vues matérialisées.

Le premier symptôme de cette erreur est la plainte suivante: “au fur et à mesure que le temps avance la base de données devient lente’’

Philosophie 4

Il existe deux aspects critiques dans l’extensibilité des applications informatiques :

• Dans un mode mono-utilisateur : est-ce que le temps de réponse de cette année sera le même que celui de l’année prochaine (voir Philosophie – 3) ?
• Dans un mode multiutilisateurs: est-ce que le temps de réponse va être le même lorsque le nombre d’utilisateurs double, triple, quadruple …. ?

Si vous voulez tourner cela en une informelle et compréhensible phrase, Cary Millsap en inventa une bonne au Collaborate2009 : ’’Rapide Maintenant, Rapide plus tard’’

Philosophie 5

Résolution de problème avec Statspack / AWR

Quelque chose doit être en premier dans le ‘’Top 5 Timed Waits’’…. même lorsqu’il n’y a pas de problème de performance.

Philosophie 6

Tests de conformité :

• Avez-vous testé  en utilisant des données de qualité et  en utilisant toutes les différentes possibilités de données foireuses ?
• Est-ce que le code fait ce qu’il doit faire… et rien de plus ?

Philosophie 7

Il n’y a pas de secrets :

Au moins, il n’existe pas de secrets impliqués dans le bon fonctionnement d’une base de données. Occasionnellement, une information nouvelle et utile est découverte ; si elle est importante,  cette information va être documentée, discutée et validée en publique. (Elle ne sera pas nécessairement documentée sur Metalink, OTN ou tahiti.oracle.com mais ceci ne fait pas d’elle un secret).

A chaque fois que je vois des personnes faire des présentations sur les ‘’secrets’’,  il s’avère qu’elles ont généralement partagé leur temps entre citer la documentation, énoncer des évidences, faire des erreurs et offrir des généralités  qui nécessitent de prudentes justifications.

J’ai une règle générale simple pour les présentations : plus leur titre est glamour, branché et excitant, moins elles ont de  chances d’être utiles (mais ceci ne va pas m’empêcher de lire le résumé – juste au cas où).

Philosophie 8

Indexes B-tree vs. Indexes Bitmap: la différence la plus critique

• Un seul index B-tree vous permet d’accéder précisément à un petit volume de données
• C’est la combinaison de la présence d’un ensemble d’indexes Bitmap qui offre le même degré de précision.

Vous ne devrez pas comparer l’efficacité d’un index bitmap avec l’efficacité d’un index B-tree.

(Inévitablement c’est un peu plus subtile que ça ; vous pouvez créer quelques indexes B-tree peu précis pour éviter les problèmes de verrous liés aux contraintes du type clé étrangère, l’optimisateur peut combiner des indexes B-tree etc… ; mais si vous commencez sur cette base vous allez avoir une vue rationnelle sur comment utiliser les indexes bitmap).

Note : rappelez vous aussi, que les indexes bitmap introduisent des problèmes massifs de concurrence et d’autres surplus de maintenance ; si vous les trouvez dans des systèmes OLTP il y a alors de fortes chances qu’ils y soient en train de causer des problèmes.

Mis à jour 23 déc. 2009 : J’ai écrit un article complémentaire à cette note puisque le sujet que j’ai traité ici semble avoir causé quelques confusions.

Philosophie 9

Il existe une vieille blague concernant un ingénieur, un mathématicien et un philosophe voyageant ensemble dans un train de Londres (Angleterre)  à Cardiff (Pays de Galles)***

Lorsque le train traversa la frontière, l’ingénieur jeta un coup d’œil par la fenêtre  et s’exclama : ‘’Oh, regardez ! Les moutons Gallois sont noirs ‘’.

Le mathématicien répondit :’’Non ; tout ce que vous pouvez prétendre c’est qu’il y a au moins un mouton au pays de Galles qui est de couleur noir’’

Le philosophe les corrigea tous les deux : ‘’Messieurs, tout ce que vous pouvez dire c’est qu’il parait qu’au Pays de Galles, existe un mouton qui semble être noir sur un côté.’’

(Croyez-moi, en 1970, ceci était assez drôle)

La morale de cette histoire est : le meilleur point de vue à considérer lors de la résolution des problèmes d’une base de données est celui du mathématicien ; n’allez pas, comme l’a fait  l’ingénieur, vers des conclusions extrêmes basées uniquement sur une observation ; mais ne  soyez pas, comme l’a fait le philosophe, coincé sur d’aussi petits détails de conformité théorique si bien que des hypothèses raisonnables soient mises de côté.

*** Note: Pour ceux qui ne sont pas familiers avec la géographie du royaume uni (UK) : ‘’Le Royaume uni de la Grande-Bretagne et de l’Irlande du Nord ‘’ représente l’union de l’Angleterre, de l’Ecosse (la grande partie de la moitié haute de l’ile) et du Pays de Galles (le morceau de terre à gauche en excluant la petite et fine partie du bas).

Philosophie 10

La question la plus significative à vous poser lorsque vous êtes en train de réfléchir à l’ajout d’un nouvel index est :

‘’Est-ce que cet index va éliminer significativement plus de travail que ce qu’il va introduire (au moment où cela importe vraiment) ? ‘’

Quelques exemples de ‘’moments qui importent’’

• Insertion/suppression/mise à jour en vrac
• Système OLTP avec une activité hautement concurrente
• Des rapports fréquents nécessitant une très grande précision
• Test d’acceptation pour les effets collatéraux.

Philosophie 12

Voici une description utile que j’ai récemment entendue du philosophe Daniel Dennett:

Les critères d’une bonne propagande

1. Elle n’est pas un simple mensonge sans masque
2. Vous devez être capable de l’énoncer très sérieusement
3. Elle doit enlever le scepticisme sans exciter la curiosité
4. Elle doit apparaitre comme profonde

Il semble qu’elle décrit beaucoup de choses que notre industrie publie sur internet.

Philosophie 13

Si vous lisez un commentaire du genre “ X est une mauvaise idée’’ ceci ne veut pas dire ’’ un mécanisme qui, vaguement, n’est pas ‘X’ est une bonne idée’’.

Si, par exemple, je dis

    ’’Les histogrammes ne vont pas bien fonctionner sur des chaines de caractères ayant plus de 32 octets (bytes) de longueur et généralement identiques dans leurs premiers 32 octets ’’

 Ceci n’est absolument pas équivalent à :

’’ C’est une bonne idée de créer des histogrammes sur des chaines de caractères ayant moins de 32 octets (bytes) de longueur’’

Si on était dans un monde purement mathématique on aurait invoqué une logique symbolique en montrant ceci:

(A => B) <=> (¬B => ¬A)

Qui signifie que mon énoncé est équivalant à :
  ’’ si vous avez des histogrammes qui fonctionnent bien, alors les données ne sont pas du type chaine de caractères de plus de 32 octets dont, généralement, les 32 premiers octets possèdent des valeurs identiques’’

Evidement, sous Oracle, vous allez peut-être rencontrer certaines personnes, quelque part, ayant exactement le même type d’histogramme qui semble brillamment fonctionner pour elles.  Cependant, ceci  n’aurait été  possible que parce que l’optimisateur aurait  complètement raté son arithmétique si bien qu’il leur a fourni un plan d’exécution optimal pour une raison complètement erronée. Ces personnes doivent faire attention lors d’une prochaine migration ou prochaine application d’un nouveau patch dans le cas où l’optimisateur y est amélioré.

Philosophie 14

Paraphrasant Yogi Berra:

‘’Ce n’est pas comité jusqu’à ce que ça soit comité”

Si vous vous demandez pourquoi c’est important de rappeler cet inhabituel commentaire –il répond à la question suivante  communément posée:

‘’Est-ce que les redo logs contiennent aussi bien les données non comitées que celles déjà comitées ?’’

La réponse est : oui

Lorsqu’une session est en train de créer un redo change vector elle ne sait pas si elle va finir par faire un commit ou par annuler ce qu’elle a fait (un rollback). Par contre, une session doit être capable de stocker une large liste arbitraire de vecteurs de changements (change vectors) quelque part ; cette liste doit apparaître dans le redo log (idéalement d’une manière instantanée) si la session fait un commit – ainsi Oracle évite des retards lors du commit en déposant les vecteurs de changements dans le redo log au moment de leur création***.

*** Pas strictement correct à partir de 10g et plus où Oracle a introduit un effet retardataire dont le but est de réduire la compétition (la demande) pour l’allocation des redo et lors des copies de redo latches pour de ‘’petites’’ transactions

Philosophie 15

Si vous exécutez une requête qui est supposée retourner un seul enregistrement à partir d’une très large table, et, vous disposez d’un index approprié sur cette table, vous vous attendrez probablement à ce que l’optimisateur d’Oracle identifie l’index et qu’il l’utilise. Si vous changez votre requête de telle sorte qu’elle renvoie tous les enregistrements de cette table (sans tri) vous vous attendrez probablement à ce que l’optimisateur d’Oracle choisisse un full table scan.

Ceci conduit à la très simple idée qui est souvent ignorée

“Quelques fois il suffit juste d’un enregistrement supplémentaire pour passer d’un plan d’exécution utilisant un index scan  à un plan utilisant un full table scan”

Il existe un point où l’optimisateur change d’un accès indexé à un seul enregistrement vers un accès à toute la table pour tous les enregistrements.

Si vous êtes assez chanceux et le modèle de l’optimisateur est parfait il n’y aura aucun effet significatif sur la performance, bien sûr. Mais, nous ne sommes pas aussi chanceux que ça, et c’est pour cette raison que certaines personnes finissent par poser la question : ‘Comment le plan d’exécution est devenu subitement non performant, il n’y a eu aucun changement …. sauf pour un petit supplément de données?”. Tout ce qu’il faut c’est un seul enregistrement (que l’optimisateur reconnait) pour changer d’un plan d’exécution à un autre- et parfois l’optimisateur trouve le mauvais moment pour opérer ce changement.

Philosophie 17

Vous devez comprendre l’application ainsi que ses données

Une récente question dans OTN demandant  des conseils pour faire en sorte que la partie SQL suivante s’exécute rapidement :

delete from toc_node_rel rel
where   not exists (
                    select  *
                     from    toc_rel_meta meta
                     where   rel.rel_id = meta.rel_id
                   );

Voici une très petite liste de questions sur lesquelles il faut se concentrer lors de la recherche des solutions possibles. Il existe une colonne nommée rel_id  dans les deux tables ; les colonnes ayant un “id” dans leur nom tendent à être un tout petit peu spéciales, donc est-ce que ces colonnes sont :

a)  la clé primaire d’une table et la clé étrangère de l’autre (si c’est oui dans quel sens) ?

b)  les clés primaires des deux tables ?

c)  deux clés étrangères d’une clé primaire partagée d’une autre table ?

Jusqu’à ce que vous connaissiez la réponse à ces questions vous ne pouvez pas réellement progresser dans votre cheminement vers la bonne manière d’implémenter le besoin. Et, même lorsque vous aurez eu les réponses, ceci n’est encore qu’une seule étape vers la bonne direction et  un précurseur pour le prochain lot de questions – comme “est ce que les contraintes ont été déclarées et activées ? est-ce que certaines contraintes du type clés étrangères permettent la présence de nulls ? est-ce que certaines contraintes du type clés primaires ont été validées par des indexes non uniques ?’’ . Et nous ne sommes pas encore arrivés aux volumes absolus des données,  à leurs types de regroupements et au volume de données candidat à la suppression.

Note:

Vous pouvez argumenter sur l’existence ou pas des possibilités citées dans un système proprement conçu. Soyez libre de le faire ; ce n’est pas parce qu’un concept est faux en théorie qu’il ne va se produire en pratique.

 Note 2:

Pour  une impression beaucoup plus concrète c sur la petite liste de questions :

Pour l’option  (a) PK/FK – imaginez un cas très simple d’un modèle de commande, sommes nous en train d’essayer de supprimer les produits pour lesquels il n’y a pas de commandes, ou les commandes pour lesquelles on ne stocke pas de  produits (qui n’auraient pas du être introduites dans le système si nous l’avions implémenté correctement) ?

Pour l’option(b) PK/PK – imaginez que notre simple modèle de processus de commande possède une table séparée de livraison qui clone la PK de la table de commande, allons-nous essayer de supprimer les livraisons qui n’ont pas de commande (encore une fois elles ne devraient pas exister, mais qui a dit que ce système a été bien conçus et bien implémenté voir (a)) ?

Pour l’option (c) de la FK partagée – imaginez un processus de commande différent qui permet plusieurs lignes de commandes par commande et qui clone la PK des lignes de commande  pour produire un enregistrement dans la table de livraison, allons nous essayer de supprimer les livraisons qui n’ont pas de lignes de commande (encore une autre possibilité provenant d’un système mal conçu et mal implémenté – mais je suis sûr que je ne suis pas la seule personne à avoir vu de pareils systèmes et un tel code ) ?

Philosophie 18

Une question que je me suis posé récemment est celle-ci:

Quelle est le plus mauvais tort que peut engendrer  la publication d’un article autour d’une technique (caractéristique) d’Oracle  

1. Dire qu’un concept fonctionne alors qu’il ne fonctionne pas.
2. Dire qu’un concept ne fonctionne pas alors qu’il  fonctionne.
3. Dire qu’un concept fonctionne alors que dans certains cas il ne fonctionne pas.
4. Dire que qu’un concept ne fonctionne pas alors que dans certains cas il fonctionne.

 Je ne pense pas que c’est une question à laquelle il est facile de répondre et, évidement,  ce n’est pas plus facile lorsqu’on commence à considérer le nombre de cas pour lesquels une caractéristique fonctionne ou ne fonctionne pas (combien de cas représentent “quelques cas“), et la fréquence à laquelle les différents cas peuvent apparaître.

 Je ne suis pas sûr qu’il existe une réponse correcte à cette question, par contre, en terme d’impact (temps perdu) j’ai tendance à condamner les affirmations qui stipulent que quelque chose fonctionne alors qu’elle ne fonctionne pas – imaginez les deux extrêmes scénarios suivants :

•  Quelqu’un est en train d’essayer de résoudre un problème et trouve une publication qui offre une solution qui est supposée bien fonctionner – combien de temps va-t-il perdre en essayant de faire en sorte que cette solution fonctionne parce qu’il ‘’sait’’ qu’elle doit fonctionner.
• Quelqu’un est tombé par hasard sur une publication qui dit que le mécanisme qu’ils ont déjà implémenté avec succès ne fonctionne pas (ils ne vont pas rechercher l’article, bien évidement, parce qu’ils ont déjà résolu le problème). Ils ne vont  perdre aucun temps – sauf s’ils décident de trouver l’erreur dans l’article.

Il existe, inévitablement,  un contre argument. Quelqu’un serait peut-être en train de chercher une idée stratégique sur comment approcher un stade majeur de leur implémentation, et écarte une ligne d’attaque très utile parce qu’une publication stipule qu’elle (ligne d’attaque) ne va pas fonctionner.  Si cela devait arriver les conséquences auraient eu un impact majeur sur la qualité et sur l’extensibilité du produit final. Par contre, je préfère penser que quelqu’un qui est en train de réfléchir stratégiquement sur des options de conception ne serait pas enclin à négliger une idée sur la base d’un seul article sauf si celui-ci contient quelques très bons arguments et bonnes démonstrations.

Peut-être que le problème n’est pas tellement ce que dit l’article mais plutôt comment il le dit. Ce n’est pas grave de vous tromper ou (comme cela arrive plus fréquemment) de vous tromper partiellement à condition que vous disposiez d’une démonstration claire du travail que vous avez fait pour arriver à votre conclusion. Si vous avez fourni des évidences (et vous êtes arrivé à le présenter proprement) ceci va fournir aux lecteurs l’opportunité de faire des observations comme ‘’ l’exemple stocke des entiers dans des colonne du type varchar2()’’, ‘’ l’exemple utilise un index à deux colonnes, mais il va avoir une seule colonne’’, ‘’l’exemple utilise le tablespsace petitfichier, mais pas grandfichier’’ et, peut-être le plus important  ‘’l’exemple tourne sur 8.1.7.4 et pas sur 11.2.0.3’’

Literal, bind variable and adaptive cursor sharing: simplify them please!!!

When you find yourself very often typing the same set of sql statements you will end up by writing a sql script in which will be collected those sql statements. As such, you will have avoided repetitive sql typing.

When you find yourself very often writing the same set of phrases to explain an Oracle concept you will end up by writing a blog article in which will be collected those phrases. As such, you will be referring to that blog article instead of re-typing the same phrases.

When it is question of pros and cons of using literals, bind variables and cursor sharing, I believe, I’ve reached the point, where writing down my corresponding repetitive phrases become necessary.  So, please, take this article as a summary for me and for those who want to deepen a little bit their knowledge of these interacting concepts.

Let’s start now.

If you want to develop a non scalable and a non available Oracle application running slowly, then you have only one thing to do: “don’t use bind variable’’.  Oracle architecture is so that sharing memory (SGA-Library cache) represents a crucial aspect Oracle engineers have to know and to master. However, as it is always the case with Oracle database, despite this feature is very important it has  few drawbacks that are worth to be known. While bind variables allow sharing of parent cursors (SQL code) they also allow sharing of execution plans (child cursor). Sharing the same execution plan for different bind variables is not always optimal as far as different bind variables can generate different data volume. This is why Oracle introduces bind variable peeking feature which allows Oracle to peek at the bind variable value and give it the best execution plan possible. However, bind variable peeking occurs only at hard parse time which means as far as the query is not hard parsed it will share the same execution plan that corresponds to the last hard parsed bind variable. In order to avoid such situation Oracle introduces in its 11gR2 release, Adaptive Cursor Sharing allowing Oracle to adapt itself to the bind variable when necessary without having to wait for a hard parse of the query.

1.Using Literal variables

 SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'Y1';

 COUNT(*) MAX(COL2)
 ---------- -----------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 Plan hash value: 761479741

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
 -------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"='Y1')

 SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'N1';

 COUNT(*) MAX(COL2)
 ---------- ---------------------------------------------
 49998 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 Plan hash value: 3693069535
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |       |       |   216 (100)|          |
 |   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
 |*  2 |   TABLE ACCESS FULL| T1   | 55095 |  1614K|   216   (2)| 00:00:02 |
 ---------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - filter("FLAG"='N1')

 SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'Y2';

 COUNT(*) MAX(COL2)
 ---------- -----------------------------------------
 1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 Plan hash value: 761479741
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"='Y2')

 SQL> select /*+ literal_variable */ count(*), max(col2) from t1 where flag = 'N2';

 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
 49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

 Plan hash value: 3693069535
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |       |       |   216 (100)|          |
 |   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
 |*  2 |   TABLE ACCESS FULL| T1   | 55251 |  1618K|   216   (2)| 00:00:02 |
 ---------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - filter("FLAG"='N2')
 

I executed the same query using 4 different hard coded variables. For each literal variable I got the adequat execution plan. That’s very nice from this point of view. But, if I consult the library cache I will see the damage I have caused

 SQL> select sql_id, substr(sql_text,1,30), executions
   2  from v$sql
   3  where sql_text like '%literal_variable%'
   4  and   sql_text not like '%v$sql%';

 SQL_ID        SUBSTR(SQL_TEXT,1,30)          EXECUTIONS
 ------------- ------------------------------ ----------
 axuhh2rjx0jc7 select /*+ literal_variable */          1---> sql code is not re-executed
 c6yy4pad9fd0x select /*+ literal_variable */          1---> sql code is not shared
 45h3507q5r318 select /*+ literal_variable */          1---> the same sql seems for the CBO
 76q7p8q473cdq select /*+ literal_variable */          1---> to be a new sql statement
 

There is 1 record for each execution.  If you repeat the same sql statement changing only the value of the flag you will end up by having as much as records in v$sql as the number of different literal values you will used.

2. Using bind variables

So what will I point out if I prefer using bind variables instead of these literal ones?

 SQL> var n varchar2(2);
 SQL> exec :n := ’Y1’ ---> bind favoring index range scan
 SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

-------------------------------------
SQL_ID  8xujk8a1g65x6, child number 0

-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    54 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

SQL> exec :n := ’N1’ ---> bind favoring full table scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

-------------------------------------
SQL_ID  8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    54 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

SQL> exec :n := ’Y2’ ---> bind favoring index range scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

-------------------------------------
SQL_ID  8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    54 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

SQL> exec :n := ’N2’ ---> bind favoring table scan
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

-------------------------------------
SQL_ID  8xujk8a1g65x6, child number 0
-------------------------------------
Plan hash value: 761479741
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    54 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

Have you already pointed out something very clear? They (4 selects) share the same execution plan which is the plan that was generated for the first hard parsed bind variable ‘Y1’. As far as this one favors an index range scan access it shares that plan with all successive identical queries having the same sql_id. But spot with me how the library cache looks now very attractive

 SQL> select sql_id, substr(sql_text,1,30), executions
  2  from v$sql
  3  where sql_text like '%bind_variable%'
  4  and   sql_text not like '%v$sql%';

 SQL_ID        SUBSTR(SQL_TEXT,1,30)          EXECUTIONS
  ------------- ------------------------------ ----------
  8xujk8a1g65x6 select /*+ bind_variable */ co          4  ---> one sql code and 4 executions
 

Let me, at this particular step, make a break point.

• SQL statements using literal variables represent a non-sharable SQL which can get the best execution plans each time at a cost in optimization overheads (memory, CPU and latching).
• SQL statements using bind variables are represented by a unique sql_id (or a very small number of copies) in the library cache statement that are re-executed saving memory and CPU parse time. But this resource saving makes SQL statements sharing the same execution plan; that is the plan corresponding to the first bind value Oracle peeked at for the plan optimization during the hard parse time even if this plan is not optimal for the next bind variable value.

So what? Shall we use literal or bind variables? The best answer I have found to this question is that of Tom Kyte “If I were to write a book about how to build non-scalable Oracle applications, then Don’t use bind variables would be the first and the last chapter”.

3. Adaptive cursor sharing came to the rescue

Adaptive cursor sharing (ACS) is a feature introduced in the Oracle 11g release to allow, under certain circumstances, the Cost Based Optimizer (CBO) to adapt itself, peeks at the bind variable and generate the best plan possible without waiting for a hard parse to occur. Below is presented the ACS working algorithm:

So far we are using bind variables. Our SQL query is then bind sensitive. Ins’t it?

SQL> alter system flush shared_pool;

SQL> exec :n := 'N1';

SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 46667 |  1367K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

SQL> exec :n := 'Y1';

SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 46667 |  1367K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

Let’s see now after several executions of the same query if ACS kicks off or not. Remember that the first condition for ACS to kick off is that our cursor has to be bind sensitive. In the next query you should read the “I” prompts as Is_bind_aware , Is_bind_sensitive  and Is_shareable respectively:

SQL> @c:\is_bind_sens

SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ------------------- ---------- ---------------
8xujk8a1g65x6            0 N N Y 9686445671300360182    5         3724264953

After 5 executions the cursor is still not bind sensitive. In fact, to be so, the bind variable should have histograms

SQL> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>'FOR COLUMNS flag SIZE AUTO',no_invalidate=>FALSE);

SQL> exec :n := 'Y1';
SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

We got an index range scan for the variable that favors an index range scan. That’s fine. Let’s see now if our cursor is bind sensitive

SQL> @c:\is_bind_sens

SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ------------------- ----------- ---------------
8xujk8a1g65x6            0 N Y Y 9686445671300360182   1         3625400295

Yes it is. But it is not yet bind aware.

SQL> exec :n := 'N2';

SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

I executed the query with the bind variable that favors a full table scan but I shares the preceding execution plan. Let’s see if our cursor is bind aware

SQL> @c:\is_bind_sens

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
8xujk8a1g65x6            0 N Y Y 9686445671300360182                               2      3625400295

Still not. The query needs a warm up period before being bind aware.  So let’s execute again

SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

SQL_ID  8xujk8a1g65x6, child number 1

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50894 |  1491K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

Finally we got a full table scan. Is this due to ACS?

SQL> @c:\is_bind_sens

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
8xujk8a1g65x6            0 N Y Y 9686445671300360182                               2      3625400295
8xujk8a1g65x6            1 Y Y Y 9686445671300360182                               1      3724264953

Yes it is. Look how the second line (child number 1) is bind sensitive, bind aware and shareable. This is how ACS works.

Now, if I execute the same query with a bind variable that favors an index range scan, ACS will give me the INDEX RANGE SCAN plan

SQL> exec :n := 'Y2';

SQL> select /*+ bind_variable */ count(*), max(col2) from t1 where flag = :n;

SQL_ID  8xujk8a1g65x6, child number 2
-------------------------------------
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> @c:\is_bind_sens

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
8xujk8a1g65x6            0 N Y N 9686445671300360182                               2      3625400295
8xujk8a1g65x6            1 Y Y Y 9686445671300360182                               2      3724264953
8xujk8a1g65x6            2 Y Y Y 9686445671300360182                               1      3625400295

Spot how a new child cursor (child number 2) has been created and it is bind sensitive, bind aware and shareable. Playing with those bind variables values combinations I ended up by having two child cursors, one for (3724264953) full table scan, and the other one (3625400295) for the index range scan that are both shareable. Thanks to these two child cursors (until they are flushed out, or something disturbs their good working), the CBO will be alternating between the two executions plans giving each bind variable its corresponding execution plan.

For those who want to play with this example, you can use Dominic brooks model reproduced below:

create table t1
(col1  number
,col2  varchar2(50)
,flag  varchar2(2));

insert into t1
select rownum
,      lpad('X',50,'X')
,      case when rownum = 1
then 'Y1'
when rownum = 2
then 'Y2'
when mod(rownum,2) = 0
then 'N1'
else 'N2'
end
from   dual
connect by rownum <= 100000;

create index i1 on t1 (flag);

And the is_bind_sens.sql script is

select sql_id
,      child_number
,      is_bind_aware
,      is_bind_sensitive
,      is_shareable
,      to_char(exact_matching_signature) sig
,      executions
,      plan_hash_value
from   v$sql
where  sql_text like '%bind_variable %'
and    sql_text not like '%v$sql%';

SPM baseline selection: how it works?

In my last post about SQL Plan Management (SPM) I investigated the behavior of Adaptive Cursor Sharing (ACS) feature in the presence of SPM baselines. I will now start focusing my interests on the interaction between the CBO and the SPM plan selection steps using the model of the last post.  During this entire blog article I will be working with an existing SPM baseline which contains two accepted and enabled plans as shown below:

 SIGNATURE            SQL_HANDLE               PLAN_NAME                      ENA ACC  PLAN_ID    DESCRIPTION
 -------------------- ------------------------ ------------------------------ --- --- ---------- -------------------
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES 1634389831 FULL TABLE SCAN(T1)
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES 2239163167 INDEX RANGE SCAN(i1)
 

And I will try to investigate the following issues

1. SPM contains two plans (1634389831, 2239163167) and CBO comes up with a plan that matches one of these existing SPM plans
2. SPM contains two plans (1634389831, 2239163167) and I drop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the bind variable value that favor the index range scan
3. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline.
4. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline but I drop also the existing index i1 (making the plan 2239163167 not anymore reproducible)

In order to follow my investigation without a lot of difficulties, please would you mind to remember that plan id finishing by 831 (1634389831) corresponds to T1 Table FULL SCAN while the plan id finishing by 167 (2239163167) corresponds to the index i1 RANGE SCAN.

Let’s now embark in the investigations starting by case number 1.

Case1: SPM contains two plans and CBO comes up with a plan that matches one of these existing SPM plans

I executed my query using bind variable = ‘Y1’ which is the bind variable that favor the i1 index range scan. The CBO should come up with the index i1 range scan plan matching the existing plan in the baseline (2239163167). Let’s see how the selection occurs via the 10053 trace file

 SPM: statement found in SMB

The first thing the CBO does is signaling that it realizes the presence of a SPM baseline. Then, follows the classical CBO query optimization

 ****************
 QUERY BLOCK TEXT
 ****************
 select count(*), max(col2) from t1 where flag = :n

 Access path analysis for T1
 ***************************************
 SINGLE TABLE ACCESS PATH
 Single Table Cardinality Estimation for T1[T1]

 Table: T1  Alias: T1
 Card: Original: 100000.000000  Rounded: 9  Computed: 9.10  Non Adjusted: 9.10
 Access Path: TableScan
 Cost:  275.38  Resp: 275.38  Degree: 0
 Cost_io: 272.00  Cost_cpu: 31121440
 Resp_io: 272.00  Resp_cpu: 31121440

 Access Path: index (AllEqRange)
 Index: I1
 resc_io: 2.00  resc_cpu: 18993
 ix_sel: 0.000091  ix_sel_with_filters: 0.000091
 Cost: 2.00  Resp: 2.00  Degree: 1
 Best:: AccessPath: IndexRange                 ---> Best Access Path Index I1 Range Scan with cost =2
 Index: I1
 Cost: 2.00
 ***************************************
 

Where the CBO found that the best access path is the Index Range scan (Index I1 with a cost of 2). However, as far as the CBO has already signaled the presence of SPM plan it knows that it is constrained. It can’t decide to use the plan it comes up with without comparing it to the existing SPM plans. This is why we see the following lines into the same 10053 trace file

 SPM: cost-based plan found in the plan baseline, planId = 2239163167 ---> 167 is the index range scan
 SPM: cost-based plan was successfully matched, planId = 2239163167 --> CBO comes up with a plan that matches a SPM plan
 

That’s all for this case:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case2: SPM contains two plans but Idrop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the index bind variable value

The 10053 trace file in this case looks as follows

 SPM: statement found in SMB

****************
QUERY BLOCK TEXT
****************
select count(*), max(col2) from t1 where flag = :n

Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 1  Computed: 0.50  Non Adjusted: 0.50
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Best:: AccessPath: TableScan        ---> CBO comes up with a FULL TABLE SCAN plan having cost = 273
Cost: 272.96
***************************************

In the absence of the i1 index, the CBO produces a FULL TABLE SCAN as the best access path. As far as this generated plan exists in the baseline it will be used as shown below:

SPM: cost-based plan found in the plan baseline, planId = 1634389831 ---> T1 FULL TABLE SCAN plan
SPM: cost-based plan was successfully matched, planId = 1634389831 --> CBO comes up with a plan that matches a SPM plan

That’s all for this case also:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case 3: SPM contains two plans and I add a new index i2 that will produce a new plan which is not in the SPM baseline

In this case I executed my query with a bind variable that favors a fast full scan of the newly created index i2.  The CBO comes up with a plan that doesn’t match any plan in the SPM baseline. This is confirmed here below:

 Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 50563  Computed: 50563.46  Non Adjusted: 50563.46
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Access Path: index (index (FFS))
Index: I2
resc_io: 44.00  resc_cpu: 18103823
ix_sel: 0.000000  ix_sel_with_filters: 1.000000

Access Path: index (FFS)
Cost:  44.56  Resp: 44.56  Degree: 1
Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (AllEqRange)
Index: I1
resc_io: 995.00  resc_cpu: 26806643
ix_sel: 0.505635  ix_sel_with_filters: 0.505635
Cost: 995.83  Resp: 995.83  Degree: 1
******** End index join costing ********

Best:: AccessPath: IndexFFS    ---> I2 Index FFS of cost 44 is the best access path
Index: I2
Cost: 44.56
***************************************

The CBO comes up with an INDEX FAST FULL SCAN on the newly created index i2. Sure this will not match an existing plan baseline because we keep repeating that we have only two enabled and accepted plan baselines one for index i1 range scan and the other one for t1 table full scan. It is then very interesting to see how the CBO will react in such a situation.

SPM: planId's of plan baseline are: 2239163167 1634389831
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 2239163167 --> I1 INDEX RANGE SCAN (167)

Since the CBO realizes that it has produced a non matching plan,  It will try to reproduce the existing SPM plan baseline using outline hint and session OFE.

Why the CBO tries to reproduce one of the existing SPM plan?  And why the CBO started by trying to reproduce the index range scan plan first? Why not simply use one of the existing SPM plan?

When the CBO comes up with a plan that is not in the SPM baseline, it considers that something has changed and it is not anymore sure that the existing SPM plans are still reproducible. It also considers that even if the SPM plans are reproducible their corresponding “stored” cost might have changed. This is why, as will we see later, the CBO will not only tries to reproduce the I1 index range scan plan but it will also try to reproduce the full table scan plan in order to compare their costs using current optimizer parameters and table/index statistics. If the two plans are reproducible, then the one having the best current re-computed cost will be used.

We can see this in the 10053 trace file

SELECT /*+ INDEX_RS_ASC ("T1" "I1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MHOURI"."T1" "T1" WHERE "T1"."FLAG"=:B1

Spot how the CBO is injecting the I1 index range scan hint in order to reproduce the SPM index i1 range scan plan

Access path analysis for T1

***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 25000  Computed: 25000.00  Non Adjusted: 25000.00

Access Path: index (AllEqRange)
Index: I1
resc_io: 492.00  resc_cpu: 13254598
ix_sel: 0.250000  ix_sel_with_filters: 0.250000
Cost: 492.41  Resp: 492.41  Degree: 1

Best:: AccessPath: IndexRange
Index: I1
Cost: 492.41  Degree: 1  Resp: 492.41  Card: 25000.00  Bytes: 0
***************************************

SPM: planId in plan baseline = 2239163167, planId of reproduced plan = 2239163167 ---> INDEX_RS plan reproduced
SPM: best cost so far = 492.41, current accepted plan cost = 492.409691           ---> cost = 492
***************************************

Since it has successfully reproduced the I1 index range scan plan and recomputed its corresponding cost, the second step, as indicated above, will be to reproduce the full table scan plan (1634389831) and it corresponding cost

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831  ---> T1 FULL TABLE SCAN (831)

CBO succeed to reproduce the FULL table scan using the plan outline hint and calculated the “new” cost to be the 272 as shown below:

SPM: planId in plan baseline = 1634389831, planId of reproduced plan = 1634389831
SPM: best cost so far = 272.96, current accepted plan cost = 272.961944

So far, the CBO succeeded to reproduce the two SPM plans baseline and to calculate their corresponding cost. It found that the FULL TABLE SCAN cost (272) is better than the cost of the I1 INDEX RANGE SCAN (492). As such, it has decide to use the SPM FULL TABLE SCAN plan.

The above preceding 10053 trace file lines show clearly  how accepted SPM plans enter in competition when the generated CBO plan is not in the SPM baseline. The CBO doesn’t rely on the cost of the plan stored in the baseline. It has to reproduce all enabled and accepted plans and compares their costs using the current session CBO parameters. 

Now that the CBO succeeded to reproduce both plans and decide to use the FULL TABLE SCAN which is the plan with the smaller cost, there is bizarrely, a supplementary step that consist of re-parsing to generate the best costed reproduced plan i.e. the T1 FULL TABLE SCAN as shown below:

SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan, planId = 1634389831

Frankly speaking I didn’t understand the need of this last re-parsing step.

That’s all for this case also:  when the cost-based generated plan doesn’t match one of the existing SPM plans, the CBO will reproduce all the SPM plans and compare their cost. The reproduced plan having the best cost will be used.

Case4: SPM contains two plans and I added a new index i2 that will produce a new plan but I also dropped the existing index i1 (making the plan 2239163167 not anymore reproducible)

In this case, I dropped the i1 (flag) index and created a new index i2 (flag, col2) and executed the query with a bind variable that usually was favoring the INDEX i1 RANGE SCAN. Before exploring the corresponding 10053 trace file, let me tell you that the CBO in this configuration will comes up with a new plan using i2 INDEX RANGE SCAN which doesn’t exist in the SPM baseline as shown below:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
kxsbbbfp=0d019fe0  bln=32  avl=02  flg=05
value="Y1"   -----> this bind value was favoring I1 INDEX RANGE SCAN before I dropped this index
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Table: T1  Alias: T1
Access Path: TableScan                     ----> Full table scan ----> cost 272
Cost:    275.38  Resp: 275.38  Degree: 0
Cost_io: 272.00  Cost_cpu: 31121440
Resp_io: 272.00  Resp_cpu: 31121440

Access Path: index (index (FFS))           ----> I2 index fast full scan ----> cost 44
Index: I2
Access Path: index (FFS)
Cost:  45.97

Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (Index Only)           ----> I2 index range scan  ---> cost 3
Index: I2
resc_io: 3.00  resc_cpu: 21564
ix_sel: 0.000005  ix_sel_with_filters: 0.000005
Cost: 3.00  Resp: 3.00  Degree: 1

Best: Access Path: Index Range            ----> best access path index range scan I2
Index: I2
Cost: 3.00
***************************************

Now that the CBO comes up with a non matching plan, as always, it will start trying to reproduce all  SPM plans. But this time, despite the index i1 range scan plan is not reproducible because I dropped that i1 index, the CBO will, nevertheless, try to reproduce this plan as shown below:

SPM: plan Ids of plan baseline are: 1634389831 2239163167
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 1634389831

During the first round, CBO is trying to reproduce the full table scan

SPM: plan Id in plan baseline = 1634389831, plan Id of reproduced plan = 1634389831
SPM: best cost so far = 275.38, current accepted plan cost = 275.379078

I will skip the  part showing  the FULL TABLE SCAN reproduction because the CBO has successfully reproduced it and there is no added value to present it here.

The next step in this round is to reproduce the I1 index range scan which is in fact impossible (How the CBO can ignore it?)

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167

Look below how the CBO is hinting an index I2 while trying to reproduce a plan with index I1 that is not any more present in the database

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX_RS_ASC ("T1" "I2") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1

And how naturally it will be impossible to reproduce the I1 INDEX RANGES SCAN plan

SPM: plan Id in plan baseline = 2239163167, plan Id of reproduced plan = 3187078153
SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167  ---> I1 INDEX RANGE SCAN
plan_baseline hintset    :

hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
hint num  3 len 22 text: DB_VERSION('11.2.0.1')
hint num  4 len  8 text: ALL_ROWS
hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num  6 len 49 text: INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))
SPM: generated non-matching plan:

The CBO failed to reproduce the I1 index range scan and it produces an I2 index range scan instead. However the CBO is still considering that the game is not over and that there is a second chance. This is why we see the following lines about round 2 in the same  10053 trace file

SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167  ---> I1 INDEX RANGE SCAN

And the evident conclusion of a non reproducible plan even in this round 2

SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167
plan_baseline hintset    :
hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

SPM: generated non-matching plan:

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    42 |           |
| 1   |  SORT AGGREGATE    |         |     1 |    54 |       |           |
| 2   |   INDEX RANGE SCAN | I2      |   24K | 1318K |    42 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 2583336616
plan_hash_2    : 3187078153 ----> I2 INDEX RANGE SCAN

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG" "T1"."COL2"))  ---> (FLAG,COL2) are the I2 index columns
END_OUTLINE_DATA
*/
------- END SPM Plan Dump -------

Finally, after two impossible rounds, the CBO recognizes, what it should have recognized much earlier, that it is impossible to reproduce the I1 index range scan and decided to use the unique SPM plan it succeeded to reproduce .i.e. T1 TABLE FULL SCAN not without a extra re-parsing step

SPM: re-parsing to generate selected accepted plan, plan Id = 1634389831 ---> FULL TABLE SCAN

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("T1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   275 |           |
| 1   |  SORT AGGREGATE     |         |     1 |    54 |       |           |
| 2   |   TABLE ACCESS FULL | T1      |     1 |    54 |   275 |  00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 3724264953
plan_hash_2    : 1634389831
Peeked Binds

============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=178
char format=1
max length=32
value=Y1

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

Bottom line: when using SPM baseline to guarantee plan stability, be warn that when you have several enabled and accepted plan for the same SQL matching signature and, if for any reason those plans become non reproducible,  you might pay a parsing time penalty because the CBO will use two rounds trying to reproduce all SPM plans – even though they are impossible to reproduce–