Discussion :

[Pomalaix]

Bonjour à tous

C'est avec Oracle 8.1.6 sous Aix.
DB_BLOCK_SIZE de 2K en test, 8K en prod.

J'ai une requête qui prend moins de 2 secondes en test et 14 secondes en prod, à volumétrie identique.

Code :

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

1
2
3
4
SELECT NUDOSS,CDINFO,TIMJIF,NOMBRE,NULMAX,NUCMAX 
FROM HR.ZYTD12 
WHERE  NUDOSS IN ( 737635809, 737635810, 737635811, 737635812, 737635815, 737635819, 737635820, 737635822, 737635825, 737635826, 737635827, 737635828, 737635830, 737635832, 737635833, 737635834, 737635835, 737635836, 737635838, 737635839)  
ORDER BY NUDOSS;

Je regarde le plan d'exécution de part et d'autre, et me rends compte qu'en prod, l'index disponible sur la table ZYTD12 (colonnes NUDOSS et CDINFO) n'est pas utilisé :

En test

Code :

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

1
2
3
4
0	SELECT STATEMENT Optimizer=CHOOSE (Cost=602 Card=934 Bytes=19614)
1	INLIST ITERATOR
2	TABLE ACCESS (BY INDEX ROWID) OF 'ZYTD12' (Cost=602 Card =934 Bytes=19614)
3	INDEX (RANGE SCAN) OF 'HRIX1ZYTD12' (UNIQUE) (Cost=15  Card=934)

En prod

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
1689 rows selected.
 
Elapsed: 00:00:14.46
 
Execution Plan
----------------------------------------------------------
   0		SELECT STATEMENT Optimizer=CHOOSE (Cost=530 Card=923 Bytes=19383)                                                       *
   1	0	SORT (ORDER BY) (Cost=530 Card=923 Bytes=19383)
   2	1	TABLE ACCESS (FULL) OF 'ZYTD12' (Cost=524 Card=923 Bytes=19383)                                                     *
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       8629  consistent gets
       8016  physical reads
          0  redo size
     139512  bytes sent via SQL*Net to client
      12856  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1689  rows processed

C'est tout simple me dis-je, je vais forcer l'usage de l'index en prod :

Code :

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

1
2
3
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8 	-- (il était à 32)
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=10		-- (il était à 100)
ALTER SESSION SET OPTIMIZER_INDEX_CACHING=90		-- (il était à 0)

Effectivement, l'index est alors utilisé, mais la requête prend toujours 14 secondes, et génère toujours autant de lectures de blocs !

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
1689 rows selected.
 
Elapsed: 00:00:14.03
 
Execution Plan
----------------------------------------------------------
   0		SELECT STATEMENT Optimizer=CHOOSE (Cost=55 Card=923 Bytes=19383)                                                        *
   1	0	INLIST ITERATOR                                    
   2	1	TABLE ACCESS (BY INDEX ROWID) OF 'ZYTD12' (Cost=55 Card=923 Bytes=19383)                                            *
   3	2	INDEX (RANGE SCAN) OF 'HRIX1ZYTD12' (UNIQUE) (Cost=2 Card=923)                                                    *
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       8629  consistent gets
       7540  physical reads
          0  redo size
     139512  bytes sent via SQL*Net to client
      12856  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1689  rows processed

Je me dis alors que c'est l'ordonnancement physique des données dans la table qui doit poser problème. Pour chaque NUDOSS, il y a environ 80 lignes de données, qui sont sans doute très dispersées dans la table.

Mais vérifications faites, les données sont autant en vrac en test qu'en prod, comme on peut le voir dans les caractéristiques de l'index.
En test :

Code :

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

1
2
3
4
5
6
7
Hauteur de l'arborescence : 3
Clés distinctes : 1 849 776
Facteur de cluster : 1 162 218
Blocs feuilles : 22 347
Nombre moyen de blocs feuille par clé : 1
Nombre moyen de blocs de données par clé : 1
Nombre de lignes : 1 849 776

En prod :

Code :

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

1
2
3
4
5
6
7
Hauteur de l'arborescence : 2
Clés distinctes : 1 856 879
Facteur de cluster : 1 100 044
Blocs feuilles : 5229
Nombre moyen de blocs feuille par clé : 1
Nombre moyen de blocs de données par clé : 1
Nombre de lignes : 1 856 879

Conclusion : tout me paraît identique, en particulier le "clustering factor" que je pensais en cause, et je ne sais plus de quel côté chercher !!

Des idées ?

[orafrance]

vérifie que les stats systémes ne sont pas calculées

Code :

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

select * from sys.aux_stats$

[Pomalaix]

vérifie que les stats systémes ne sont pas calculées

C'est un point que j'avais vérifié, y a pas de problème de ce côté-là...

[orafrance]

Tiens, le nombre de blocs par feuille est très différent quand même... c'est les mêmes FS ?

[Pomalaix]

Tiens, le nombre de blocs par feuille est très différent quand même... c'est les mêmes FS ?

C'est le nombre de blocs DE feuilles (et non PAR feuille).
Comme j'ai indiqué tout en haut, j'ai des blocs de 8K en prod, et de 2K en test.
Donc le nombre de blocs est tout à fait cohérent.

[Wurlitzer]

Peux tu faire un tkprof (avec EVENT 10046 wait) sur les deux bases ?

On devrait pouvoir voir ou il passe plus de temps d'une base a l'autre

[Pomalaix]

Bon, après avoir trié la table selon l'index (en prod), c'est le jour et la nuit :

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
1689 rows selected.
 
Elapsed: 00:00:00.74
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=76 Card=922 Bytes=19
          362)
 
   1    0   INLIST ITERATOR
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ZYTD12_TEST' (Cost=76
          Card=922 Bytes=19362)
 
   3    2       INDEX (RANGE SCAN) OF 'IND_ZYTD12' (NON-UNIQUE) (Cost=
          5 Card=922)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        491  consistent gets
         23  physical reads
          0  redo size
     122594  bytes sent via SQL*Net to client
      12856  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1689  rows processed

Mais je ne m'explique toujours pas pourquoi le même désordre a des effets différents en test et en prod...