1. #1
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    505
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 505
    Points : 386
    Points
    386

    Par défaut Pourquoi un ORDER BY fait chuter le nombre de CONSISTENT GETS ?

    Bonjour,

    Je me trouve confronté au problème suivant : en ajoutant un ORDER BY dans ma requête SQL, le nombre de CONSISTENT GETS diminue fois 10 et je ne peux pas l'expliquer...
    Quelqu'un aurait-il une idée?

    Je crée une table avec 600 000 rows.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SQL> create table test_obj01 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;
     
    SQL> insert into test_obj01 select * from test_obj01;
    78417 rows created.
     
    SQL> /
    ...
    ...
     
    SQL> select count(*) from test_obj01;
    COUNT(*)
    ----------
    627352
    Création d'un ID avec rownum et je génère les stats.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SQL> update test_obj01 set OWNER = OWNER || to_char(rownum);
    627352 rows updated.
    SQL> commit;
     
    SQL> exec dbms_stats.gather_schema_stats('HR');

    Si je fais un SELECT sans ORDER BY, voilà ce que j'obtiens la deuxième fois (je ne mets pas le premier SELECT car il y a des lectures sur disque dur).
    Ce sont les mêmes nombres si je ré-exécute plusieurs fois la requête.
    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
    SQL> set autotrace traceonly explain statistics
    SQL> SET TIMING ON
    
    SQL> select owner from test_obj01;
    627352 rows selected.
    Elapsed: 00:00:04.13
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2410895595
    
    ---------------------------------------------------------------------------------------------------------------------
    | Id  | Operation	                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |	                   |   627K|  7351K|  1168	 (1)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL  |  TEST_OBJ01   |   627K|  7351K|  1168	 (1)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
    	  0  recursive calls
    	  0  db block gets
          45841  consistent gets
    	  0  physical reads
    	  0  redo size
       15681662  bytes sent via SQL*Net to client
         460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         627352  rows processed

    Je fais un A/R de ma base (oui, c'est violent), j'ajoute le ORDER BY et, pour les SELECT après le premier, voilà les résultats : temps de requête identique que sans le ORDER BY mais le nombre de CONSISTENT GETS à diminué de 10...
    Et on voit bien que le ORDER BY est exécuté.

    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 owner from test_obj01 order by owner;
    627352 rows selected.
    
    Elapsed: 00:00:04.10
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2720153981
    --------------------------------------------------------------------------------
    
    | Id  | Operation	   	                            | Name		      | Rows | Bytes     |TempSpc| Cost (%CPU)| Time	|
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |			      |   627K|  7351K|		       |  4029   (1)| 00:00:01 |
    |   1 |  SORT ORDER BY	            |			      |   627K|  7351K|    12M      |  4029   (1)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL                   | TEST_OBJ01     |   627K|  7351K|		       |  1168   (1)| 00:00:01 |
    
    -----------------------------------------------------------------------------------------------------------------------------------
    
    Statistics
    ----------------------------------------------------------
    	  0  recursive calls
    	  0  db block gets
           4283  consistent gets
    	  0  physical reads
    	  0  redo size
       15681662  bytes sent via SQL*Net to client
         460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
    	  1  sorts (memory)
    	  0  sorts (disk)
         627352  rows processed
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Expert confirmé
    Avatar de pachot
    Homme Profil pro
    Oracle ACE Director, DBA OCM 12c, consultant. En Suisse (dbi services)
    Inscrit en
    novembre 2007
    Messages
    1 562
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Suisse

    Informations professionnelles :
    Activité : Oracle ACE Director, DBA OCM 12c, consultant. En Suisse (dbi services)
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 562
    Points : 5 417
    Points
    5 417
    Billets dans le blog
    5

    Par défaut

    Bonjour,

    Ici, tu as un fetch size de 15 (le défaut de sqlplus) et il y a environ 150 lignes par bloc.
    Tu vas donc revoir 10 fois de suite le même bloc, mais oracle ne va pas garder le bloc 'pinned' entre deux aller-retours clients -> nouveau buffer get à chaque fois
    On voit ça dans le nombre de 'SQL*Net roundtrips to/from client'

    Avec un SORT, chaque bloc n'est lu qu'une fois, va en workarea, est trié, et le fetch se fait là dessus.

    Ai tu met une taille de fetch plus grante (set arraysize 5000) tu retombera à un nombre raisonable de consistent gets même sans le 'sort'

    Cordialement,
    Franck.
    Franck Pachot - Consultant et formateur (dbi services) - Oracle ACED - Oracle Certified Master 12c - Oak Table member - twitter: @FranckPachot
    Besoin d'une formation Oracle 12cR2 ?


  3. #3
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    505
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 505
    Points : 386
    Points
    386

    Par défaut

    Je te remercie pour ta réponse Franck. J'avais voulu imiter ton article "Oracle tuning silver bullet: add an order by to make your query faster" mais avec mes propres données. Résultat, je suis tombé sur ce cas, à savoir que la requête va aussi vite mais pas grâce de la compression Oracle*Net mais grâce à cette chute de Consistents gets.

    Je teste ça ce week-end

    A nouveau un gros merci pour ta réponse!
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  4. #4
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    505
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 505
    Points : 386
    Points
    386

    Par défaut

    Bon, j'ai fais mes tests Franck. J'ai modifié le ARRAYSIZE mais cela diminue le nombre de données transférées aussi bien avec le ORDER BY que sans celui-ci.
    Je m'attendais, comme dans ton article, à trouver une différence sur le temps d'exécution entre l'ordre SQL sans ORDER BY et celui avec ORDER BY (plus rapide).
    Dans mon cas, c'est l'ordre SQL avec le ORDER BY qui est le plus lent et, surtout, j'ai la même valeur pour les données transférées, comme si Oracle.Net ne faisait pas de compression : il faut modifier un paramètre pour activer cette compression?

    Est-ce que mes données seraient en cause? J'ai récupéré OWNER, OBJECT_NAME, SUBOBJECT_NAME de DBA_OBJECTS. Rien que pour le champ OWNER, la compression devrait marcher et diminuer le nombre de données, non? En outre j'ai dupliqué 5 fois mes données pour passer de 78417 rows à 627352 rows : avec le ORDER BY ça devrait se voir, non?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SQL> show arraysize  
    arraysize 15
    SQL> set arraysize 200           
    SQL> show arraysize
    arraysize 200

    Le SELECT sans ORDER BY
    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
    SQL> select owner from test_obj01;
    627352 rows selected.
    
    Elapsed: 00:00:00.68
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2410895595
    
    --------------------------------------------------------------------------------
    | Id  | Operation	  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |	       |   627K|  7351K|  1168	 (1)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| TEST_OBJ01 |   627K|  7351K|  1168	 (1)| 00:00:01 |
    --------------------------------------------------------------------------------
    
    Statistics
    ----------------------------------------------------------
    	  0  recursive calls
    	  0  db block gets
           7396  consistent gets
    	  0  physical reads
    	  0  redo size
        8177124  bytes sent via SQL*Net to client
          35104  bytes received via SQL*Net from client
           3138  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         627352  rows processed

    Le SELECT avec ORDER BY
    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
    SQL> select owner from test_obj01 order by owner;
    627352 rows selected.
    
    Elapsed: 00:00:00.90
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2720153981
    
    --------------------------------------------------------------------------------
    | Id  | Operation	   | Name	| Rows	| Bytes |TempSpc| Cost (%CPU)| Time	|
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |		|   627K|  7351K|	|  4029   (1)| 00:00:01 |
    |   1 |  SORT ORDER BY	   |		|   627K|  7351K|    12M|  4029   (1)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| TEST_OBJ01 |   627K|  7351K|	|  1168   (1)| 00:00:01 |
    --------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
    	  0  recursive calls
    	  0  db block gets
           4283  consistent gets
    	  0  physical reads
    	  0  redo size
        8177124  bytes sent via SQL*Net to client
          35104  bytes received via SQL*Net from client
           3138  SQL*Net roundtrips to/from client
    	  1  sorts (memory)
    	  0  sorts (disk)
         627352  rows processed
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  5. #5
    Expert confirmé
    Avatar de pachot
    Homme Profil pro
    Oracle ACE Director, DBA OCM 12c, consultant. En Suisse (dbi services)
    Inscrit en
    novembre 2007
    Messages
    1 562
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Suisse

    Informations professionnelles :
    Activité : Oracle ACE Director, DBA OCM 12c, consultant. En Suisse (dbi services)
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 562
    Points : 5 417
    Points
    5 417
    Billets dans le blog
    5

    Par défaut

    Non, il n'y a rien à activer pour la compression SQL*Net. Mais c'est très basique: seulement lorsque la valeur d'une colonne est la même que la ligne précédente.
    Franck Pachot - Consultant et formateur (dbi services) - Oracle ACED - Oracle Certified Master 12c - Oak Table member - twitter: @FranckPachot
    Besoin d'une formation Oracle 12cR2 ?


  6. #6
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    505
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 505
    Points : 386
    Points
    386

    Par défaut

    OK, je teste ça prochainement
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  7. #7
    Expert confirmé
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 600
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 600
    Points : 5 016
    Points
    5 016

    Par défaut

    J'ai peut être pas tout suivi mais :
    Citation Envoyé par pachot Voir le message
    Non, il n'y a rien à activer pour la compression SQL*Net. Mais c'est très basique: seulement lorsque la valeur d'une colonne est la même que la ligne précédente.
    Citation Envoyé par Ikebukuro Voir le message
    Est-ce que mes données seraient en cause? J'ai récupéré OWNER, OBJECT_NAME, SUBOBJECT_NAME de DBA_OBJECTS. Rien que pour le champ OWNER, la compression devrait marcher et diminuer le nombre de données, non?
    Couplé à :
    Citation Envoyé par Ikebukuro Voir le message
    Création d'un ID avec rownum et je génère les stats.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SQL> update test_obj01 set OWNER = OWNER || to_char(rownum);
    627352 rows updated.
    SQL> commit;
    Fait que OWNER ne semble plus trop éligible à la compression.

  8. #8
    Expert confirmé
    Avatar de pachot
    Homme Profil pro
    Oracle ACE Director, DBA OCM 12c, consultant. En Suisse (dbi services)
    Inscrit en
    novembre 2007
    Messages
    1 562
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Suisse

    Informations professionnelles :
    Activité : Oracle ACE Director, DBA OCM 12c, consultant. En Suisse (dbi services)
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 562
    Points : 5 417
    Points
    5 417
    Billets dans le blog
    5

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    Fait que OWNER ne semble plus trop éligible à la compression.
    Effectivement
    Franck Pachot - Consultant et formateur (dbi services) - Oracle ACED - Oracle Certified Master 12c - Oak Table member - twitter: @FranckPachot
    Besoin d'une formation Oracle 12cR2 ?


  9. #9
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    505
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 505
    Points : 386
    Points
    386

    Par défaut

    Argh, j'avais oublié cette modif... mais bon, ça m'a permis d'isoler l'histoire du Consistent gets
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

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

Discussions similaires

  1. [2008R2] Pourquoi la transaction ne fait pas de rollback ?
    Par Kropernic dans le forum Développement
    Réponses: 8
    Dernier message: 01/02/2015, 11h41
  2. Pourquoi l'enregistrement se fait 2 fois
    Par pierrot10 dans le forum PHP & MySQL
    Réponses: 4
    Dernier message: 30/04/2009, 19h50
  3. Pourquoi l'enregistrement se fait 2 fois
    Par pierrot10 dans le forum Requêtes
    Réponses: 0
    Dernier message: 30/04/2009, 16h36
  4. Réponses: 1
    Dernier message: 07/02/2008, 10h43
  5. Réponses: 19
    Dernier message: 25/05/2007, 17h15

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