Discussion :

[13thFloor]

Appel à la communauté d'expert Oracle.
Je rencontre une bizarrerie sur une base en 10.2.
Voici le contexte :
Une requête avait un bon temps de réponse en 9.2.0.8 (moins d'1 seconde). Depuis le passage en 10.2.0.4, elle met un peu plus de 10". Pas gênant unitairement mais comme elle est lancée environ 4000 fois dans une chaîne, le temps global de traitement est franchement alourdi.

J'ai remarqué qu'en 9.2, le plan d'exécution (le vrai, pas l'explain plan) faisait un index skip scan. Or en 10.2, il fait nativement de l'index full scan.

Pour retrouver de l'index skip scan, il me suffit :
- soit de créer un autre index composé des 3 champs de la clause where
- soit de positionner le paramètre optimizer_features_enable à 10.2.0.3 au lieu de 10.2.0.4

Différents calculs de statistiques (dbms_stats.gather_table_stats) n'ont rien changé : avec/sans histogramme, estimate 100, sample...

N'y aurait-il pas un bug avec la fonction max en 10.2.0.4 qui empêcherait d'utiliser l'index_ss ?
Je n'ai pas trouver de bug sur metalink relatif à ce cas (sauf un qui est le contraire : Bug 5714944 - CBO may choose INDEX SKIP SCAN instead of INDEX UNIQUE SCAN). Dans mon cas l'index skip scan est plus performant.

Je préconise donc à mon client de setter optimizer_features_enable à 10.2.0.3 au niveau de sa session, voire au niveau db mais il y a-t-il d'autre mauvaises surprises ?

Un fix_control existe t-il ?

Requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select MAX(TB_NOT.NUM_VER)
  FROM tb_not tb_not
    WHERE (    (99999 = tb_not.id_ele_eval AND '1244' = tb_not.cd_not)
     AND 1 = tb_not.cd_fam_not
      );

16 millions de lignes environ.

Un seul index de type pk: (NUM_VER,CD_FAM_NOT,CD_NOT,ID_ELE_EVAL)

Il n'y a, pour les valeurs en clause where, qu'une seule ligne retournée (requête générée, non-modifiable).

Plan 10.2.0.4 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Elapsed: 00:00:11.15
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    15 |    57  (97)| 00:00:01 |
|   1 |  SORT AGGREGATE             |           |     1 |    15 |            |          |
|   2 |   FIRST ROW                 |           |     1 |    15 |    57  (97)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| PK_TB_NOT |     1 |    15 |    57  (97)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
90324 consistent gets => voila d’où vient la consommation cpu
0 physical reads

Plan 9.2.0.8 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
--------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    15 |     8 |
|   1 |  SORT AGGREGATE  |           |     1 |    15 |       |
|*  2 |   INDEX SKIP SCAN| PK_TB_NOT |     1 |    15 |     8 |
--------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
150 consistent gets
0 physical reads

Plan 10.2.0.4 avec paramètre optimizer_features_enable à "10.2.0.3" :
Elapsed: 00:00:00.03

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    15 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |           |     1 |    15 |            |          |
|*  2 |   INDEX SKIP SCAN| PK_TB_NOT |     1 |    15 |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
150 consistent gets => ouf, ça va mieux !
0 physical reads

Principaux paramètres (merci à v$sys_optimizer_env) :

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
      ID NAME                                     ISD VALUE                     DEFAULT_VALUE
---------- ---------------------------------------- --- ------------------------- -------------------------
         2 parallel_execution_enabled               YES true                      true
         9 optimizer_features_enable                YES 10.2.0.4                  10.2.0.4
        11 cpu_count                                YES 32                        32
        12 active_instance_count                    YES 1                         1
        13 parallel_threads_per_cpu                 YES 2                         2
        14 hash_area_size                           YES 131072                    131072
        15 bitmap_merge_area_size                   YES 1048576                   1048576
        16 sort_area_size                           YES 65536                     65536
        17 sort_area_retained_size                  YES 0                         0
        24 pga_aggregate_target                     YES 2048000 KB                2048000 KB
        25 _pga_max_size                            NO  409600 KB                 204800 KB
        35 parallel_query_mode                      YES enabled                   enabled
        36 parallel_dml_mode                        YES disabled                  disabled
        37 parallel_ddl_mode                        YES enabled                   enabled
        38 optimizer_mode                           YES all_rows                  all_rows
        48 cursor_sharing                           YES exact                     exact
        50 star_transformation_enabled              YES false                     false
        66 optimizer_index_cost_adj                 NO  50                        100
        67 optimizer_index_caching                  YES 0                         0
        70 query_rewrite_enabled                    YES true                      true
        71 query_rewrite_integrity                  YES enforced                  enforced
       101 workarea_size_policy                     YES auto                      auto
       105 optimizer_dynamic_sampling               YES 2                         2
       112 statistics_level                         YES typical                   typical
       114 skip_unusable_indexes                    YES true                      true
       165 optimizer_secure_view_merging            YES true                      true

Taille de bloc : 8K

Particularité : la fonction MIN() fait de l'index full scan mais bien plus rapide (ce qui me semble logique vu que l'index est, par nature, trié, donc moindre besoin de lire des blocs).

Environnement : 10.2.0.4 bundle #3, solaris 10 sur un e15k

[sweeton]

Est ce que les statistiques systèmes (dbms_stats.gather_system_stats) ont été calculées ?

[pachot]

Bonjour,

Effectivement celà ressemble à un bug. Je ne vois pas en quoi un INDEX FULL SCAN (MIN/MAX) peut être utile dans ce cas puisqu'il sert à aller chercher directement la première ou la dernière entrée de l'index (donc lire seulement 3 blocs si l'index a une hauteur 3 par exemple). Mais là il doit lire toutes les feuilles de l'index pour vérifier la clause where.
Le INDEX SKIP SCAN permet d'éviter la pluspart des feuilles (celles qui ne correpondent pas à la clause where) lorsque la première colonne a peu de valeurs distinctes.

Le mieux me paraitrait de ne pas mettre NUM_VER en première colonne de l'index... mais celà dépends évidemment des autres requêtes.

Cordialement,
Franck.

[13thFloor]

Est ce que les statistiques systèmes (dbms_stats.gather_system_stats) ont été calculées ?

Pas encore. Je vais suggérer au client de la faire pendant un période de charge et de comparer/tester sur sa préprod voire sur son environnement de dév.

L'index étant une PK, les 4 champs doivent faire partie de l'index.
La solution retenue est d'ajouter un index composé des 3 autres champs.