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 :

Optimisation d'un 'sort'


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre expérimenté Avatar de bidi
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    262
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Octobre 2004
    Messages : 262
    Par défaut Optimisation d'un 'sort'
    Hello,

    Voilà c'est très simple

    Disons que j'ai 3 tables:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    ACTOR (+- 1800000 rows)
    FILES (+- 1700000 rows)
    FILE_CONTRACT (+-500000 rows)
    la requête paginée suivante:
    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
     
    select * 
      from 
    ( select rownum rnum, a.*
        from (SELECT contract.INTEGRATED_FILE_ID
              FROM FILE_CONTRACT file_contract,
                   FILES contract,
                   ACTOR declarant
              WHERE file_contract.CONTRACTOR_ID=declarant.ID
              AND file_contract.FILE_ID=contract.ID
              AND declarant.ACTOR_TYPE_CODE ='contractor'
              AND declarant.BCE_NUM=999999724
              ORDER BY contract.INTEGRATED_FILE_ID desc
             ) a
       where rownum < 25 
    )
    where rnum >= 10;
    qui contient donc un order by (pour la pagination).

    les indexes sur les champs utilisés dans cette requête sont:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    ACTOR(BCE_NUM)
    FILES(ID,INTEGRATED_FILE_ID)
    FILE_CONTRACT(CONTRACTOR_ID,FILE_ID)
    l'explain plan:
    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
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
     
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation                         |  Name                           | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                                 |     1 |    26 |    10 |
    |*  1 |  VIEW                             |                                 |     1 |    26 |    10 |
    |*  2 |   COUNT STOPKEY                   |                                 |       |       |       |
    |   3 |    VIEW                           |                                 |     1 |    13 |    10 |
    |*  4 |     SORT ORDER BY STOPKEY         |                                 |     1 |    40 |    10 |
    |   5 |      NESTED LOOPS                 |                                 |     1 |    40 |     8 |
    |   6 |       NESTED LOOPS                |                                 |     4 |   116 |     4 |
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    |*  7 |        TABLE ACCESS BY INDEX ROWID| ACTOR                           |     1 |    18 |     2 |
    |*  8 |         INDEX RANGE SCAN          | ACTOR_M2_BCE_NUM                |     2 |       |     1 |
    |*  9 |        INDEX RANGE SCAN           | FILE_CONTRACT_M1_CONTRACTOR_ID  |     1 |    11 |     2 |
    |* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |
    -----------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter("from$_subquery$_001"."RNUM">=10)
       2 - filter(ROWNUM<25)
     
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
       4 - filter(ROWNUM<25)
       7 - filter("DECLARANT"."ACTOR_TYPE_CODE"='contractor')
       8 - access("DECLARANT"."BCE_NUM"=416451583)
       9 - access("FILE_CONTRACT"."CONTRACTOR_ID"="DECLARANT"."ID")
      10 - access(SYS_OP_DESCEND("FILE_CONTRACT"."FILE_ID")=SYS_OP_DESCEND("CONTRACT"."ID"))
           filter("FILE_CONTRACT"."FILE_ID"="CONTRACT"."ID")
    Le problème c'est que c'est quand même assez couteux... Ca tombe même en time out (30sec!) quand la db est chargée ;(

    Et ça à l'air de venir de la clause order by. (Normal j'imagine que ça prenne du temps mais si quelqu'un sait comment optimiser ça, en jouant sur les indexes ou autre chose...)

    Voici, pour info les traces, on voit clairement la différence avec la clause order by ou sans:

    Avec:
    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
     
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=26)
       1    0   VIEW (Cost=10 Card=1 Bytes=26)
       2    1     COUNT (STOPKEY)
       3    2       VIEW (Cost=10 Card=1 Bytes=13)
       4    3         SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=40)
       5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=40)
       6    5             NESTED LOOPS (Cost=4 Card=4 Bytes=116)
       7    6               TABLE ACCESS (BY INDEX ROWID) OF 'ACTOR' (Cost
              =2 Card=1 Bytes=18)
     
       8    7                 INDEX (RANGE SCAN) OF 'ACTOR_M2_BCE_NUM' (NO
              N-UNIQUE) (Cost=1 Card=2)
     
       9    6               INDEX (RANGE SCAN) OF 'FILE_CONTRACT_M1_CONTRA
              CTOR_ID' (NON-UNIQUE) (Cost=2 Card=1 Bytes=11)
     
      10    5             INDEX (RANGE SCAN) OF 'FILES_M3_INT_FILE_ID' (NO
              N-UNIQUE) (Cost=1 Card=1635731 Bytes=17993041)
     
     
     
     
     
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           5970  consistent gets
           1630  physical reads
              0  redo size
            669  bytes sent via SQL*Net to client
            368  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             15  rows processed
    et sans:
    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
     
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=26)
       1    0   VIEW (Cost=8 Card=1 Bytes=26)
       2    1     COUNT (STOPKEY)
       3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=40)
       4    3         NESTED LOOPS (Cost=4 Card=4 Bytes=116)
       5    4           TABLE ACCESS (BY INDEX ROWID) OF 'ACTOR' (Cost=2 C
              ard=1 Bytes=18)
     
       6    5             INDEX (RANGE SCAN) OF 'ACTOR_M2_BCE_NUM' (NON-UN
              IQUE) (Cost=1 Card=2)
     
       7    4           INDEX (RANGE SCAN) OF 'FILE_CONTRACT_M1_CONTRACTOR
              _ID' (NON-UNIQUE) (Cost=2 Card=1 Bytes=11)
     
       8    3         INDEX (RANGE SCAN) OF 'FILES_M3_INT_FILE_ID' (NON-UN
              IQUE) (Cost=1 Card=1635731 Bytes=17993041)
     
     
     
     
     
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
             59  consistent gets
              0  physical reads
              0  redo size
            640  bytes sent via SQL*Net to client
            368  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             15  rows processed

    Voilà j'ai l'impression que ce sont les physical reads qui plombent tout mais je ne sais pas trop d'ou ça vient... et même s'il y a moyen d'optimiser ça...

    Merci pour ceux qui ont lu jusqu'au bout

  2. #2
    Membre Expert Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    Juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : Juin 2004
    Messages : 1 626
    Par défaut
    Salut !

    Est-ce que tes conditions :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
              AND declarant.ACTOR_TYPE_CODE ='contractor'
              AND declarant.BCE_NUM=999999724
    ... identifient un declarant de manière unique ?

    Si oui, y a-t-il concrètement une contrainte ?
    Parce que si c'est le cas, il ne devrait pas y avoir de tri en passant par l'index...

    Sinon, le je suis un peu traumatisé par les 1635K indiqués dans le plan...
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    |* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |
    Tu es sensé avoir combien de lignes sans les ROWNUM (toutes pages comprises, donc) ?

  3. #3
    Membre expérimenté Avatar de bidi
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    262
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Octobre 2004
    Messages : 262
    Par défaut
    Citation Envoyé par pacmann Voir le message
    Salut !

    Est-ce que tes conditions :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
              AND declarant.ACTOR_TYPE_CODE ='contractor'
              AND declarant.BCE_NUM=999999724
    ... identifient un declarant de manière unique ?
    Oui

    Citation Envoyé par pacmann Voir le message
    Si oui, y a-t-il concrètement une contrainte ?
    Heu non...

    Citation Envoyé par pacmann Voir le message
    Parce que si c'est le cas, il ne devrait pas y avoir de tri en passant par l'index...
    Je vais essayer en rajoutant la contrainte alors.

    Citation Envoyé par pacmann Voir le message
    Sinon, le je suis un peu traumatisé par les 1635K indiqués dans le plan...
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    |* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |
    Tu es sensé avoir combien de lignes sans les ROWNUM (toutes pages comprises, donc) ?
    Environ 3000 dans ce cas... d'où la pagination. J'ai raté quelque chose ou il y a quelque chose que je ne fais pas bien?

    Pour info, 'Businessement parlant', cette requête est donc censée ramener la liste des déclarations d'un déclarant par paquet de 15 donc. En lui présentant les plus nouvelles en premier.

    Merci pour ton aide

  4. #4
    Membre expérimenté Avatar de bidi
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    262
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Octobre 2004
    Messages : 262
    Par défaut
    Heu sorry, je ne peux pas mettre de contrainte BCE_NUM/ACTOR_TYPE_CODE

    parce parfois BCE_NUM peut être null et on peut avoir des cas de ce genre:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
     
    BCE_NUM   |   ACTOR_TYPE_CODE
    ---------------------------------
    null      |    labo
    null      |    labo
    ...
    ...
    Donc sorry

  5. #5
    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
    Citation Envoyé par bidi Voir le message
    ...Voilà j'ai l'impression que ce sont les physical reads qui plombent tout mais je ne sais pas trop d'ou ça vient... et même s'il y a moyen d'optimiser ça...

    Merci pour ceux qui ont lu jusqu'au bout
    Pour quoi il y a autant de consistents gets ? A priori le tri (sorts) se fait en mémoire seulement.
    Sinon, le plan est presque identique dans le deux cas donc ce n'est pas le order by qui pose des soucis.

  6. #6
    Membre expérimenté Avatar de bidi
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    262
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Octobre 2004
    Messages : 262
    Par défaut
    Je n'en sais rien pourquoi il y a autant de consistents gets justement

    Ce que je constate c'est qu'il y en a autant justement quand la clause order by est présente.

    Et je ne parviens pas à comprendre pourquoi...

  7. #7
    Membre expérimenté Avatar de bidi
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    262
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Octobre 2004
    Messages : 262
    Par défaut
    se pourrait-il que ça ait un rapport avec ceci:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    Index_name   |   clustering_factor
    FILE_CONTRACT_M1_CONTRACTOR_ID   |   313679
    FILE_CONTRACT_M2_COMPANY_ID   |   1780
    FILE_CONTRACT_M3_CONTACT_ID   |   21874
    FILE_CONTRACT_PK   |   1746
    Le clustering factor (merci le blog de pacman ;p) est super élevé pour FILE_CONTRACT_M1_CONTRACTOR_ID.

    Puis-je y faire quelque chose? j'ai bien peur que non...

  8. #8
    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
    Le clustering factor n’est qu’une mesure de l’écart entre la façon dans laquelle les données sont stockées dans l’index et sur le disque. C’est un attribut de l’index qui exprime que l’index est plus ou moins intéressant comme choix pour la recherche des données par rapport à un autre index ou un full table scan. Une réorganisation des données peut changer cette valeur.
    Le consistent gets c’est la lecture des données en mode consistent : par rapport au début de l’interrogation.
    [EDIT] Un nombre important de consistent gets indique d’habitude que les données lues sont en train d’être modifiés par un autre traitement.
    Ce n’est pas le tri qui implique un nombre élevé des consistent gets, d’où ma question.
    [/EDIT]
    Le paragraphe édité est incorrect.

  9. #9
    Membre expérimenté Avatar de bidi
    Profil pro
    Inscrit en
    Octobre 2004
    Messages
    262
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Octobre 2004
    Messages : 262
    Par défaut
    Bon, j'ai reconstruit la table avec un order by contractor_id et là je n'ai plus de physical reads pour ma requête et ça va beaucoup plus vite. Par contre c'est juste pour le fun car j'imagine qu'une telle pratique n'est pas viable en production ...

    Pour le nombre de consistents gets, désolé mais je ne vois pas d'où ça vient...Il n'y a rien d'autre qui modifie les données quand j'y accède. Et, encore une fois, si l'on regarde la trace, ce nombre est ridiculement bas quand on ne fait pas le order by...(de +-60 sans à +-6000 avec...)

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

Discussions similaires

  1. Optimiser un tirage au sort dans une collection
    Par sepas dans le forum Général Java
    Réponses: 5
    Dernier message: 11/07/2014, 17h47
  2. Optimisation de votre SGBDR et de vos requêtes...
    Par SQLpro dans le forum Langage SQL
    Réponses: 35
    Dernier message: 11/01/2013, 11h49
  3. Réponses: 15
    Dernier message: 12/10/2012, 08h06
  4. Réponses: 15
    Dernier message: 12/10/2012, 08h06
  5. [langage] Optimiser la lecture d'un fichier
    Par And_the_problem_is dans le forum Langage
    Réponses: 2
    Dernier message: 11/06/2002, 10h24

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