Discussion :

[farenheiit]

Bonjour,

Je travaille pour un éditeur de logiciel.
Un de nos clients (en 10g R2) a vu sa session bloquer sur la requête suivante:

Code :

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

SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM VCHKCL X WHERE X.COMAR IN ('ICE') AND TRUNC(X.DATOP,'DD')<=TO_DATE('09062009','DDMMYYYY') AND TRUNC(X.DANEG,'DD')> TO_DATE('09062009','DDMMYYYY'))

VCHKCL est une vue. voici sa définition:

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
 
CREATE OR REPLACE VIEW VCHKCL
(COMAR,NCOCL,NFICL,NUCON,NUFDP,DANEG,COINF,COINC,
 CNACT,CMECH,CAECH,CSOPT,MTSNA,QTCLO,COTSJ,DATOP,NUCPT,NUBIX,NUBCL)
AS 
SELECT 
M.COMAR,D.NUCON,D.NUFDP,M.NUCON,M.NUFDP,NVL(C.DAEOD,C.DATOP),N.COINF,D.COINC,
N.CNACT,N.CMECH,N.CAECH,N.CSOPT,N.MTSNA,M.QTCLO,A.COTSJ,NVL(N.DAEOD,N.DATOP),D.NUCPT,M.NUBIX,M.NUBCL
FROM 
MATCLO M,
FICDEP D,
FICNEG N,
FICNEG C,
NATACF A 
WHERE 
D.NUBIX=M.NUBCL AND 
D.NUFDP=M.NFICL AND 
N.NUBIX=M.NUBCL AND 
C.NUBIX=M.NUBIX AND 
A.CNACT=N.CNACT;

Les tables MATCLO, FICDEP et FICNEG sont partitionnées by range sur le champ COMAR.
La table NATACF n'est pas partitionnée.

En regardant dans v$session_wait j'ai vu que la session était bloquée sur l'évenement LATCH cache buffer chains.

Le plan d'execution de la requête est le suivant:

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
 
Avant 
------------------------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT                       |         |       |       |   339 (100)|          |       |       | 
|   1 |  FILTER                                |         |       |       |            |          |       |       | 
|   2 |   FAST DUAL                            |         |     1 |       |     2   (0)| 00:00:01 |       |       | 
|   3 |   NESTED LOOPS                         |         |     1 |   146 |   337   (0)| 00:00:02 |       |       | 
|   4 |    NESTED LOOPS                        |         |     1 |   115 |   336   (0)| 00:00:02 |       |       | 
|   5 |     NESTED LOOPS                       |         |     1 |    72 |   334   (1)| 00:00:02 |       |       | 
|   6 |      MERGE JOIN CARTESIAN              |         |     1 |    35 |   329   (0)| 00:00:02 |       |       | 
|   7 |       INDEX FULL SCAN                  | FICDEP1 |     1 |    26 |   315   (0)| 00:00:02 |       |       | 
|   8 |       BUFFER SORT                      |         | 45493 |   399K|    14   (0)| 00:00:01 |       |       | 
|   9 |        INDEX FULL SCAN                 | NATACF1 | 45493 |   399K|    14   (0)| 00:00:01 |       |       | 
|  10 |      PARTITION RANGE ALL               |         |    13 |   481 |     4   (0)| 00:00:01 |     1 |    45 | 
|  11 |       TABLE ACCESS BY LOCAL INDEX ROWID| FICNEG  |    13 |   481 |     4   (0)| 00:00:01 |     1 |    45 | 
|  12 |        INDEX RANGE SCAN                | FICNEG2 |     1 |       |     4   (0)| 00:00:01 |     1 |    45 | 
|  13 |     PARTITION RANGE SINGLE             |         |     1 |    43 |     2   (0)| 00:00:01 |   KEY |   KEY | 
|  14 |      TABLE ACCESS BY LOCAL INDEX ROWID | MATCLO  |     1 |    43 |     2   (0)| 00:00:01 |   KEY |   KEY | 
|  15 |       INDEX RANGE SCAN                 | MATCLO2 |   899 |       |     1   (0)| 00:00:01 |   KEY |   KEY | 
|  16 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | FICNEG  |     1 |    31 |     1   (0)| 00:00:01 | ROW L | ROW L | 
|  17 |     INDEX UNIQUE SCAN                  | FICNEG1 |     1 |       |     1   (0)| 00:00:01 |       |       | 
------------------------------------------------------------------------------------------------------------------

La requête reste bloquée pendant des heures.
Il n'y a pas de stats sur les tables MATCLO, FICDEP et FICNEG mais le paramètre OPTIMZER_DYNAMIC_SAMPLING est positionné à 2.
Par contre, les stats sont présentes pour NATACF.

Cette requête est exécutée via notre progiciel.
En exécutant la même requête à part sous sqlplus et sur la même base du client celle ci s'exécute instantanément.

En killant la session et en relancant l'application, la session reste bloquée tjr sur la même requête et tjr avec le même événement d'attente.

