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

SQL Oracle Discussion :

Requête ralentie par "order by"


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé

    Inscrit en
    Octobre 2003
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Octobre 2003
    Messages : 188
    Par défaut Requête ralentie par "order by"
    Bonjour.

    Je travaille sur une base 10.2.0.5.0 sur Win2003 SP2.

    Voici mon problème :

    La requête
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063);
    ramène 40 lignes en une seconde voir moins.
    Si je rajoute la clause ORDER BY j'obtiens un temps de réponse à pleurer !

    TABLE2 compte près de 3 millions de lignes (9 champs num(10), 1 champ date)
    TABLE3 compte près de 32 million de lignes (4 champs num (10), 1 champ date)

    J'ai exécuté un explain plan des 2 requêtes dont voici le résultat.

    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
     
    SQL> explain plan for select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063);
     
    Explicité.
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3324095648
     
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                    |    68 |  2176 |    18   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID  | TABLE3             |    14 |   294 |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS                |                    |    68 |  2176 |    18   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| TABLE2             |     5 |    55 |     4   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | IDX_TABLE2_CLE1    |     5 |       |     3   (0)| 00:00:01 |
    |*  5 |    INDEX RANGE SCAN           | PK_TABLE3          |    14 |       |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - access("CLE1"=354063)
       5 - access("CLE2"="CLE2")
     
    18 ligne(s) sélectionnée(s).
    et
    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
     
    SQL> explain plan for select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063) order by CLE_TRI;
     
    Explicité.
     
    SQL> select * from table(dbms_xplan.display);
     
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3634225011
     
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                        |    68 |  2176 |    69M  (1)|233:01:59 |
    |   1 |  NESTED LOOPS                |                        |    68 |  2176 |    69M  (1)|233:01:59 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| TABLE3                 |    31M|   638M|  6161K  (1)| 20:32:24 |
    |   3 |    INDEX FULL SCAN           | TABLE4TABLE3           |    31M|       | 64280   (1)| 00:12:52 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID| TABLE2                 |     1 |    11 |     2   (0)| 00:00:01 |
    |*  5 |    INDEX UNIQUE SCAN         | PK_TABLE2              |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - filter("CLE1"=354063)
       5 - access("CLE2"="CLE2")
     
    18 ligne(s) sélectionnée(s).
    Quelqu'un pourrait-il m'expliquer pourquoi j'ai une telle variation de temps de réponse?

  2. #2
    Expert confirmé 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
    Par défaut
    Votre première requête réécrite comme :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    Select TABLE3.*
      From TABLE3
    	Join
    	(Select distinct cle2 as cle2
                    From TABLE2
                  Where cle1 = 'valeur'
               )
         on TABLE3.cle2 = TABLE2.cle2
    est une jointure entre les deux tables avec une seule condition de filtrage sur la table TABLE2. Le plan choisi par l'optimiseur est d'identifier les enregistrements de la table TABLE2 via l'index IDX_TABLE2_CLE1 et par la suite d'utiliser la colonne CLE2 pour accéder via l'index PK_TABLE3 aux enregistrements de la table TABLE3.

    Le fait d'ajouter le tri sur une colonne indexée de la table TABLE3: CLE_TRI ouvre une autre possibilité: accéder d'abord aux enregistrements de la table TABLE3 via l'index TABLE4TABLE3 (!?) et éviter ainsi l'étape de tri imposé par la présence de la clause SQL ORDER BY, trouver via la CLE2 l'enregistrement de la table TABLE2 et filtrer par rapport à la condition de filtrage sur CLE1.

    Maintenant, la vrai la question est pourquoi cela se produit vu qu'à priori vos statistiques semble correctes (à vérifier) et que j'ai du mal à m'imaginer pourquoi la solution basée sur le premier plan avec en plus le tri effectué en dernière étape lui semble plus coûteuse que le plan 2 ?

    Avez vous plus de détails à nous donner ?

  3. #3
    Membre confirmé

    Inscrit en
    Octobre 2003
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Octobre 2003
    Messages : 188
    Par défaut
    Bonjour.

    Je me permet juste d'ajouter, pour ceux qui liront peut être cette discussion, un petit correctif (c'est du moins ce que j'ai dû faire) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT TABLE3.*
      FROM TABLE3
    	JOIN
    	(SELECT DISTINCT cle2 AS cle2
                    FROM TABLE2
                  WHERE cle1 = 'valeur'
               ) as TABLE2
         ON TABLE3.cle2 = TABLE2.cle2

    Ceci étant dit, comment puis-je vérifier l'exactitude des mes statistiques?
    Quels détails puis-je vous fournir qui permettraient de pousser l'analyse?

  4. #4
    Expert confirmé 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
    Par défaut
    Merci, j'avais l'intime conviction d'avoir corrigé la requête!

    Essayez de suivre le démarche proposé par Mohamed.Houri dans ses intervention (#4 ou #8) pour comparer les statistiques estimés par rapport aux statistiques réelles.

  5. #5
    Membre confirmé

    Inscrit en
    Octobre 2003
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Octobre 2003
    Messages : 188
    Par défaut
    Je regarde ces 2 points et je vous tiens au courant.

  6. #6
    Membre confirmé

    Inscrit en
    Octobre 2003
    Messages
    188
    Détails du profil
    Informations forums :
    Inscription : Octobre 2003
    Messages : 188
    Par défaut
    Bonjour.

    Désolé d'avoir interrompu cette conversation aussi longtemps.
    J'ai pu exécuter ce que vous m'aviez demandé et voici les résultats :
    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
    SQL> alter session set statistics_level = all;
     
    Session modifiée.
     
    SQL> select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063);
     
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
     
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  6nwkss5c38wqf, child number 1
    -------------------------------------
    select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063)
     
    Plan hash value: 3324095648
     
    -----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    -----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                    |      0 |        |      0 |00:00:00.01 |       0 |      0 |
    |   1 |  TABLE ACCESS BY INDEX ROWID  | TABLE3             |      1 |     14 |     39 |00:00:00.03 |      28 |      6 |
    |   2 |   NESTED LOOPS                |                    |      1 |     65 |     44 |00:00:00.02 |      23 |      4 |
    |   3 |    TABLE ACCESS BY INDEX ROWID| TABLE2             |      1 |      5 |      4 |00:00:00.01 |       9 |      2 |
    |*  4 |     INDEX RANGE SCAN          | IDX_TABLE2_CLE1    |      1 |      5 |      4 |00:00:00.01 |       6 |      2 |
    |*  5 |    INDEX RANGE SCAN           | PK_TABLE3          |      4 |     14 |     39 |00:00:00.01 |      14 |      2 |
    -----------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - access("CLE1"=354063)
       5 - access("CLE2"="CLE2")
     
     
    23 ligne(s) sélectionnée(s).
    et
    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
    SQL> select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063) order by 2;
     
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
     
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  9ksd6bb3xfcdc, child number 1
    -------------------------------------
    select * from TABLE3 where CLE2 in ( select CLE2 from TABLE2 where CLE1 = 354063) order by CLE_TRI
     
    Plan hash value: 3634225011
     
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                        |      1 |        |     39 |00:15:38.05 |     102M|   5223K|
    |   1 |  NESTED LOOPS                |                        |      1 |     65 |     39 |00:15:38.05 |     102M|   5223K|
    |   2 |   TABLE ACCESS BY INDEX ROWID| TABLE3                 |      1 |     32M|     32M|00:10:11.26 |    6445K|   5194K|
    |   3 |    INDEX FULL SCAN           | TABLE4TABLE3           |      1 |     32M|     32M|00:00:32.18 |   65951 |  65869 |
    |*  4 |   TABLE ACCESS BY INDEX ROWID| TABLE2                 |     32M|      1 |     39 |00:04:30.91 |      96M|  28267 |
    |*  5 |    INDEX UNIQUE SCAN         | PK_TABLE2              |     32M|      1 |     32M|00:02:36.58 |      64M|  24850 |
    --------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - filter("CLE1"=354063)
       5 - access("CLE2"="CLE2")
     
     
    23 ligne(s) sélectionnée(s).
    Comment dois-je interpréter ces résultats?

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [Toutes versions] Requête SQL avec Simples et Doubles Quotes
    Par Roums dans le forum Macros et VBA Excel
    Réponses: 7
    Dernier message: 25/03/2010, 10h00

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