Discussion :

[Lana.Bauer]

Bonjour,

Je vous présente ce tutoriel intitulé :

Optimisation des LDD

À partir de sa version 11g, Oracle a introduit une technique très performante qui permet l'optimisation des opérations d'ajout de colonnes sur une table. Lorsque vous ajoutez à une table une colonne « not null » et qu'en même temps vous attachez une valeur par défaut à cette colonne, alors une nouvelle technique d'optimisation LDD a lieu permettant à cette opération d'être instantanée. Comment cela est-il possible lorsque la table ainsi modifiée possède des millions d'enregistrements qui doivent voir leur nouvelle colonne ajoutée mise à jour avec la valeur par défaut ? Et est-ce que cette nouvelle technique d'optimisation des opérations LDD a été implémentée sans aucun effet secondaire que nous devrions connaître avant son utilisation ? C'est ce que je vais vous expliquer dans cet article.

Bonne lecture !

Vous aussi, vous pouvez contribuer avec nous !

[skuatamad]

Merci pour la publication de l'article, et surtout merci à Mohamed pour la rédaction.

Je vous précise que certains plan d'exécution n'apparaissent pas (version html et pdf).
Si vous pouviez vous rapprocher de Mohamed afin de compléter les éléments manquant qui sont à ce jour un frein à la bonne lecture de l'article.

[Chtulus]

Salut skuatamad,

Je vous précise que certains plan d'exécution n'apparaissent pas (version html et pdf).

