Discussion :

[tipeh]

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 !

[Waldar]

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;

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

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.

[Waldar]

Oui, très intéressant.

[tipeh]

Merci pour vos réponses.

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;

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.

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)

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 ?

[skuatamad]

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

[tipeh]

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 ?

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.

[pachot]

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.

[Waldar]

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

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

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

[Mohamed.Houri]

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

[pacmann]

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

)

[pachot]

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.

[skuatamad]

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.

[tipeh]

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 ?

[Mohamed.Houri]

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')

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

[pacmann]

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.

[pachot]

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.

[Mohamed.Houri]

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.

[skuatamad]

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