IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Contribuez Oracle Discussion :

Demande d'avis sur tutoriel tuning de requêtes


Sujet :

Contribuez Oracle

  1. #21
    Membre averti

    Inscrit en
    Mars 2006
    Messages
    64
    Détails du profil
    Informations personnelles :
    Âge : 41

    Informations forums :
    Inscription : Mars 2006
    Messages : 64
    Points : 306
    Points
    306
    Par défaut
    Merci mnitu pour toutes ces remarques, toutes constructives

    Citation Envoyé par mnitu Voir le message
    Bref, il y a certaines choses à corriger, des autres à préciser et malheureusement quelques mythes à combattre.
    1) Attention: les valeurs NULL ne sont pas indexées


    En fait les choses sont un peu plus nuancés et votre exemple mal choisi. Voici une explication.
    => Complètement d'accord. Personnellement j'utilise souvent l'astuce de tom avec le index(colonne,0). Je vais donc corriger mon article car c'est vrai que tel quel ce que je dis est faux.

    C’est quoi « nombre conséquent de lignes » 10 000, 1 000 000, 10 000 000 ?]
    Sur du 8 millions de ligne j'ai eu ce genre de choses, ou le exists était bcp plus performant. Pour l'instant je n'ai pas sur moi de quoi reproduire le cas mais je rechercherai ça ASAP. Mais la requête était de mémoire bien complexe, basée sur 6 tables avec des clauses sur des champs ou il n'y a pas d'unicité (dates entre autres), je ne me souviens pas de plus ...

    VII-B. La fonction qu'on ne voit pas
    => j'ai été un peu vite celà d'applique aux index bitmaps uniquement.

    IV-C. Les indexes composés :quelques règles
    Là je me réfèrre au support de cours "oracle sql tuning" officiel d'Oracle. Je n'ai pas eu personnellement à constater ce cas, des collègues m'ont dit l'avoir eu, je n'ai jamais été vérifier.

    Enfin je reprécise que ce document est fait avec ce que JE sais, que je n'ai pas 10 ans d'experience dans le domaine et que pour le coup je fais ce que je peux.
    Pas de question technique par MP SVP.

  2. #22
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par jeepnc Voir le message
    ...

    => j'ai été un peu vite celà d'applique aux index bitmaps uniquement.
    ...
    Si, si ! Cella s’applique bien aux index B-tree et c'est maintenant que vous allez un peu trop vite.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
     
    Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
    Connected as mni
     
    SQL> 
    SQL> drop table emp_2
      2  /
     
    Table dropped
    SQL> create table emp_2 as
      2  select to_char(empno) empno, ename, job, mgr, hiredate, sal, comm, deptno
      3  From emp
      4  /
     
    Table created
    SQL> alter table emp_2
      2  add constraint pk_emp_2 primary key (empno)
      3  /
     
    Table altered
    SQL> exec dbms_stats.gather_table_stats(USER, 'EMP_2', cascade => True)
     
    PL/SQL procedure successfully completed
     
    SQL> set autotrace traceonly explain 
     
    SQL> Select *
    from emp_2
    where empno = '7369'
    /
      2    3    4
    Plan d'exécution
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=3
              8)
     
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP_2' (Cost=2 Card=1 By
              tes=38)
     
       2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP_2' (UNIQUE)
     
    SQL> Select *
    from emp_2
    where empno = 7369
    /
      2    3    4
    Plan d'exécution
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=3
              8)
     
       1    0   TABLE ACCESS (FULL) OF 'EMP_2' (Cost=2 Card=1 Bytes=38)

  3. #23
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    5) Les indexes à clefs inversées:

    Les indexes à clefs inversées: là c'est très simple : "1234" deviendra "4321" ... La raison de cette inversion provient du fait que les indexes peuvent devenir un goulot d'étranglement quand un grand nombre d'insertions est fait ... L'index étant ordonné, quand vous insérez par exemple beaucoup de valeurs venant d'une séquence, c'est le même bloc de l'index qui est alors systématiquement sollicité. En cassant la continuité, on enlève cet inconvénient ... ce type d'index a toutefois un inconvénient, on ne peut y effectuer des "range scan" (parcours d'une partie de l'index) de part sa nature discontinue. Concrètement cet index pourra être utilisé lors d'une clause where monchamp=maValeur mais pas avec une clause where monChamps > maValeur. Seul l'opérateur égalité peut l'utiliser. Cela peut facilement être gênant.
    Désolé mais encore une fois comme pour les Nulls il faut être un peu plus rigoureux : on peut observer des range scan pour ce type d’index.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
     
    Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
    Connected as mni
     
    SQL> 
    SQL> drop table emp_3
      2  /
     
    Table dropped
     
    SQL> 
    SQL> create table emp_3 as
      2  select 'ORACLE' company, empno, ename, job, mgr, hiredate, sal, comm, deptno
      3  From emp
      4  /
     
    Table created
    SQL> alter table emp_3
      2  add constraint pk_emp_3 primary key (company, empno)
      3  /
     
    Table altered
    SQL> alter index pk_emp_3 rebuild reverse
      2  /
     
    Index altered
    SQL> exec dbms_stats.gather_table_stats(USER, 'EMP_3', cascade => True)
     
    PL/SQL procedure successfully completed
     
    SQL> set autotrace traceonly explain
    SQL> Select *
    from emp_3
    where company = 'ORACLE'
      2    3    4  /
     
    Plan d'exécution
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes=
              616)
     
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP_3' (Cost=2 Card=14 B
              ytes=616)
     
       2    1     INDEX (RANGE SCAN) OF 'PK_EMP_3' (UNIQUE) (Cost=1 Card=1
              4)

  4. #24
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    6) VI. Clefs etrangères et indexes:

    Dans de la grande majorité des cas, l'indexation des clefs étrangères peut amener un gain non négligeable ! Vous vous rappelez, je disais que les indexes alentissaient les insertions ... et bien pas toujours.
    Imaginons un cas tout simple de clef étrangère: le champ CA de la table A référence le champ CB de la table B. Nous demandons ici à Oracle de vérifier que la valeur que l'on mettra dans CA existe bien dans CB ... et bien comment croyez vous qu'il va la chercher cette valeur pour savoir si elle existe ? En accédant à la table ... Et si le champ est indéxé il y a des chances que ça aille plus vite ...
    Une des extrémités (le champ reférencé) est souvent une clef primaire mais pas forcément, une contrainte d'unicité suffit, dans ce cas indexer sera judicieux. L'autre extrémité, le référençant est rarement indexé, pensez y, cela accelera les jointures et recherches qui pourraient se faire via ce champ.
    Actuellement je trouve ce paragraphe plutôt confus. La norme concernant l’indexation est d’indexer que ce qui est nécessaire parce que cella coût cher ; comme vous le dit d’ailleurs. Donc dans la majorité des cases il ne faut pas indexer les clefs étrangères. Dans aucun cas je ne pense pas que on peut parler "d’effet d’accélérateur des insertions via les contraintes". L’index sur la table maître existe toujours qu’il s’agit de clé primaire ou unique donc à l’insertion dans ce table il y a toujours plus de boulot à faire en présence d’index. Le fait que la recherche de l’existence de la valeur dans la table maître bénéficie de cette index est toute à fait normale : c’est pour accélérer les recherches qu’on index.
    L’indexation de la colonne référencé n’est utile que dans des cas particulières et dans ces cases le gain est toujours dans la recherche des valeurs.

  5. #25
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par jeepnc Voir le message
    ...
    ...
    Sur du 8 millions de ligne j'ai eu ce genre de choses, ou le exists était bcp plus performant. Pour l'instant je n'ai pas sur moi de quoi reproduire le cas mais je rechercherai ça ASAP. Mais la requête était de mémoire bien complexe, basée sur 6 tables avec des clauses sur des champs ou il n'y a pas d'unicité (dates entre autres), je ne me souviens pas de plus ...
    ...
    Mais, oui un requête bien plus complexe avec six tables et non pas deux et très probablement des critères de recherche peu sélective sur la table principale ainsi que sur les autres tables et pourtant l’intersection des tous ces critères ramène un nombre réduit des enregistrements.
    Je ne doute pas du fait que votre solution a changé radicalement le temps de réponse de la requête en question mais je doute fortement de la conclusion que vous avez tiré ; c’est typiquement ce type d’expérience qui enfante les divers mythes. Et c’est exactement ce type d’affirmation : « utilisez plutôt la méthode A à la place de la méthode B» non soutenu par un « test case » ou d’une analyse approfondie qui expliquerais dans quel cas précis cella est vrai, qui me gêne le plus dans un tutorial sur l’optimisation.
    Voilà un tkprof de ces mêmes types de requêtes mais arranges un peu différemment. Maintenez-vous toujours votre affirmation ?
    Vous constaterez lors de tables avec de nombreux enregistrements, que celà est quand même bien plus rapide ...
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
     
    SELECT BIGDEPT.*
    FROM
     BIGDEPT WHERE EXISTS (SELECT NULL FROM BIGEMP WHERE BIGEMP.DEPTNO =
      BIGDEPT.DEPTNO )
     
     
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch   300001      9.90      44.01      11489    1209562          4      300000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total   300003      9.90      44.02      11489    1209562          4      300000
     
    Misses in library cache during parse: 1
    Optimizer goal: FIRST_ROWS
    Parsing user id: 226     (recursive depth: 1)
     
    Rows     Row Source Operation
    -------  ---------------------------------------------------
     300000  NESTED LOOPS
     300000   SORT UNIQUE
    1400000    TABLE ACCESS FULL BIGEMP
     300000   TABLE ACCESS BY INDEX ROWID BIGDEPT
     300000    INDEX UNIQUE SCAN SYS_C0021149 (object id 107421)
     
    ********************************************************************************
     
    SELECT DISTINCT BIGDEPT.*
    FROM
     BIGDEPT JOIN BIGEMP ON (BIGDEPT.DEPTNO = BIGEMP.DEPTNO)
     
     
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.07          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch   300001      9.10      29.18      16338      11168          8      300000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total   300003      9.10      29.25      16338      11168          8      300000
     
    Misses in library cache during parse: 1
    Optimizer goal: FIRST_ROWS
    Parsing user id: 226     (recursive depth: 1)
     
    Rows     Row Source Operation
    -------  ---------------------------------------------------
     300000  SORT UNIQUE
    1400000   HASH JOIN
     400000    TABLE ACCESS FULL BIGDEPT
    1400000    TABLE ACCESS FULL BIGEMP
     
    ********************************************************************************

  6. #26
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par jeepnc Voir le message
    ...

    Là je me réfèrre au support de cours "oracle sql tuning" officiel d'Oracle. Je n'ai pas eu personnellement à constater ce cas, des collègues m'ont dit l'avoir eu, je n'ai jamais été vérifier.
    ...
    Voilà un lien qui parle de test proposé par Tom Kyte.

  7. #27
    Nouveau membre du Club
    Profil pro
    Inscrit en
    Décembre 2008
    Messages
    31
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2008
    Messages : 31
    Points : 37
    Points
    37
    Par défaut
    Si je puis me permettre : quelques fautes d'orthographe et quelques imprécisions :

    L'élément à mon avis le plus important est le nombre d'I/O Disque que nécéssite la requête. Lire sur un disque c'est lent. Pire encore, devoir effectuer un tri de données sur le disque s'avère souvent extrêmement coûteux...
    Ceci n'est pas exact car cela sous-entend que lire en mémoire est bien plus rapide que sur disque et qu'il faut donc se concentrer sur la réduction des PIO (Physical I/O - lectures sur disque) et non pas sur les LIO (Logical I/O - lectures en mémoire) pour tuner sa DB. Or ceci est faux, c'est même plutôt l'inverse qu'il faut viser. En effet, les zones mémoires d'Oracle sont protégées par des latchs (locks sériazables) ce qui n'est pas le cas des PIO.
    Un excellent PDF qui explique tout ça dans le détail sur www.hotsos.fr (LIO instead of PIO)

    Les statistiques permettent à l'optimiseur d'estimer le coût d'accès aux données. Normalement, elles sont en général calculées de manière automatique / périodique (Oracle 10G) ou manuelle (10G et avant ...).
    En 10, Oracle fourni un job par défaut qui le fait mais tout bon DBA a fait le sien pour les versions antérieures de manière à ce que ce soit automatique


    Pour en savoir plus sur le calcul des stats sur vos bases, demandez à votre DBA. Calculer des stats se fait en lançant une requête "ANALYSE TABLE matable COMPUTE STATISTICS;" par exemple ou mieux en utilisant le package dbms_stats comme ici:
    => ANALYZE

    Parcours complet de table ou Full Table Scan: Là c'est simple on parcours toute la table. En l'absence d'index approprié à la recherche c'est ce qu'Oracle fait. Ce mode est couteux sur de grosses tables car il génére beaucoup d'accès disque, étant donné que toute la table est lue.
    Ca dépend du % de lignes qu'Oracle doit ramener de la table aussi. De mémoire si on ramène 20+% des lignes d'une table, Oracle fait un FTS (Full Table Scan).

    Vous pouvez demander à Oracle de conserver certaines tables en mémoire cache. Ceci est particulièrement intéressant pour des petites tables de paramètres que vous accédez souvent, lors de jointures par exemple. Attention toutefois à ne pas en abuser, conserver de grosses tables en mémoire aurait des gros inconvénients au niveau occupation mémoire et performances ...
    Précisions ici : Oracle utilise (comme beaucoup) un mécanisme de LRU (Least Recent Used) pour gérer ses caches (les plus anciennement utilisés sont supprimés du cache lorsque des nouveaux ont besoin d'y être mis). Donc dans le cas de table de paramètres, si elles sont effectivement souvent utilisées, elles seront forcément en cache.
    L'intérêt de mettre ces tables dans le "KEEP_CACHE" n'est donc pas de "forcer" Oracle à les garder en mémoire mais d'utiliser un autre latch que celui du Buffer Cache pour gérer les accès à ces tables très souvent utilisées.

  8. #28
    Membre éclairé Avatar de jkofr
    Homme Profil pro
    Senior Consultant DBA (Trivadis SA)
    Inscrit en
    Octobre 2006
    Messages
    484
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Suisse

    Informations professionnelles :
    Activité : Senior Consultant DBA (Trivadis SA)
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 484
    Points : 724
    Points
    724
    Par défaut
    Hello,

    Voici une petite étude que j'avais faite sur les différents Btree.

    Ça peut être intéressant.

    A noter bien sur que Oracle n'utilise pas l'algo de suppression

    Jko
    Fichiers attachés Fichiers attachés
    OCM 11g, RAC and Performance & Tuning Expert 11g
    RMAN Backup & Recovery, Data Guard and Grid Control

Discussions similaires

  1. Demande d'avis sur une requête
    Par minnemo dans le forum Langage SQL
    Réponses: 1
    Dernier message: 19/05/2009, 17h08
  2. Demande d'avis sur un bouquin (Deitel)
    Par oodini dans le forum C++
    Réponses: 7
    Dernier message: 26/02/2005, 02h50

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo