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

  1. #1
    Membre régulier
    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 éminent sénior
    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 régulier
    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 éminent sénior
    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 régulier
    Je regarde ces 2 points et je vous tiens au courant.

  6. #6
    Membre régulier
    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?

  7. #7
    Expert éminent sénior
    Il y a quelque chose qui m’interpelle : la première requête ne ramène rien et la deuxième ramène 39 enregistrements ! Est-ce ce le cas ?

  8. #8
    Membre régulier
    C'est vrai que c'est surprenant. Je n'avais pas vu que la première ligne du tableau renvoie A-ROWS=0.
    Pourtant, la requête me restitue bien les mêmes 39 lignes que la deuxième requête. Je viens de la relancer à l'instant

  9. #9
    Expert éminent sénior
    Pourriez-vous exécuter aussi cette commande (vous devez avoir accès à la table sys.v$parameter)
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    SQL> show parameter optimizer
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_dynamic_sampling           integer     2
    optimizer_features_enable            string      10.2.0.4
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      first_rows
    optimizer_secure_view_merging        boolean     TRUE

  10. #10
    Membre régulier
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SQL> show parameter optimizer;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_dynamic_sampling           integer     2
    optimizer_features_enable            string      10.2.0.5
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      FIRST_ROWS
    optimizer_secure_view_merging        boolean     TRUE

  11. #11
    Membre éprouvé
    Citation Envoyé par 'olivier
    ;6526705']
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    SQL> show parameter optimizer;
     
    optimizer_mode                       string      FIRST_ROWS
    Votre problème est là. Changer en ALL_ROWS et donnez nous des nouvelles

    C'est très intéressant de le confirmer ou d'infirmer
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  12. #12
    Membre régulier
    Heu.... Comment je change ce parametre?

  13. #13
    Membre éprouvé
    Citation Envoyé par 'olivier
    ;6526735']Heu.... Comment je change ce parametre?
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
     
    alter session set optimizer_mode=ALL_ROWS;
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  14. #14
    Expert éminent sénior
    Citation Envoyé par Mohamed.Houri Voir le message

    ...
    C'est très intéressant de le confirmer ou d'infirmer
    C'est vrai que First_Rows est mauvais mais j'en doute que jusqu'à ce point. Mais comme vous dites à confirmer/infirmer!

  15. #15
    Membre régulier
    j'ai un peu oublié GooGle sur ce coup-la!

    Ceci étant dit, avec les bons paramètres, ça va tout de suite mieux.

    temps d'execution
    sans tri 00: 00: 00.09
    avec tri 00: 00: 00.12

    Merci infiniment de votre aide à tous les deux.

  16. #16
    Membre éprouvé
    Citation Envoyé par 'olivier
    ;6526768']j'ai un peu oublié GooGle sur ce coup-la!

    Ceci étant dit, avec les bons paramètres, ça va tout de suite mieux.

    temps d'execution
    sans tri 00: 00: 00.09
    avec tri 00: 00: 00.12

    Merci infiniment de votre aide à tous les deux.
    Et maintenant je vais vous expliquer pourquoi?

    C’est un bug dans le hint first_rows qu’Oracle maintient uniquement pour raison de ''backward compatibility'' en gros pour que le passé ne soit pas dépaysé.

    Il ne faut plus l’utiliser dans les nouvelles versions et optez plutot si besoin est au first_rows_n.

    Sous ce mode d’optimisation le CBO, s’il voit qu’il peut utiliser un index pour éviter un ORDER BY, alors il le fera quelque soit le cout de l’accès à cet index.

    Observez bien que dans votre second ''explain plan'', il n’y a aucune opération du type ORDER BY alors que votre requête contient bien une instruction ORDER BY. Par contre, on constate bien une opération INDEX FULL SCAN très couteuse et très volumineuse 32M

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
     
    3 |    INDEX FULL SCAN           | TABLE4TABLE3           |      1 |     32M|     32M|00:00:32.18 |


    C’est cette opération qui a été privilégiée à un simple ORDER BY de 39 records.

    Une remarque : dans des plans d’exécution plus compliqués on aurait observé une opération du type sort (order by) nosort montrant qu’il y a bien eu un order by mais sans passer par un tri supplémentaire.
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  17. #17
    Membre régulier
    Il ne faut plus l’utiliser dans les nouvelles versions et optez plutot si besoin est au first_rows_n.
    Est-ce un paramètre que je peux modifier au niveau de la base pour que toutes les sessions (SQLPlus ou via des programmes) le prennent en compte ou bien dois-je le faire à chaque session SQLPlus que j'ouvre?

  18. #18
    Expert éminent sénior
    Citation Envoyé par Mohamed.Houri Voir le message
    ...C’est un bug dans le hint first_rows qu’Oracle maintient uniquement pour raison de ''backward compatibility'' en gros pour que le passé ne soit pas dépaysé.
    ...
    Si vous me permettez quelques précisions :
    Ici first rows n’est pas un hint SQL mais un mode de fonctionnement de l’optimiseur légal, initialisé via un paramètre. Il existe bien aussi un hint SQL qui porte ce nom.
    Il n’y a pas de bug, tout simplement il fonctionne comme ça, d’après la documentation il utilise un mix de coût et règles heuristiques pour trouver un bon plan. De plus la documentation indique clairement que parfois il se trompe et elle recommande de ne plus l’utiliser

    FIRST_ROWS The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
    Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead

    Bravo à vous pour avoir bien vu !

  19. #19
    Membre régulier
    C'est bon, j'ai trouvé la réponse à ma dernière question
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    Alter system set optimizer_mode = FIRST_ROWS_1000;


    Encore merci tant pour les réponses que pour les compléments que je vais garder précieusement.

  20. #20
    Expert éminent sénior
    Ajoutez scope=both si vous avez un spfile ou modifiez aussi le fichier initXXX.ora.
    Sinon au prochain démarrage de l’instance vous allez avoir une surprise.