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:
VCHKCL est une vue. voici sa définition:
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'))
Les tables MATCLO, FICDEP et FICNEG sont partitionnées by range sur le champ COMAR.
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;
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:
La requête reste bloquée pendant des heures.
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 | | | ------------------------------------------------------------------------------------------------------------------
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:
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?
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 | | | -------------------------------------------------------------------------------------------------------------------
je dois fournir une explication au client et là je n'en ai pas vraiment.
merci de votre aide
Partager