Cost Based Optimisation par Jonathan Lewis

Date de publication : 19/09/2007 , Date de mise à jour : 19/09/2007

Cost Based Optimisation par Jonathan Lewis. Conférence organisée par Oracle France.

I. Introduction
II. Qui est Jonathan Lewis
III. Le programme
IV. Impression

I. Introduction

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

En outre il est l’un des directeurs d’UKOUG : UK Oracle User Group. Organisation, qui, tout comme l’AUFO : Association des utilisateurs francophone d’Oracle, effectue la promotion des utilisateurs des technos Oracle : (Oracle, PeopleSoft, Siebel …).

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/SQL Release 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> alter session set "_optimizer_ignore_hints"=true ;

Session modifiée.

SQL>  show parameter  _optimizer

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
_optimizer_ignore_hints              boolean TRUE
SQL>

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

Il existe deux scripts :

serveur-oracle: /home/oracle>ls -ltr $ORACLE_HOME/rdbms/admin/utlxpls.sql
-rw-r-----   1 oracle   dba            1650 Feb 27 2002  /oracle/rdbms/admin/utlxpls.sql
serveur-oracle: /home/oracle>ls -ltr $ORACLE_HOME/rdbms/admin/utlxplp.sql
-rw-r-----   1 oracle   dba            1529 Jan 25 2002  /oracle/rdbms/admin/utlxplp.sql

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> create table 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  insert into dvp values (i ) ;
  6  end loop ; 
  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> create index 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> create table 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  insert into dvp values ('DVP.COM', i ) ;
  6  end loop ; 
  7  commit ;
  8  end ; 
  9  /

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

Ecoulé : 00 :00 :05.16
SQL> create index i_dvp on dvp (a,b) ; 

Index créé.

--- Analyse des tables et indexes

SQL>  select * from dvp where b=25 ;
Ecoulé : 00 :00 :00.16

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT 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.

Copyright © . Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.