Performance sur simple requete

**orafrance** · 06/03/2008, 20h36

essaye de remplacer :

Code :

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

  AND HD.COINI NOT IN (SELECT NUCPT FROM INTERV I2 WHERE I2.CTINT='I')

par :

Code :

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

  AND NOT EXISTS (SELECT 1 FROM INTERV I2 WHERE I2.CTINT='I' AND I2.NUCPT = HD.COINI

Faudrait peut-être se passer de la jointure externe pour faire un UNION aussi

Et aussi :

AND DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')
AND DAHIS=(SELECT MAX(HC2.DAHIS)
FROM HISCPN HC2
WHERE HC1.NUFDP=HC2.NUFDP
AND HC1.NUBIX=HC2.NUBIX
AND HC2.NCCOF=7
AND HC2.DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON'))
) HC

La condition en rouge est inutile puisqu'elle est déjà dans la recherche du MAX en vert

Ajoute

Code :

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

AND HC.NUBIX = HN.NUBIX AND C.COINT = HD.COINC

2 conditions implicites mais dans les requêtes compliquées ça vaut le coup de les écrire quand même

Et il ne manque pas une jointure du genre :

Code :

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

CO.NUCPT = I.NUCPT

**zizou771** · 07/03/2008, 10h13

Code :

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

AND HC.NUBIX = HN.NUBIX AND C.COINT = HD.COINC

Comme tu me la indiqué, j'ai changer

Code :

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

HD.NUBIX=HC.NUBIX

en

Code :

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

HN.NUBIX = HC.NUBIX

En revanche

Code :

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

C.COINT = HD.COINC

est déjà présent.

La jointure

Code :

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

CO.NUCPT = I.NUCPT

n'est pas nécessaire.

Pour moi le pb reste cette sous requete:

Code :

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

1
2
(SELECT NUBIX,NUFDP,MHPND,CODEV FROM HISCPN HC1 WHERE NCCOF=7 
AND DAHIS=(SELECT MAX(HC2.DAHIS) FROM HISCPN HC2 WHERE HC1.NUFDP=HC2.NUFDP AND HC1.NUBIX=HC2.NUBIX AND HC2.NCCOF=7 AND HC2.DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')) ) HC

Alors que quand je la lance toute seul elle dure 5s.

**Yorglaa** · 07/03/2008, 10h20

T'as essayé avec le WITH ?

du coup tes valeurs "max" seraient précalculées et pourraient prendre moins de temps...

**orafrance** · 07/03/2008, 10h35

Envoyé par zizou771

Comme tu me la indiqué, j'ai changer

Code :

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

HD.NUBIX=HC.NUBIX

en

Code :

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

HN.NUBIX = HC.NUBIX

Je t'ai pas dit de changer mais d'ajouter

Envoyé par zizou771

La jointure

Code :

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

CO.NUCPT = I.NUCPT

n'est pas nécessaire.

je sais mais une jointure explicite peut aider le CBO... ce serait pas mal que tu suives les recommandations avant d'être imaginatif

Si tu as :
A=B
B=C
Ca vaut le coup d'ajouter A=C même si c'est l'évidence pour toi, ça peut aider l'optimiseur.

Envoyé par zizou771

Pour moi le pb reste cette sous requete:

Code :

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

1
2
(SELECT NUBIX,NUFDP,MHPND,CODEV FROM HISCPN HC1 WHERE NCCOF=7 
AND DAHIS=(SELECT MAX(HC2.DAHIS) FROM HISCPN HC2 WHERE HC1.NUFDP=HC2.NUFDP AND HC1.NUBIX=HC2.NUBIX AND HC2.NCCOF=7 AND HC2.DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')) ) HC

Alors que quand je la lance toute seul elle dure 5s.

pour moi c'est pas là qu'est le plus gros problème... y'a qu'à regarder l'explain plan... l'optimisation c'est pas un exercice qui se fait à l'instinct uniquement

**zizou771** · 07/03/2008, 10h38

oui c aussi long avec le with.
j'comprend pas ce qui cloche

**orafrance** · 07/03/2008, 10h41

Envoyé par zizou771

j'comprend pas ce qui cloche

que tu t'évertues à pas vouloir écouter nos conseils peut-être

**orafrance** · 07/03/2008, 10h42

Applique ce que je t'ai dit, teste à nouveau et envoie le plan d'exécution et la requête à chaque nouvelle tentative STP

**zizou771** · 07/03/2008, 10h47

j'applique j'applique, voici la dernière requete avec l'explain plan:

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
34
35
36
37
38
39
40
WITH HC AS 
(SELECT NUBIX,NUFDP,MHPND,CODEV FROM HISCPN HC1 WHERE NCCOF=7 
AND DAHIS=(SELECT MAX(HC2.DAHIS) FROM HISCPN HC2 WHERE HC1.NUFDP=HC2.NUFDP AND HC1.NUBIX=HC2.NUBIX AND HC2.NCCOF=7 AND HC2.DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')) ) 
SELECT to_char(to_date('29/02/2008', 'DD/MM/YYYY'),'YYYYMMDD') "Business day", 
		'ACOMITY' "REC type", 
		I.COINT "Legal Entity", 
		'ACC' "Account description", 
		HD.NUCPT "Account", 
		DECODE(C.FSANG,'NET','N','GROSS','G','') "Net/Gross client", 
		CO.RGCOD "Segregated", 
		C.RGCOD "Regulatory Code", 
		C.CRDS "Counterparty", 
		C.CRDSP "Cost Center", 
		HC.CODEV "Currency", 
		'' "deal_id", 
		'' "int_deal_id", 
		'' "Product", 
		'' "Instrument", 
		'' "Buy/Sell", 
		'' "Put/Call", 
		'' "EXC/OTC", 
		'' "Give-out", 
		'' "Collateral type", 
		DECODE(C.HSACT,'O','I','N','E','') "Internal", 
		ROUND(SUM(HC.MHPND),2) "Amount" 
FROM INTERV I, HISNEG HN, HISDEP HD, CFLAID C, COMPTE CO, HC
WHERE I.CTINT='G' AND HD.DATRA <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON') 
AND HC.MHPND!=0 
AND HD.COINC=C.COINT AND HD.NUCPT=C.NUCPT (+) AND NOT EXISTS (SELECT 1 FROM INTERV I2 WHERE I2.CTINT='I' AND I2.NUCPT = HD.COINI)
AND CO.COINT=HD.COINC AND HD.NUCPT=CO.NUCPT AND HN.NUBIX=HD.NUBIX AND HD.NUFDP=HC.NUFDP AND HD.NUBIX=HC.NUBIX AND HN.NUBIX=HC.NUBIX
GROUP BY 
		HD.NUCPT ,
		I.COINT , 
		DECODE(C.FSANG,'NET','N','GROSS','G','') , 
		CO.RGCOD, 
		C.RGCOD, 
		C.CRDS , 
		C.CRDSP , 
		HC.CODEV , 
		DECODE(C.HSACT,'O','I','N','E','')

**orafrance** · 07/03/2008, 10h55

t'as pas d'index sur HISCPN.NCCOF ou HISCPN.DAHIS ? Et sur INTERV.CTINT ou INTERV.NUCPT ?

Il manque encore la jointure :

Code :

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

AND C.COINT = CO.COINT

je pense aussi que :

Code :

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

AND HD.DATRA <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON')

est inutile puisque la condition sur UBIXDAY.Prevday existe déjà dans la recherche du MAX et que tu joins HC avec HD par un index unique apparemment.

essaye aussi :

Code :

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

AND CO.NUCPT =C.NUCPT (+)

**zizou771** · 07/03/2008, 11h00

j'ai rajouté le dernier bout de code.
En index sur HISCPN & INTERV j'ai ceci:

**orafrance** · 07/03/2008, 11h02

Que donne :

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
34
35
36
37
38
39
40
41
SELECT to_char(to_date('29/02/2008', 'DD/MM/YYYY'),'YYYYMMDD') "Business day", 
		'ACOMITY' "REC type", 
		I.COINT "Legal Entity", 
		'ACC' "Account description", 
		HD.NUCPT "Account", 
		DECODE(C.FSANG,'NET','N','GROSS','G','') "Net/Gross client", 
		CO.RGCOD "Segregated", 
		C.RGCOD "Regulatory Code", 
		C.CRDS "Counterparty", 
		C.CRDSP "Cost Center", 
		HC.CODEV "Currency", 
		'' "deal_id", 
		'' "int_deal_id", 
		'' "Product", 
		'' "Instrument", 
		'' "Buy/Sell", 
		'' "Put/Call", 
		'' "EXC/OTC", 
		'' "Give-out", 
		'' "Collateral type", 
		DECODE(C.HSACT,'O','I','N','E','') "Internal", 
		HC.MHPND "Amount" 
FROM INTERV I, HISNEG HN, HISDEP HD, CFLAID C, COMPTE CO, HISCPN HC
WHERE I.CTINT='G' 
AND HC.NCCOF=7 
AND HC.MHPND!=0 
AND HD.COINC=C.COINT 
AND CO.COINT=HD.COINC 
AND HD.NUCPT=C.NUCPT (+) 
AND HD.NUCPT=CO.NUCPT 
AND HD.NUFDP=HC.NUFDP 
AND NOT EXISTS (SELECT 1 FROM INTERV I2 WHERE I2.CTINT='I' AND I2.NUCPT = HD.COINI)
AND HN.NUBIX=HD.NUBIX 
AND HD.NUBIX=HC.NUBIX 
AND HN.NUBIX=HC.NUBIX
AND HC.DAHIS=(SELECT MAX(DAHIS) 
				FROM HISCPN  
			   WHERE HC.NUFDP=NUFDP 
			 	 AND HC.NUBIX=NUBIX 
				 AND NCCOF=7 
				 AND DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON'))

Et en ajoutant :

Code :

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

AND CO.NUCPT=C.NUCPT (+)

**orafrance** · 07/03/2008, 11h06

Envoyé par zizou771

j'ai rajouté le dernier bout de code.
En index sur HISCPN & INTERV j'ai ceci:

pour HISCPN ça devrait être suffisant par contre sur INTERV il manque peut-être un index sur CTINT,NUCPT (ajoute NUCPT à INTERV2)

**zizou771** · 07/03/2008, 11h08

je n'ai pas mis

Code :

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

AND CO.NUCPT=C.NUCPT (+)

car cela me sort une erreur de jointure.

voici l'explain plan:

**orafrance** · 07/03/2008, 11h10

Ajoute l'index sur INTERV, il est là ton problème.

**zizou771** · 07/03/2008, 11h16

mais INTERV ne me sert qu'as rajouté un seul champs. Si je le zappe le pb est toujours présent. Je viens de lancer cette requête qui est aussi long:

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
34
35
36
37
38
39
40
41
42
SELECT to_char(to_date('29/02/2008', 'DD/MM/YYYY'),'YYYYMMDD') "Business day", 
		'ACOMITY' "REC type", 
	--	I.COINT "Legal Entity", 
		'ACC' "Account description", 
		HD.NUCPT "Account", 
		DECODE(C.FSANG,'NET','N','GROSS','G','') "Net/Gross client", 
		CO.RGCOD "Segregated", 
		C.RGCOD "Regulatory Code", 
		C.CRDS "Counterparty", 
		C.CRDSP "Cost Center", 
		HC.CODEV "Currency", 
		'' "deal_id", 
		'' "int_deal_id", 
		'' "Product", 
		'' "Instrument", 
		'' "Buy/Sell", 
		'' "Put/Call", 
		'' "EXC/OTC", 
		'' "Give-out", 
		'' "Collateral type", 
		DECODE(C.HSACT,'O','I','N','E','') "Internal", 
		HC.MHPND "Amount" 
FROM --INTERV I,
 HISNEG HN, HISDEP HD, CFLAID C, COMPTE CO, HISCPN HC
WHERE --I.CTINT='G' AND 
HC.NCCOF=7 
AND HC.MHPND!=0 
AND HD.COINC=C.COINT 
AND CO.COINT=HD.COINC 
AND HD.NUCPT=C.NUCPT (+) 
AND HD.NUCPT=CO.NUCPT 
AND HD.NUFDP=HC.NUFDP 
AND NOT EXISTS (SELECT 1 FROM INTERV I2 WHERE I2.CTINT='I' AND I2.NUCPT = HD.COINI)
AND HN.NUBIX=HD.NUBIX 
AND HD.NUBIX=HC.NUBIX 
AND HN.NUBIX=HC.NUBIX
AND HC.DAHIS=(SELECT MAX(DAHIS) 
				FROM HISCPN  
			   WHERE HC.NUFDP=NUFDP 
			 	 AND HC.NUBIX=NUBIX 
				 AND NCCOF=7 
				 AND DAHIS <= UBIXDAY.Prevday(to_char(to_date('29/02/2008', 'DD/MM/YY'),'DD-MON-YY'),'LON'))

**orafrance** · 07/03/2008, 11h17

En effet, j'avais pas vu que l'index sur HISCPN n'est pas correct... tu dois mettre DAHIS en dernier pour pouvoir l'utiliser dans la jointure avec HISNEG

Pour avancer tu dois trouver et traiter les FULL TABLE SCAN

**zizou771** · 07/03/2008, 11h21

ah c'est beaucoup mieux en effet...merci

Mais pourquoi le mettre en dernier?
Et je peux pas créer un dexieme index NUBIX, NUFDP, DAHIS en + de DAHIS, NUBIX, NUFDP

**orafrance** · 07/03/2008, 11h24

parce que l'index est sur : DAHIS, NUBIX, NUCPT et que tu n'utilises que les 2 dernières colonnes. Or l'arbre de l'index commence sur DAHIS ce qui évidemment n'arrange pas les affaires d'Oracle. Aparemment NUBIX, NUCPT ont une signification fonctionnel forte, alors il faut les mettres en 1°. Si tu fais des accès sur DAHIS uniquement dans d'autres requêtes alors il faut que tu crées un index à part pour lui

ça ressemble à quoi maintenant avec ta requête initiale plus les jointures que je t'ai fait ajouter ? Avec le NOT IN et avec le NOT EXISTS ?

**zizou771** · 07/03/2008, 11h30

Et je peux créer un 2nd index NUBIX, NUFDP, DAHIS en + de DAHIS, NUBIX, NUFDP, ca cause pas de pb?

voila l'explain plan:

**orafrance** · 07/03/2008, 11h32

Envoyé par zizou771

Et je peux créer un 2nd index NUBIX, NUFDP, DAHIS en + de DAHIS, NUBIX, NUFDP, ca cause pas de pb?

voila l'explain plan:

bah c'est nul

Tu crées 2 mêmes indexes avec juste l'ordre qui change, c'est idéal pour perdre le CBO... pourquoi pas : (NUBIX, NUFDP, DAHIS) et DAHIS seul ? Ou même (NUBIX, NUFDP) + DAHIS

T'as toujours ton FTS sur HISCPN ?

Performance sur simple requete

SQL Oracle

Discussions similaires

Partager

Partager