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

  1. #1
    Membre à l'essai
    L'optimiseur d'Oracle fait des mauvais choix ?
    Bonjour,

    J'ai mis en place dans Oracle une table qui contient 3 millions de lignes avec comme clé primaire les deux premières colonnes (Clé composite) : colonne1 et colonne2.

    Le problème est le suivant :
    - Quand je lance une requête toute simple du genre :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT colonne1
    FROM matable
    GROUP BY colonne1
    ORDER BY colonne1 ASC NULLS LAST;

    L'optimiseur d'oracle décide d'utiliser l'index de clé primaire, ce qui prend 20 secondes de temps d'exécution.

    - Quand je met un hint pour ne pas utiliser cet index, la requête mets 3 secondes pour s'exécuter.

    Oracle prend-il de mauvaises décisions quand à l'utilisation des index ? Est-il possible d'arranger les choses ?

    Je précise que j'ai remis à jour les statistiques de la table et ça fonctionne toujours aussi mal.
    Je ne peux pas non plus utiliser comme solution l'utilisation d'un hint permanent pour l’empêcher d'utiliser l'index vu que la requête est lancée par une application cliente ! (Impossible de la modifier, donc impossible d'insérer des hints).

    Merci d'avance pour votre aide !

  2. #2
    Modérateur

    Commencez par bien écrire la requête :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
      SELECT DISTINCT colonne1
        FROM matable
    ORDER BY colonne1 ASC NULLS LAST;

    Ensuite, l'analyse du plan d'exécution est nécessaire mais pas suffisante.
    Exécutez une trace sur le serveur avec les deux instructions.

    Au doigt mouillé, pour que l'index réponde moins vite que la table, il a peut-être des problèmes.
    Ça peut valoir le coup de lui passer un COALESCE :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    ALTER INDEX <votre_index> COALESCE;

  3. #3
    Expert confirmé
    Vous n'y pouvez certes rien, mais tout de même, quel est l'intérêt d'utiliser NULLS LAST sur une colonne NOT NULL...
    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
    SQL> drop table t;
     
    Table dropped.
     
    SQL> create table t (c1 number, c2 varchar2(10), c3 date);
     
    Table created.
     
    SQL> alter table t add primary key (c1, c2);
     
    Table altered.
     
    SQL> desc t;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     C1                                        NOT NULL NUMBER
     C2                                        NOT NULL VARCHAR2(10)
     C3                                                 DATE
     
    SQL>

    Quels sont les types de données des colonnes composant la clé primaire ?

    Juste pour info, je ne cautionne évidemment pas l'utilisation de GROUP BY à la place de DISTINCT, mais dans de rare cas (requête TOP N) DISTINCT et GROUP BY ne sont pas optimisé de la même manière :
    Fast Query To Get TOP-N Distinct Rows From A Huge Data Table

    C'est tout de même intéressant à savoir.

  4. #4
    Modérateur

    Oui, très intéressant.

  5. #5
    Membre à l'essai
    Merci pour vos réponses.

    Citation Envoyé par Waldar Voir le message
    Commencez par bien écrire la requête :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
      SELECT DISTINCT colonne1
        FROM matable
    ORDER BY colonne1 ASC NULLS LAST;

    Citation Envoyé par skuatamad Voir le message
    Vous n'y pouvez certes rien, mais tout de même, quel est l'intérêt d'utiliser NULLS LAST sur une colonne NOT NULL...
    Malheureusement, comme je l'ai deja dit dans mon premier post, je n'ai aucun contrôle sur les requêtes. Je donne mes tables à une application cliente qui elle s'occupe de générer des requêtes. Donc je ne peux ni changer en DISTINCT ni enlever le NULLS LAST.

    Citation Envoyé par skuatamad Voir le message
    Quels sont les types de données des colonnes composant la clé primaire ?
    La première colonne de ma clé composite : colonne 1
    • Type VARCHAR2 (64 CHAR)
    • Possède 3 valeurs distinctes
    • La valeur la plus longue dans ce champs est de longueur 4

    La deuxième de ma clé composite : colonne 2
    • Type CHAR (15 CHAR)


    Citation Envoyé par Waldar Voir le message

    Au doigt mouillé, pour que l'index réponde moins vite que la table, il a peut-être des problèmes.
    Ça peut valoir le coup de lui passer un COALESCE
    J'ai même fait un rebuild mais les performances n'ont pas changé.

    J'ai trouvé un moyen qui me parait un peu "brute", c'est de rajouter un index BITMAP. Est ce qu'il y aurait de mauvaises conséquences si j'utilise ça ?

  6. #6
    Expert confirmé
    Citation Envoyé par tipeh Voir le message
    J'ai trouvé un moyen qui me parait un peu "brute", c'est de rajouter un index BITMAP. Est ce qu'il y aurait de mauvaises conséquences si j'utilise ça ?
    C'est probablement une mauvaise idée.
    Les index bitmap ont un but précis et s'utilise à bon escient, les insert/update/delete vont être très pénalisés.
    Ce n'est envisageable que dans un contexte datawarehouse avec chargement des données en batch.

    Pouvez-vous poster un plan d'exécution comme indiqué ici :
    Afficher le plan d’exécution avec les stats de chaque étape

    Par ailleurs vous devriez faire une trace étendue (10046 en level 12) à analyser avec tkprof.

    Ce qui me semble plus raisonnable serait par exemple de compresser l'index :
    11_QUESTION_ID:8806017524595" target="_blank">http://asktom.oracle.com/pls/asktom/...:8806017524595

  7. #7
    Membre à l'essai
    Citation Envoyé par skuatamad Voir le message
    C'est probablement une mauvaise idée.
    Les index bitmap ont un but précis et s'utilise à bon escient, les insert/update/delete vont être très pénalisés.
    Ce n'est envisageable que dans un contexte datawarehouse avec chargement des données en batch.
    C'est justement mon cas. Ma table est une table de fait que je ne mets a jour qu'une seule fois par jour.
    Mais qu'est ce que vous voulez dire par pénalise les insertions les delete et les update ? est ce que cela les ralentirait de manière considérable ? Ne serait-il pas envisageable de désactiver l'index bitmap avant le chargement de données et de le reconstruire après ?

    Citation Envoyé par skuatamad Voir le message

    Pouvez-vous poster un plan d'exécution comme indiqué ici :
    Afficher le plan d’exécution avec les stats de chaque étape
    Plan d’exécution avec l'index : (Image)
    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
    SQL_ID  fh344s0y9yxm8, child number 0
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */DWH_STATS_TICKETS.REGISTRE as c0 from DWH.DWH_STATS_TICKETS DWH_STATS_TICKETS group by 
    DWH_STATS_TICKETS.REGISTRE order by DWH_STATS_TICKETS.REGISTRE 
    ASC NULLS LAST
     
    Plan hash value: 2815412565
     
    ----------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation         | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                      |      1 |        |      3 |00:00:21.60 |   23080 |  23080 |       |       |          |
    |   1 |  SORT ORDER BY    |                      |      1 |      3 |      3 |00:00:21.60 |   23080 |  23080 |  4096 |  4096 | 4096  (0)|
    |   2 |   HASH GROUP BY   |                      |      1 |      3 |      3 |00:00:21.60 |   23080 |  23080 |  1307K|  1307K| 1544K (0)|
    |   3 |    INDEX FULL SCAN| DWH_STATS_TICKETS_PK |      1 |   2923K|   2928K|00:00:21.99 |   23080 |  23080 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------------------



    Plan d'exécution sans l'index (désactivation de l'index avec un hint) : (Image)
    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
    SQL_ID  95c7257g6q2kt, child number 0
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS NO_INDEX(DWH_STATS_TICKETS 
    DWH_STATS_TICKETS_PK) */DWH_STATS_TICKETS.REGISTRE as c0 from 
    DWH.DWH_STATS_TICKETS DWH_STATS_TICKETS group by 
    DWH_STATS_TICKETS.REGISTRE order by DWH_STATS_TICKETS.REGISTRE 
    ASC NULLS LAST              
     
    Plan hash value: 2801208526 
     
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                   |      1 |        |      3 |00:00:04.03 |   41845 |  41842 |       |       |          |
    |   1 |  SORT ORDER BY      |                   |      1 |      3 |      3 |00:00:04.03 |   41845 |  41842 |  4096 |  4096 | 4096  (0)|
    |   2 |   HASH GROUP BY     |                   |      1 |      3 |      3 |00:00:04.03 |   41845 |  41842 |  1307K|  1307K|  603K (0)|
    |   3 |    TABLE ACCESS FULL| DWH_STATS_TICKETS |      1 |   2923K|   2928K|00:00:03.19 |   41845 |  41842 |       |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------------


    Je m'excuse pour la taille de mes images.

  8. #8
    Expert éminent
    Bonjour,
    Ce serait intéressant d'avoir le plan d'exécution avec le coût estimé pour comprendre le choix de l'optimiseur.
    Mais ce n'est pas normal qu'un FULL TABLE SCAN (donc lecture multibloc) de 41842 blocks soit tellement plus lent que un INDEX FULL SCAN (donc lecture block par bloc) de 23080.
    Je ne suis pas sûr que ce soit le choix de l'optimiseur qui soit mauvais, mais plutôt les i/o sur cette table qui ont quelque chose de pas optimal du tout.
    Cordialement,
    Franck.
    Franck Pachot - dbi services - Oracle ACE Director - OCM 12c - Oak Table member - twitter: @FranckPachot - blog: blog.pachot.net

  9. #9
    Modérateur

    Justement, c'est bien la lecture de la table qui est cinq fois plus rapide que celle de l'index.

  10. #10
    Membre émérite
    Salut,

    Tout à fait Waldar.

    Du coup il semble que l'optimiseur sous-estime sa capacité à faire du multiblock read... ou sur estime sa vitesse de range scan.

    Quelle est la valeur de optimizer_index_cost_adj ?
    Peut être rafraîchir les stats système également ?

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  11. #11
    Membre éprouvé
    Citation Envoyé par pacmann Voir le message
    Salut,

    Tout à fait Waldar.

    Du coup il semble que l'optimiseur sous-estime sa capacité à faire du multiblock read... ou sur estime sa vitesse de range scan.
    Ou bien que le CBO a voulu éviter une opération ORDER BY en préférant un accès en INDEX FULL SCAN quelque soit le coût de cet accès. Quoique cela n’apparaisse pas être le cas car l'opération ORDER BY est bien présente dans le plan non désiré.

    Il serait intéressant de connaitre, en plus de ce qu'a déjà demandé, pacman (optimizer_index_cost_adj) le mode de l'optimisateur utilisé (ALL_ROWS, FIRST_ROWS).
    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
     
    SQL> show parameter %optimi%
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------------------
    ----
    optimizer_capture_sql_plan_baselines boolean     FALSE
    optimizer_dynamic_sampling           integer     2
    optimizer_features_enable            string      11.2.0.3
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE
    optimizer_use_invisible_indexes      boolean     FALSE
    optimizer_use_pending_statistics     boolean     FALSE
    optimizer_use_sql_plan_baselines     boolean     TRUE
    plsql_optimize_level                 integer     2
    Bien Respectueusement
    www.hourim.wordpress.com

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

  12. #12
    Membre émérite
    Mohamed, c'est également ce que j'ai pensé au départ... mais dans le plan utilisant l'index, le hash group by est réalisé avant le sort, ce qui fait perdre "l'avantage" du tri (on n'a pas NO SORT du coup).

    (Sinon, juste pour économiser un peu les doigts, je crois qu'on n'a pas besoin des '%' pour le sho parameter
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    SQL> sho parameter optimi
     
    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      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE
    plsql_optimize_level                 integer     2

    )

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  13. #13
    Expert éminent
    Citation Envoyé par Waldar Voir le message
    Justement, c'est bien la lecture de la table qui est cinq fois plus rapide que celle de l'index.
    Ah oui, Merci Waldar, j'ai lu à l'envers

    @tipeh,
    Est-ce qu'on peut avoir le plan d'exécution en remplacant les 'ALLSTATS LAST' par 'ADVANCED' ?
    Afin de voir les couts estimés et les paramètres particuliers.

    Merci,
    Franck.
    Franck Pachot - dbi services - Oracle ACE Director - OCM 12c - Oak Table member - twitter: @FranckPachot - blog: blog.pachot.net

  14. #14
    Expert confirmé
    Citation Envoyé par tipeh Voir le message
    C'est justement mon cas. Ma table est une table de fait que je ne mets a jour qu'une seule fois par jour.
    Dans ce cas là c'est plus envisageable.

    Dans un 1er temps je ferais une trace + tkprof et je testerais la compression de l'index.

  15. #15
    Membre à l'essai
    Merci beaucoup pour vos réponses.

    Pour les paramètres de l'optimiseur, les voici :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    NAME                                   TYPE        VALUE    
    -------------------------------------  ----------- ---------
    optimizer_capture_sql_plan_baselines   boolean     FALSE    
    optimizer_dynamic_sampling             integer     2        
    optimizer_features_enable              string      11.2.0.3 
    optimizer_index_caching                integer     50       
    optimizer_index_cost_adj               integer     20       
    optimizer_mode                         string      ALL_ROWS 
    optimizer_secure_view_merging          boolean     TRUE     
    optimizer_use_invisible_indexes        boolean     FALSE    
    optimizer_use_pending_statistics       boolean     FALSE    
    optimizer_use_sql_plan_baselines       boolean     TRUE     
    plsql_optimize_level                   integer     0


    Donc mon optimizer_index_cost_adj est à 20. Est-ce sa valeur normale, faut-il le changer ?

    Pour les plans d'exécution avec le 'advanced' à la place de 'allstats last' :
    Plan d'exécution avec l'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
    47
    48
    49
    50
    51
    52
    53
    SQL_ID  fh344s0y9yxm8, child number 0
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */"DWH_STATS_TICKETS"."REGISTRE" as 
    "c0" from "DWH"."DWH_STATS_TICKETS" "DWH_STATS_TICKETS" group by 
    "DWH_STATS_TICKETS"."REGISTRE" order by "DWH_STATS_TICKETS"."REGISTRE" 
    ASC NULLS LAST
     
    Plan hash value: 2815412565
     
    ------------------------------------------------------------------------------------------
    | Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                      |       |       |  4808 (100)|          |
    |   1 |  SORT ORDER BY    |                      |     3 |    12 |  4808   (6)| 00:00:58 |
    |   2 |   HASH GROUP BY   |                      |     3 |    12 |  4808   (6)| 00:00:58 |
    |   3 |    INDEX FULL SCAN| DWH_STATS_TICKETS_PK |  2936K|    11M|  4535   (1)| 00:00:55 |
    ------------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$1
       3 - SEL$1 / DWH_STATS_TICKETS@SEL$1
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          OPT_PARAM('star_transformation_enabled' 'true')
          OPT_PARAM('optimizer_index_cost_adj' 20)
          OPT_PARAM('optimizer_index_caching' 50)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX(@"SEL$1" "DWH_STATS_TICKETS"@"SEL$1" ("DWH_STATS_TICKETS"."LASTMAJDATE" 
                  "DWH_STATS_TICKETS"."IDADH" "DWH_STATS_TICKETS"."IDOPE" 
                  "DWH_STATS_TICKETS"."IDETATCOURANTOPE" "DWH_STATS_TICKETS"."TYPETITULAIREDEPOT" 
                  "DWH_STATS_TICKETS"."DPTTITULAIRE" "DWH_STATS_TICKETS"."PAYSTITULAIRE" 
                  "DWH_STATS_TICKETS"."TLD" "DWH_STATS_TICKETS"."REGISTRE"))
          USE_HASH_AGGREGATION(@"SEL$1")
          END_OUTLINE_DATA
      */
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=1) NLSSORT("DWH_STATS_TICKETS"."REGISTRE",'nls_sort=''FRENCH''')[200
           0], "DWH_STATS_TICKETS"."REGISTRE"[VARCHAR2,256]
       2 - "DWH_STATS_TICKETS"."REGISTRE"[VARCHAR2,256]
       3 - "DWH_STATS_TICKETS"."REGISTRE"[VARCHAR2,256]


    Plan d'exécution sans l'index (désactivation de l'index avec un hint) :
    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
    SQL_ID  95c7257g6q2kt, child number 0
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS NO_INDEX(DWH_STATS_TICKETS 
    DWH_STATS_TICKETS_PK) */"DWH_STATS_TICKETS"."REGISTRE" as "c0" from 
    "DWH"."DWH_STATS_TICKETS" "DWH_STATS_TICKETS" group by 
    "DWH_STATS_TICKETS"."REGISTRE" order by "DWH_STATS_TICKETS"."REGISTRE" 
    ASC NULLS LAST
     
    Plan hash value: 2801208526
     
    -----------------------------------------------------------------------------------------
    | Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                   |       |       | 11737 (100)|          |
    |   1 |  SORT ORDER BY      |                   |     3 |    12 | 11737   (3)| 00:02:21 |
    |   2 |   HASH GROUP BY     |                   |     3 |    12 | 11737   (3)| 00:02:21 |
    |   3 |    TABLE ACCESS FULL| DWH_STATS_TICKETS |  2936K|    11M| 11465   (1)| 00:02:18 |
    -----------------------------------------------------------------------------------------
     
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
     
       1 - SEL$1
       3 - SEL$1 / DWH_STATS_TICKETS@SEL$1
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          OPT_PARAM('star_transformation_enabled' 'true')
          OPT_PARAM('optimizer_index_cost_adj' 20)
          OPT_PARAM('optimizer_index_caching' 50)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          FULL(@"SEL$1" "DWH_STATS_TICKETS"@"SEL$1")
          USE_HASH_AGGREGATION(@"SEL$1")
          END_OUTLINE_DATA
      */
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=1) NLSSORT("DWH_STATS_TICKETS"."REGISTRE",'nls_sort=''FRENCH''')[20
           00], "DWH_STATS_TICKETS"."REGISTRE"[VARCHAR2,256]
       2 - "DWH_STATS_TICKETS"."REGISTRE"[VARCHAR2,256]
       3 - "DWH_STATS_TICKETS"."REGISTRE"[VARCHAR2,256]


    Je ne comprend pas à quoi correspond la colonne "Time" dans ces plans d'exécution. Ce n'est surement pas le temps d'exécution vu que chez moi la requête avec l'index renvoie une réponse en 22 secondes. C'est les temps estimés ?

    Est ce que je dois changer un paramètre de l'optimiseur ? Si c'est le cas, est-il possible de le faire que pour un schéma particulier ?

  16. #16
    Membre éprouvé
    Quelle est votre version d’Oracle? Edit : 11.2.0.3 (je viens de le voir sur votre dernière réponse).

    J’ai essayé de reproduire votre cas comme suit. D’abords le modèle
    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
     
    drop table t;
     
    create table t (c1 varchar2(64), c2 char(15), d1 date);
     
    insert into t
     select
       mod(abs(dbms_random.random),3)+ 1||chr(ascii('Y'))
      ,dbms_random.string('L',dbms_random.value(1,5))||rownum
      ,to_date(to_char(to_date('01/01/1980','dd/mm/yyyy'),'J') +      
                           trunc(dbms_random.value(1,11280)),'J')
    from dual
    connect by level <= 2e6;
     
    alter table t add constraint t_pk primary key (c1,c2) using index;
     
    exec dbms_stats.gather_table_stats (user, 't', cascade => true, method_opt => 'FOR ALL COLUMNS SIZE 1');


    Ensuite les tests en 11gR2, 10gR2 et 12c respectivement

    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
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
     
    SQL> select * from v$version;
     
    BANNER
    ---------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
     
     
    SQL> SELECT  c1
      2  FROM t
      3  GROUP BY c1
      4  ORDER BY c1 ASC NULLS LAST;
     
    C1
    ---
    1Y
    2Y
    3Y
     
    SQL> start c:\xstat
     
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------
    SQL_ID  0gy317h433nkd, child number 0
    -------------------------------------
    SELECT  c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST
     
    Plan hash value: 2111031280
     
    ---------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |      1 |        |      3 |00:00:00.47 |    1983 |
    |   1 |  SORT GROUP BY NOSORT|      |      1 |      3 |      3 |00:00:00.47 |    1983 |
    |   2 |   INDEX FULL SCAN    | T_PK |      1 |   2000K|   2000K|00:00:00.24 |    1983 |
    ---------------------------------------------------------------------------------------
     
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           1983  consistent gets
              0  physical reads
              0  redo size
            363  bytes sent via SQL*Net to client
            350  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              3  rows processed
     
    SQL> SELECT /*+ no_index(t t_pk) */ c1
      2  FROM t
      3  GROUP BY c1
      4  ORDER BY c1 ASC NULLS LAST;
     
    C1
    --------
    1Y
    2Y
    3Y
     
    SQL> start c:\xstat
     
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------
    SQL_ID  241y7p6tyf6pa, child number 0
    -------------------------------------
    SELECT /*+ no_index(t t_pk) */ c1 FROM t GROUP BY c1 ORDER BY c1 ASC
    NULLS LAST
     
    Plan hash value: 1476560607
     
    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.53 |    2204 |       |       |          |
    |   1 |  SORT GROUP BY     |      |      1 |      3 |      3 |00:00:00.53 |    2204 |  2048 |  2048 | 2048  (0)|
    |   2 |   TABLE ACCESS FULL| T    |      1 |   2000K|   2000K|00:00:00.20 |    2204 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------
     
    15 rows selected.
     
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           2204  consistent gets
              0  physical reads
              0  redo size
            363  bytes sent via SQL*Net to client
            350  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              3  rows processed


    En 11gR2, j’ai un comportement proche du votre sauf que chez moi l’index full scan a bien été utilisé pour éviter l’opération ORDER BY (SORT GROUP BY NOSORT) et l’utilisation de l’index est plus rapide qu’un ''FULL TABLE SCAN'' nécessitant moins de lectures logiques (1983 au lieu de 2204). Alors que chez vous malgré un balayage couteux de l’index dans un ordre bien précis (INDEX FULL SCAN) il y a une opération ORDER BY supplémentaire.

    Puis sous la 10gR2 et la 12c respectivement
    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
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
     
     
    SQL> select * from v$version;
     
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 – Production
     
    SQL> SELECT  c1
      2  FROM t
      3  GROUP BY c1
      4  ORDER BY c1 ASC NULLS LAST;
     
    C1
    ---------------------
    1Y
    2Y
    3Y
     
    SQL> start c:\xstat
     
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------
    SQL_ID  0gy317h433nkd, child number 0
    -------------------------------------
    SELECT  c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST
     
    Plan hash value: 861388998
     
    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------
    |   1 |  SORT GROUP BY        |      |      1 |      3 |      3 |00:00:01.65 |    8222 |    368 |  2048 |  2048 | 2048  (0)|
    |   2 |   INDEX FAST FULL SCAN| T_PK |      1 |   1991K|   2000K|00:00:00.01 |    8222 |    368 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------
     
     
    13 rows selected.
     
    SQL> SELECT /*+ no_index(t t_pk) */ c1
      2  FROM t
      3  GROUP BY c1
      4  ORDER BY c1 ASC NULLS LAST;
     
    C1
    --------
    1Y
    2Y
    3Y
     
    SQL> start c:\xstat
     
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------
    SQL_ID  241y7p6tyf6pa, child number 0
    -------------------------------------
    SELECT /*+ no_index(t t_pk) */ c1 FROM t GROUP BY c1 ORDER BY c1 ASC NULLS LAST
     
    Plan hash value: 3156910365
     
    -------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------
    |   1 |  SORT GROUP BY     |      |      1 |      3 |      3 |00:00:03.33 |    8912 |   8852 |  2048 |  2048 | 2048  (0)|
    |   2 |   TABLE ACCESS FULL| T    |      1 |   1991K|   2000K|00:00:02.09 |    8912 |   8852 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------
     
    13 rows selected.
     
     
    SQL> select * from v$version;
     
    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
     
    SQL> SELECT  c1
      2      FROM t
      3      GROUP BY c1
      4      ORDER BY c1 ASC NULLS LAST;
     
    C1                                                                                                                      
    ----------------------------------------------------------------                                                        
    1Y                                                                                                                      
    2Y                                                                                                                      
    3Y                                                                                                                      
     
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
     
    PLAN_TABLE_OUTPUT                                                                                                       
    ------------------------------------------------------------------------------------------------------------------------
    SQL_ID  4gkp6sz1sfbvv, child number 1                                                                                   
    -------------------------------------                                                                                   
    SELECT  c1     FROM t     GROUP BY c1     ORDER BY c1 ASC NULLS LAST                                                    
     
    Plan hash value: 755059269                                                                                              
     
    -------------------------------------------------------------------------------------------------------------------     
    | Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |     
    -------------------------------------------------------------------------------------------------------------------     
    |   0 | SELECT STATEMENT      |      |      1 |        |      3 |00:00:01.03 |    8133 |       |       |          |     
    |   1 |  SORT GROUP BY        |      |      1 |      3 |      3 |00:00:01.03 |    8133 |  2048 |  2048 | 2048  (0)|     
    |   2 |   INDEX FAST FULL SCAN| T_PK |      1 |   2000K|   2000K|00:00:00.49 |    8133 |       |       |          |     
    -------------------------------------------------------------------------------------------------------------------     
     
     
    14 rows selected.
     
    SQL> SELECT /*+ no_index(t t_pk) */ c1
      2      FROM t
      3      GROUP BY c1
      4      ORDER BY c1 ASC NULLS LAST;
     
    C1                                                                                                                      
    ----------------------------------------------------------------                                                        
    1Y                                                                                                                      
    2Y                                                                                                                      
    3Y                                                                                                                      
     
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
     
    PLAN_TABLE_OUTPUT                                                                                                       
    ----------------------------------------------------------------------------------------------------------------
    SQL_ID  4wnum1mvwdp08, child number 0                                                                                   
    -------------------------------------                                                                                   
    SELECT /*+ no_index(t t_pk) */ c1     FROM t     GROUP BY c1     ORDER                                                  
    BY c1 ASC NULLS LAST                                                                                                    
     
    Plan hash value: 1476560607                                                                                             
     
    ----------------------------------------------------------------------------------------------------------------        
    | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |        
    ----------------------------------------------------------------------------------------------------------------        
    |   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.41 |    9087 |       |       |          |        
    |   1 |  SORT GROUP BY     |      |      1 |      3 |      3 |00:00:00.41 |    9087 |  2048 |  2048 | 2048  (0)|        
    |   2 |   TABLE ACCESS FULL| T    |      1 |   2000K|   2000K|00:00:00.13 |    9087 |       |       |          |        
    ----------------------------------------------------------------------------------------------------------------                                                                                                                               
     
    15 rows selected.


    Cette fois, sous 10gR2 et 12c, le CBOa choisi, par défaut, un INDEX FAST FULL SCAN. Ce qui me parait un choix judicieux.

    En ce qui concerne votre cas, il est clair que l’utilisation de l’index full scan est inappropriée vue que cette opération dure 21 secondes alors que l’opération full table scan s’exécute en 3 secondes. La question qui mérite d’être posée est pourquoi le CBO a préféré un ''index full scan'' très couteux en temps d’exécution à un accès en table full scan? Pourquoi a-t-il utilisé un INDEX FULL SCAN et un ORDER BY en même temps? Pourquoi a-t-il utilisé un HASH GROUP BY et pas un SORT GROUP BY ? Pourtant un GROUP BY suivi d’un ORDER BY sur la même colonne devrait conduire le CBO à préférer UN SORT GROUP BY. Vous pouvez à titre d’essai annihiler l’utilisation du HASH GROUP BY en utilisant le hint /*+ OPT_PARAM('_gby_hash_aggregation_enabled' 'false')


    Citation Envoyé par tipeh Voir le message

    Je ne peux pas non plus utiliser comme solution l'utilisation d'un hint permanent pour l’empêcher d'utiliser l'index vu que la requête est lancée par une application cliente ! (Impossible de la modifier, donc impossible d'insérer des hints).
    Vous pouvez injecter un hint sans toucher à l'application. Vous avez un exemple ici et une discussion sur le package SQL_PATCH ici
    Bien Respectueusement
    www.hourim.wordpress.com

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

  17. #17
    Membre émérite
    Citation Envoyé par tipeh Voir le message

    Donc mon optimizer_index_cost_adj est à 20. Est-ce sa valeur normale, faut-il le changer ?
    La valeur par défaut est 100... c'est en fait la valeur neutre qui ne change pas le coût. Quand le paramètre vaut 20, le coût calculé par ailleurs de l'accès par index est divisé par 5.

    Ceci explique assez largement le comportement à mon avis.

    Maintenant, changer un tel paramètre au niveau du système est une tâche sensible... parce qu'actuellement, il doit favoriser l'utilisation d'index en OLTP par exemple. Le passer à 100 pourrait faire ramer toute la base.

    Déjà, tu peux tenter un alter session pour voir ce que donne ta requête avec d'autres valeurs du paramètre, et aussi en discuter avec ton dba.

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  18. #18
    Expert éminent
    Donc:
    le CBO choisit le moindre coût (4808 au lieu de 11737)

    Le coût du FULL TABLE SCAN est calculé à partir du nombre de blocs à lire (BLOCKS dans ALL_TABLES) et du coût des lectures multibloc (db_file_multiblock_read_count, ou statistiques système de SYS.AUX_STATS$)

    Le coût du INDEX FULL SCAN dépends du nombre de blocs à lire (LEAF_BLOCKS dans ALL_INDEXES) et est ici réduit de 50% par optimizer_index_caching (dont le défaut est 0)

    Il faudrait voir toutes ces valeurs pour comprendre le coût est 2x plus élevé en FULL TABLE SCAN.

    Cordialement,
    Franck.
    Franck Pachot - dbi services - Oracle ACE Director - OCM 12c - Oak Table member - twitter: @FranckPachot - blog: blog.pachot.net

  19. #19
    Membre éprouvé
    En utilisant votre ''OUTLINE'', j'arrive à reproduire le cas (INDEX FULL SCAN + HASH GROUP BY + 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
    33
    34
    35
    36
     
    explain plan for
    SELECT /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          OPT_PARAM('star_transformation_enabled' 'true')
          OPT_PARAM('optimizer_index_cost_adj' 20)
          OPT_PARAM('optimizer_index_caching' 50)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX(@"SEL$1" "DWH_STATS_TICKETS"@"SEL$1" ("DWH_STATS_TICKETS"."LASTMAJDATE" 
                  "DWH_STATS_TICKETS"."IDADH" "DWH_STATS_TICKETS"."IDOPE" 
                  "DWH_STATS_TICKETS"."IDETATCOURANTOPE" "DWH_STATS_TICKETS"."TYPETITULAIREDEPOT" 
                  "DWH_STATS_TICKETS"."DPTTITULAIRE" "DWH_STATS_TICKETS"."PAYSTITULAIRE" 
                  "DWH_STATS_TICKETS"."TLD" "DWH_STATS_TICKETS"."REGISTRE"))
          USE_HASH_AGGREGATION(@"SEL$1")
          END_OUTLINE_DATA
      */ c1
        FROM t
        GROUP BY c1
        ORDER BY c1 ASC NULLS LAST;
     
    select * from table(dbms_xplan.display);    
     
    Plan hash value: 2835688100
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     3 |     9 |   414   (5)| 00:00:02 |
    |   1 |  SORT ORDER BY    |      |     3 |     9 |   414   (5)| 00:00:02 |
    |   2 |   HASH GROUP BY   |      |     3 |     9 |   414   (5)| 00:00:02 |
    |   3 |    INDEX FULL SCAN| T_PK |  2000K|  5859K|   414   (5)| 00:00:02 |
    --------------------------------------------------------------------------


    En supprimant uniquement le USE_HASH_AGGREGATION(@"SEL$1") je retombe sur "mon" cas par défaut (INDEX FULL SCAN + SORT GROUP BY (NO SORT))

    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
     
    explain plan for
    SELECT /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          OPT_PARAM('star_transformation_enabled' 'true')
          OPT_PARAM('optimizer_index_cost_adj' 20)
          OPT_PARAM('optimizer_index_caching' 50)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX(@"SEL$1" "DWH_STATS_TICKETS"@"SEL$1" ("DWH_STATS_TICKETS"."LASTMAJDATE" 
                  "DWH_STATS_TICKETS"."IDADH" "DWH_STATS_TICKETS"."IDOPE" 
                  "DWH_STATS_TICKETS"."IDETATCOURANTOPE" "DWH_STATS_TICKETS"."TYPETITULAIREDEPOT" 
                  "DWH_STATS_TICKETS"."DPTTITULAIRE" "DWH_STATS_TICKETS"."PAYSTITULAIRE" 
                  "DWH_STATS_TICKETS"."TLD" "DWH_STATS_TICKETS"."REGISTRE"))     
          END_OUTLINE_DATA
      */ c1
        FROM t
        GROUP BY c1
        ORDER BY c1 ASC NULLS LAST;
     
    select * from table(dbms_xplan.display);  
     
    Plan hash value: 2111031280
     
    -----------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |     3 |     9 |   414   (5)| 00:00:02 |
    |   1 |  SORT GROUP BY NOSORT|      |     3 |     9 |   414   (5)| 00:00:02 |
    |   2 |   INDEX FULL SCAN    | T_PK |  2000K|  5859K|   414   (5)| 00:00:02 |
    -----------------------------------------------------------------------------


    A titre de comparaison, mon 'OUTILNE' par défaut est
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    Outline Data
    -------------
     
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          DB_VERSION('11.2.0.3')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX(@"SEL$1" "T"@"SEL$1" ("T"."C1" "T"."C2"))
          END_OUTLINE_DATA
      */


    Le 'OUTLINE' montre également plusieurs colonnes indexées (en plus de la colonne REGISTRE), quels sont tous les indexes sur cette table?

    Il faut donc voir pourquoi le USE_HASH_AGGREGATION est choisi par défaut.
    Bien Respectueusement
    www.hourim.wordpress.com

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

  20. #20
    Expert confirmé
    Mohamed, en reprenant votre jeu de test j'obtiens un comportement un peu différent sur une 11.2.0.2 (XE)
    De base oracle choisit un INDEX FAST FULL SCAN, ce qui l'oblige bien à effectuer le SORT ultérieurement.

    J'obtiens bien l'étape combiné SORT GROUP BY avec un NLS_SORT à BINARY.
    Et j'obtiens la présence d'un HASH GROUP BY suivit d'un SORT ORDER BY, en passant le NLS_SORT à FRENCH, ce qui est le cas de tipeh au vu des précédents plans postés.

    Avec le paramètre OPTIMIZER_INDEX_COST_ADJ à 20, l'optimiseur choisit de l'INDEX FULL SCAN et avec le NLS_SORT à FRENCH il doit retrier (en BINARY il utilise bien un SORT GROUP BY NOSORT)
    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
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    SQL> show parameter optimizer_index_cost_adj;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_index_cost_adj             integer     100
    SQL> show parameter nls_sort;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    nls_sort                             string      BINARY
    SQL> set autotrace traceonly explain
    SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1
      2    FROM t
      3   GROUP BY c1
      4   ORDER BY c1 ASC NULLS LAST;
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 755059269
     
    ------------------------------------------------------------------------------
    | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |     3 |     9 |  2206   (5)| 00:00:27 |
    |   1 |  SORT GROUP BY        |      |     3 |     9 |  2206   (5)| 00:00:27 |
    |   2 |   INDEX FAST FULL SCAN| T_PK |  2000K|  5859K|  2119   (1)| 00:00:26 |
    ------------------------------------------------------------------------------
     
    SQL> alter session set nls_sort=french;
     
    Session altered.
     
    SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1
      2    FROM t
      3   GROUP BY c1
      4   ORDER BY c1 ASC NULLS LAST;
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 65194836
     
    -------------------------------------------------------------------------------
    | Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |      |     3 |     9 |  2294   (9)| 00:00:28 |
    |   1 |  SORT ORDER BY         |      |     3 |     9 |  2294   (9)| 00:00:28 |
    |   2 |   HASH GROUP BY        |      |     3 |     9 |  2294   (9)| 00:00:28 |
    |   3 |    INDEX FAST FULL SCAN| T_PK |  2000K|  5859K|  2119   (1)| 00:00:26 |
    -------------------------------------------------------------------------------
     
    SQL> alter session set nls_sort=binary;
     
    Session altered.
     
    SQL> alter session set optimizer_index_cost_adj=20;
     
    Session altered.
     
    SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1
      2    FROM t
      3   GROUP BY c1
      4   ORDER BY c1 ASC NULLS LAST;
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2111031280
     
    -----------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |     3 |     9 |  1558   (1)| 00:00:19 |
    |   1 |  SORT GROUP BY NOSORT|      |     3 |     9 |  1558   (1)| 00:00:19 |
    |   2 |   INDEX FULL SCAN    | T_PK |  2000K|  5859K|  1558   (1)| 00:00:19 |
    -----------------------------------------------------------------------------
     
    SQL> alter session set nls_sort=french;
     
    Session altered.
     
    SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1
      2    FROM t
      3   GROUP BY c1
      4   ORDER BY c1 ASC NULLS LAST;
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1760210272
     
    --------------------------------------------------------------------------------------
    | Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |      |     3 |     9 |       |  6937   (2)| 00:01:24 |
    |   1 |  SORT ORDER BY        |      |     3 |     9 |    22M|  6937   (2)| 00:01:24 |
    |   2 |   SORT GROUP BY NOSORT|      |     3 |     9 |       |  6937   (2)| 00:01:24 |
    |   3 |    INDEX FULL SCAN    | T_PK |  2000K|  5859K|       |  1558   (1)| 00:00:19 |
    --------------------------------------------------------------------------------------
     
    SQL> set autotrace off

    Tipeh ci-dessous un cas assez proche en terme de paramétrage :
    11_QUESTION_ID:3193365100346233806#4270429200346841037" target="_blank">http://asktom.oracle.com/pls/asktom/...29200346841037

    Vos paramètres ressemblent plus à du OLTP en 9i qu'à du DWH en 11G
    Mais votre base héberge peut être aussi du OLTP.
    Assurez vous par ailleurs que votre configuration sqlplus corresponde à peu près à la configuration de l'application, comme nls_sort ou arraysize (ici avec 3 lignes en retour ça n'a pas d'impact)...

    Je vous encourage vivement à faire une trace pour avoir également d'autres informations, sur les waits notamment.

    Par ailleurs l'index et la table sont ils créés dans le même tablespace ?
    Et lors de votre test avec l'index bitmap, a-t-il été créé dans le même tablespace que l'index PK ?

###raw>template_hook.ano_emploi###