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:

---

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 !

Commencez par bien écrire la requête :

Code:

---

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:

---

ALTER INDEX <votre_index> COALESCE;

---

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:

---

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.

Oui, très intéressant.

Merci pour vos réponses.

Citation:

---

Envoyé par Waldar

Commencez par bien écrire la requête :

Code:

---

1 2 3

SELECT DISTINCT colonne1 FROM matable ORDER BY colonne1 ASC NULLS LAST;

---

---

Citation:

---

Envoyé par skuatamad

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

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

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 ?

Citation:

---

Envoyé par tipeh

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 :
http://asktom.oracle.com/pls/asktom/...:8806017524595

Citation:

---

Envoyé par skuatamad

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

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:

---

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:

---

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.

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.

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

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 ?

Citation:

---

Envoyé par pacmann

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:

---

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

---

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:

---

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

---

)

Citation:

---

Envoyé par Waldar

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 :oops:

@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.

Citation:

---

Envoyé par tipeh

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.

Merci beaucoup pour vos réponses.

Pour les paramètres de l'optimiseur, les voici :

Code:

---

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:

---

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:

---

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 ?

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:

---

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:

---

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:

---

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

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

Citation:

---

Envoyé par tipeh

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.

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.

En utilisant votre ''OUTLINE'', j'arrive à reproduire le cas (INDEX FULL SCAN + HASH GROUP BY + ORDER BY)

Code:

---

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:

---

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:

---

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.

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:

---

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 :
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 ?