En modifiant la vue pour y rajouter les critères de partitionnement le problème ne se pose plus et le plan d'execution devient le suivant:

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
Après 
 
------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                               | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                        |         |       |       |     8 (100)|          |       |       | 
|   1 |  FILTER                                 |         |       |       |            |          |       |       | 
|   2 |   FAST DUAL                             |         |     1 |       |     2   (0)| 00:00:01 |       |       | 
|   3 |   NESTED LOOPS                          |         |     1 |    92 |     6   (0)| 00:00:01 |       |       | 
|   4 |    NESTED LOOPS                         |         |     1 |    78 |     5   (0)| 00:00:01 |       |       | 
|   5 |     NESTED LOOPS                        |         |     1 |    49 |     4   (0)| 00:00:01 |       |       | 
|   6 |      NESTED LOOPS                       |         |     1 |    28 |     3   (0)| 00:00:01 |       |       | 
|   7 |       PARTITION RANGE SINGLE            |         |    20 |   220 |     1   (0)| 00:00:01 |   KEY |   KEY | 
|   8 |        TABLE ACCESS BY LOCAL INDEX ROWID| FICDEP  |    20 |   220 |     1   (0)| 00:00:01 |   KEY |   KEY | 
|   9 |         INDEX RANGE SCAN                | FICDEP4 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY | 
|  10 |       PARTITION RANGE SINGLE            |         |     1 |    17 |     1   (0)| 00:00:01 |   KEY |   KEY | 
|  11 |        TABLE ACCESS BY LOCAL INDEX ROWID| MATCLO  |     1 |    17 |     1   (0)| 00:00:01 |   KEY |   KEY | 
|  12 |         INDEX RANGE SCAN                | MATCLO2 |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY | 
|  13 |      TABLE ACCESS BY GLOBAL INDEX ROWID | FICNEG  |     1 |    21 |     1   (0)| 00:00:01 | ROW L | ROW L | 
|  14 |       INDEX UNIQUE SCAN                 | FICNEG1 |     1 |       |     1   (0)| 00:00:01 |       |       | 
|  15 |     TABLE ACCESS BY GLOBAL INDEX ROWID  | FICNEG  |     1 |    29 |     1   (0)| 00:00:01 | ROW L | ROW L | 
|  16 |      INDEX UNIQUE SCAN                  | FICNEG1 |     1 |       |     1   (0)| 00:00:01 |       |       | 
|  17 |    TABLE ACCESS BY INDEX ROWID          | NATACF  |     1 |    14 |     1   (0)| 00:00:01 |       |       | 
|  18 |     INDEX UNIQUE SCAN                   | NATACF1 |     1 |       |     1   (0)| 00:00:01 |       |       | 
-------------------------------------------------------------------------------------------------------------------

Pourquoi ce pb s'est il posé? pourquoi dans le premier plan d'execution fait-il un produit cartesien ? pourquoi la session reste en attente sur l'événement LATCH cache Buffer chains ? Est-ce dû à des statistiques fausses? faut-il augmenter le degré du dynamic_sampling?

je dois fournir une explication au client et là je n'en ai pas vraiment.

merci de votre aide

[orafrance]

Apparemment dans un cas il utilise l'index global et parcourt toutes les partitions contre l'utilisation de l'index local dans le deuxième cas.

[farenheiit]

Quel rapport avec l'evenement d'attente Latch cache buffer chains?
pourquoi fait il un produit cartesien s'il parcourt toutes les partitions ?

[orafrance]

c'est pas un produit cartesian, c'est juste une méthode de rapprochement de 2 FULL INDEX. Par contre comme tu parcours tous les blocs de l'index ça fait pas mal de bloc en mémoire à charger/décharger

[pifor]

Si vous êtes absolument sûr que la requête est complètement bloquée dans un cas, alors c'est forcément un bug.

[farenheiit]

Si vous êtes absolument sûr que la requête est complètement bloquée dans un cas, alors c'est forcément un bug.

un bug oracle?

[orafrance]

Si vous êtes absolument sûr que la requête est complètement bloquée dans un cas, alors c'est forcément un bug.

Je ne vois pas pourquoi ? Si dans le deuxième cas, il limite le nombre de partitions parcourus c'est forcément moins bloquant

Les stats sont bien à jour ?

Déjà l'IFS n'est peut-être pas pertinent, faudrait essayer un FULL TABLE SCAN dans ce cas

[farenheiit]

c'est pas un produit cartesian, c'est juste une méthode de rapprochement de 2 FULL INDEX. Par contre comme tu parcours tous les blocs de l'index ça fait pas mal de bloc en mémoire à charger/décharger

Sur ORAfaq on me dit qu'il fait bien un produit cartesien car l'Optimiseur part du principe que le full index scan ne lui retournera qu'une seule ligne, et donc un produit cartesien avec une seule ligne d'un côté n'est pas du tout couteux