Soit cela a été résolu ( 'suis un peu en retard sur le coup), soit c'est de ton côté, tout fonctionne bien

[Elros]

Merci énormément pour le partage de cette astuce très utile !

[skuatamad]

Soit cela a été résolu ( 'suis un peu en retard sur le coup), soit c'est de ton côté, tout fonctionne bien

Salut Chtulus,

Merci de t'intéresser au sujet, peut être que ça vient de moi, mais je ne pense pas (testé sur plusieurs navigateurs), exemple paragraphe 2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

10.2.0.4.0> select * from table(dbms_xplan.display_cursor);

L'absence de la fonction NVL dans la partie prédicat couplée

Le plan n'est pas affiché entre la commande d'affichage et le début de l'analyse contrairement au paragraphe 3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

En dessous le plan est affiché dans une image.

Certes on a les commandes pour faire afficher les plans, mais je pense que Mohamed souhaitait faire afficher tous les plans dans le tuto pour une bonne lecture.
L’analyse a plus de sens quand le plan est affiché tout de même.

[Lana.Bauer]

Je viens de vérifier tout le tutoriel, pourriez-vous me confirmer que les plans d’exécution s'affichent bien sur le pdf ?

Merci

[Chtulus]

Salut skuatamad, Lana,

Pour moi tout est OK

[skuatamad]

Bonjour Lana,

Merci pour votre retour et pour les corrections apportées sur le chapitre "2. Le concept" (version pdf et html)

Cependant, il reste des manques sur les chapitres suivant (version pdf et html):

Chapitre "3-2. Sur la colonne C_DDL indexée"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Il y a une bonne nouvelle ici : l'index est utilisé. La fonction cachée NVL n'est pas appliquée sur la colonne C_DDL lorsque celle-ci provient de l'index. Cela explique pourquoi l'index a bien été utilisé par le CBO.

puis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select /*+ gather_plan_statistics */ count(1) from t1 where n1= 100 and C_DDL
= 42;

Observez comment la fonction NVL a été également appliquée sur la colonne même lorsque la table t1 est visitée via indexrowid.

Enfin chapitre "4. Oracle 12c et l'optimisation DLL pour les colonnes NULL"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from table(dbms_xplan.display_cursor);

Cependant, afin d'implémenter l'optimisation LDD pour les colonnes pouvant être nulles, une légère complexité a été introduite par rapport à

Ma vérification a été assez rapide, j'espère qu'elle est exhaustive.
J'espère que vous pourrez apporter ces corrections également.
Merci pour le suivi.

[Mohamed.Houri]

Bonjour

Merci skuatamad pour vos remarques qui depuis le début ont été très justement pertinentes et correctes.

Après une rapide vérification, il me semble que tous les plans d'exécution sont maintenant présents dans le document html(je n'ai pas vérifié la version pdf)

Bien Cordialement
Mohamed Houri
www.hourim.wordpress.com

[skuatamad]

Je confirme que tout a été corrigé, merci à Lana pour le suivi.

[Pomalaix]

Bravo pour cet article qui décortique le sujet de manière approfondie, avec une mise en forme rigoureuse, et sans tomber dans le jargon franglais !


Quelques commentaires néanmoins (sinon on ne me reconnaîtrait pas, hein ! )


La formulation suivante est maladroite, pour ne pas dire fausse, puisque justement vous vous attachez à démontrer que cette valeur n'est pas stockée en table.
"la requête montre que la totalité des colonnes C_DDL a été mise à jour avec sa valeur par défaut 42."
On aurait pu relever que la taille de la table n'a pas augmenté quand on a ajouté la colonne avec sa valeur nulle.


Par ailleurs, je trouve que la démonstration concernant la présence physique de la valeur par défaut dans l'index est un peu détournée, et pas spécialement probante.
Pour ma part, j'aurais plutôt insisté sur sa durée de création, mais surtout sur sa taille. Un index de 47 Mo doit bien stocker quelque chose de réel.
Mais à vrai dire, un vidage de bloc d'index aurait été encore plus convaincant.


J'ai relevé quelques bricoles sur la forme :
* à plusieurs reprises, vous parlez de millisecondes alors qu'il s'agit de centièmes de seconde
* en fin de section 2, une coquille : "ne vas pas" au lieu de "ne va pas"
* en fin de section 3.2 "très confiant de..." n'est guère français
* en fin de section 5 "sensé" au lieu de "censé"

Les plans d'exécution V11 comportent parfois des incohérences au niveau des temps d'exécution A-Time, par exemple à la section 3.2.
En effet, on a le parcours d'index qui se fait en 75 centièmes, pour un temps total de la requête de 47 centièmes.


Au passage, je suis tombé sur l'erreur suivante en créant la table de démonstration.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORA-30009: Not enough memory for CONNECT BY operation

Ce que j'ai contourné par le bricolage suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter session set "_SMM_MAX_SIZE"=200000000;

[Mohamed.Houri]

Merci Pomalaix pour vos remarques

Je prends souvent du temps pour relire les articles mais à chaque fois je suis persuadé qu'il reste quelque chose à revoir. Les fautes de style et d'orthographe que vous avez relevées en sont la preuve.

La formulation suivante est maladroite, pour ne pas dire fausse, puisque justement vous vous attachez à démontrer que cette valeur n'est pas stockée en table.
"la requête montre que la totalité des colonnes C_DDL a été mise à jour avec sa valeur par défaut 42."
On aurait pu relever que la taille de la table n'a pas augmenté quand on a ajouté la colonne avec sa valeur nulle.

Oui bien vu. J'aurai peut-être mieux fait d'utiliser suggère et d’enchaîner tout de suite avec le reste à savoir:

"ce qui signifie, qu’en réalité, cette colonne n’a pas été mise à jour, et c’est la raison pour laquelle, Oracle est en train de la remplacer par sa valeur par défaut 42)"

Je pense aussi que c'est le résultat de la requête qui suggère et non la requête :-)

Par ailleurs, je trouve que la démonstration concernant la présence physique de la valeur par défaut dans l'index est un peu détournée, et pas spécialement probante.
Pour ma part, j'aurais plutôt insisté sur sa durée de création, mais surtout sur sa taille. Un index de 47 Mo doit bien stocker quelque chose de réel.
Mais à vrai dire, un vidage de bloc d'index aurait été encore plus convaincant.

Oui également bien vu.

Les plans d'exécution V11 comportent parfois des incohérences au niveau des temps d'exécution A-Time, par exemple à la section 3.2.
En effet, on a le parcours d'index qui se fait en 75 centièmes, pour un temps total de la requête de 47 centièmes.

J'ai fait plusieurs essais sur plusieurs versions d'Oracle avec plusieurs tailles de bloque et il est fort possible que ce que vous avez relevé soit une erreur. Par contre, sachez bien que cette incohérence est tout à fait possible. J'ai déjà rencontré des cas semblables en production qui ont retenu mon attention si bien que j'ai posé la question à Christian Antognini. Vous trouverez sa réponse ici

Bien Cordialement