Le 10 et 11 septembre 2007 dans les locaux d’Oracle à Colombes, a eu lieu un
séminaire :
Cost Based Optimisation, Indexing Strategies, Explain Plan avec Jonathan Lewis.
II. Qui est Jonathan Lewis
Jonathan Lewis est un spécialiste mondial reconnu. Cela fait environ 22 ans
qu’il travaille dans le monde informatique, et 19 sur les technologies d’Oracle.
Oracle étant un vaste océan, il a décidé de se consacrer essentiellement à la
question du Tuning. Site internet et Blog
Il est également ORACLE ACE, et enfin élu auteur de l’année 2006. Tout cela
démontrant qu’en plus d’être un expert reconnu sur les technologies Oracle, il est un
fervent animateur de cette même communauté.
Mr Lewis a donc abordé la question du Tuning du code applicatif, bien que le
séminaire n’ait duré que deux jours, le programme fut copieux :
III. Le programme
Premier Jour :
- Basic Cost arithmetic : Ici il s’agit de poser les principes fondamentaux du
CBO (Cost Based Optimisation). Comme aime à le rappeler Jonathan,
« fondamental » ne veut pas dire simple mais cela représente les fondations pour
comprendre comment « fonctionne » le CBO. Ici nous abordons différentes notions et
paramètres : l’optimiseur, les statistiques, IO Costing et CPU Costing, le clustering
factor, les jointures, MBR (Multi Block Read Count), la sélectivité …
- Join Mechanisms : Ici il s’agit de détailler les principes des différentes
jointures sous Oracle : NL, Hash Join, Merge Join. Cette précision à pour but évident
de bien comprendre ces mécanismes pour pouvoir au mieux optimiser la jointure de
ces tables et des tris.
Par exemple : Une Jointure Nested Loop est une jointure entre deux
tables, ou Oracle accède, grâce à chaque ligne de la première table, aux
données de la table INNER. Il est donc évident que pour les nested Loops,
que nous retrouvons fréquemment sous PeopleSoft, il est très critique
qu’Oracle puisse accéder aux données rapidement de la table INNER.
- Selectivity and Hints : Après avoir posé les bases de la sélectivité, Mr
Lewis rappelle que c’est un critère important permettant de diminuer le nombre de
lignes renvoyé soit à la ligne parente soit au résultat. Diminuant de facto les tris et
donc le temps d’exécution. Il passe ensuite sur les prédicats. Il termine enfin sur les
Hints, qui permettent de donner des ordres directement à l’optimiseur. Une partie de
ce chapitre est consacré à un élément important mais souvent peu détaillé chez
Oracle, the Sanity check ou la ré écriture d’une requête pour améliorer son
comportement.
Un Hint est un ordre obligatoire mais qui peut ne pas être exécuté si :
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
PL/SQLRelease 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> altersessionset"_optimizer_ignore_hints"=true ;
Session modifiée.
SQL> show parameter _optimizer
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
_optimizer_ignore_hints booleanTRUESQL>
Ou si le Hint n'est pas applicable.
- Finding executions Plans : Ici il s'agit de déterminer par différents moyens l'Explain plan
Les différents moyens sont autotrace, explain plan event 10046 and TKPROF, v$SQL_PLAN, Statspack- Awr et les events 10132 et 10053.
A ce titre je me permets de vous indiquer le tutoriel DVP sur ce sujet :
- TKPROF et Explain Plan
- Statspack
Permettant de déterminer un plan d'exécution pour une exécution en Série ou Parallèle.
- Reading executions Plans : Après avoir déterminé les plans, il faut bien entendu lire ce plan pour en tirer les informations nécessaires.
Ainsi il existe deux règles qui régissent la lecture, tout d'abord un parent peut avoir un seul enfant qui effectue le travail, ou alors les parents peuvent avoir plusieurs enfants effectuant chacun une tâche.
Donc lors des analyses toujours se poser la question suivante, qu'est ce qu'une ligne demande et que renvoie-t-elle. Se focaliser sur une étape à la fois.
Second Jour : - Problems with plans : : En effet Jonathan attire notre attention sur les erreurs induites par les Explains Plans. Afin d'avoir un Explain Plan le plus prés possible de la vérité il faut aller le chercher dans v$sql_plan. De plus les traces peuvent ne pas contenir les statistiques.
Le formatage obtenu grâce à TKPROF peut également induire des " oublis " des statistiques ou, pire, avoir de fausses statistiques.
Par exemple, un DBA reçoit une requête qui pose problème mais oublie le fait que la base est en VPD, FGAC ou RLS. Il peut également y avoir un Trigger On Logon qui effectue un set current_schema.
- Use of indexes : L'utilisation ou non des index est toujours un sujet primordial chez Oracle.
Par exemple, maintenir une table avec index est deux fois plus couteux que sans.
Mise en place de la table et insertion :
SQL> createtable dvp (a number) ;
Table créée.
Ecoulé : 00 :00 :00.09
SQL> declare i number;
2 begin
3 for i in 1..100000
4 loop
5 insertinto dvp values (i ) ;
6 endloop ;
7 commit ;
8 end ;
9 /
Procédure PL/SQL terminée avec succès.
Ecoulé : 00 :00 :04.97
Mise à jour sans index :
QL> update dvp set a = 20 ;
100000 ligne(s) mise(s) à jour.
Ecoulé : 00 :00 :03.85
Statistics----------------------------------------------------------
192 recursive calls
239625 db block gets
480 consistent gets
0 physical reads
51683740 redo size
478 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL> commit ;
Validation effectuée.
Ecoulé : 00 :00 :00.32
Mise à jour avec index :
SQL> createindex i_dvp on dvp (a ) ;
Index créé.
Ecoulé : 00 :00 :00.96
SQL> update dvp set a = 21 ;
100000 ligne(s) mise(s) à jour.
Ecoulé : 00 :00 :06.44
Statistics----------------------------------------------------------
711 recursive calls
509578 db block gets
982 consistent gets
199 physical reads
71022892 redo size
484 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100000 rows processed
- Index Myths : Ici il s'agit de tordre le cou à certains mythes dans le monde Oracle.
Index Myths
L'espace d'un index n'est jamais ré utilisé
Avoir la colonne la plus sélective positionnée en première position.
Les petites tables ne doivent jamais être indexées.
Le mode Logging ne produit jamais de Redo.
Les index doivent être reconstruits fréquemment.
Un index est inutilisable si la première colonne n'est pas utilisée
Par exemple concernant la dernière proposition :
SQL> createtable dvp ( a varchar2(10), b number ) ;
Table créée.
Ecoulé : 00 :00 :00.31
SQL> declare i number;
2 begin
3 for i in 1..100000
4 loop
5 insertinto dvp values ('DVP.COM', i ) ;
6 endloop ;
7 commit ;
8 end ;
9 /
Procédure PL/SQL terminée avec succès.
Ecoulé : 00 :00 :05.16
SQL> createindex i_dvp on dvp (a,b) ;
Index créé.
--- Analyse des tables et indexesSQL> select * from dvp where b=25 ;
Ecoulé : 00 :00 :00.16
Execution Plan----------------------------------------------------------
0 SELECTSTATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=11)
1 0 INDEX (SKIP SCAN) OF 'I_DVP' (INDEX) (Cost=2 Card=1 Bytes=
11)
- Describing data : Les points les plus importants abordés dans ce chapitre sont les statistiques, les histogrammes, les outlines, profiles et conseils. Oracle recommande de donner le maximum d'information à son optimiseur pour qu'il puisse prendre le meilleur chemin.
Le design produit au début du projet est un élément important, voire même déterminant
IV. Impression
Outre l'aspect très technique de ce type de conférence, ce qui est surtout très intéressant est le retour d'expérience sur tous les environnements possible, permettant ainsi non seulement de détailler la théorie mais également de confronter ce que font différentes entreprises via un seul interlocuteur.
Une autre impression qui fut amplement confirmé, plus l'interlocuteur est techniquement très fort, plus il est gentil et abordable.
Ce document est issu de http://www.developpez.com et reste la propriété exclusive de son auteur.
La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.
