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