Optimisation vue matérialisées [Résolu]

[solkarlus]

Bonjour à tous,

j'ai une petite question:

actuellement j'ai des vues materialisées du type :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
CREATE MATERIALIZED VIEW MV_CONTRAT
TABLESPACE mon_tablespace
NOCACHE
LOGGING
NOPARALLEL
REFRESH FORCE ON DEMAND
AS 
SELECT 
ctr.*,
ma_fonction1 (ctr.id_ctr),
ma_fonction2 (ctr.id_ctr)
FROM CONTRAT ctr

j'ai optimiser au max les fonctions en utilisant les fonction analytiques d'oracle et en creant des indexes sur les champs scannés pour obtenir un plan sans full scan.

le truc c'est que j'ai une table de plus de 5 millions d'enregistrement et donc 5 millions d'appels de ma fonction 1, 2 etc ...

j'en ai pour en tout pour plus de 3 h de temps de refresh.

j'ai mis a jour le plan oracle :

analyze table CONTRAT compute statistics.

bref je suis à cours d'idee ...

je travail sur Oracle 9.2

[orafrance]

déjà remplace ANALYZE par DBMS_STATS. Ensuite, t'as essayé sans les fonctions ? Ca prend combien de temps dans ce cas ? Tu aurais pas le code des fonctions par hasard et une trace de leur exécution ?

[solkarlus]

En fait sans les fonctions c'est immédiat.

le truc c'est qu'il passe bien par le snapshot log de ma table CONTRAT mais recalcule quand meme les fonctions du coup je ne peux pas faire de refresh fast.
Dois je faire appel à mes fonctions dans la création du snapshot log ?

le code de mes fonctions :

Code :

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
CREATE OR REPLACE FUNCTION FXCP_PLUS_MOINS_VALUE_CONTRAT(VAL_ACQUISE_IN IN NUMBER, MT_TOT_VERSE_IN IN NUMBER,MT_ECHURAC_IN IN NUMBER ) RETURN NUMBER IS
/******************************************************************************
   NAME:       FXCP_PLUS_MOINS_VALUE_CONTRAT
   PURPOSE:
 
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        05/07/2007          1. Created this function.
 
   NOTES: CALCUL DE LA PLUS MOINS VALUE DU CONTRAT
******************************************************************************/
 
   BEGIN
 
        RETURN (NVL(VAL_ACQUISE_IN,0) - NVL(MT_TOT_VERSE_IN,0) + NVL(MT_ECHURAC_IN,0) )/( NVL(MT_TOT_VERSE_IN,0) - NVL(MT_ECHURAC_IN,0) )*100;
 
   EXCEPTION
   WHEN ZERO_DIVIDE THEN        
        RETURN(NULL);
   WHEN NO_DATA_FOUND THEN
        RETURN(NULL);
   WHEN OTHERS THEN        
		RETURN(NULL);
   END;
/

Code :

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
CREATE OR REPLACE FUNCTION FXCP_TX_PART_UC(IDSOUSUNIQUE IN VARCHAR2) RETURN NUMBER IS 
/******************************************************************************
   NAME:       FXCP_TX_PART_UC
   PURPOSE:    
 
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        05/07/2007          1. Created this function.
 
   NOTES: CALCUL TAUX DE REPARTITION DU SUPPORT UC PAR RAPPORT À L'ENSEMBLE DES SUPPORTS
******************************************************************************/ 
TX NUMBER;
   BEGIN
        SELECT SUM(DECODE(TYPE_SUP,'UC',NVL(MT_SUPPORT,0),0))/DECODE(SUM(NVL(MT_SUPPORT,0)),0,1,SUM(NVL(MT_SUPPORT,0)))*100 INTO TX 
        FROM TAEPREPARTSUP
        WHERE ID_SOUS_UNIQUE=IDSOUSUNIQUE; 
   RETURN(TX);
   EXCEPTION     
   WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('LE CONTRAT '||IDSOUSUNIQUE||' A GENERE DANS LE FONCTION FXCP_TX_PART_UC - '||SQLCODE||' -ERROR- '||SQLERRM);
        RETURN(NULL);
   WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('LE CONTRAT '||IDSOUSUNIQUE||' A GENERE DANS LE FONCTION FXCP_TX_PART_UC - '||SQLCODE||' -ERROR- '||SQLERRM);
        RETURN(NULL);      
   END;         
/

[orafrance]

qu'est ce qui t'empêche de remplacer la 1° fonction par ce qu'elle fait dans la vue ?

La 2° ne peut pas être remplacer par un traitement massif lancé à chaque refresh de la vue ou mieux, également intégré à la requête de la vue grâce aux fonctions analytiques ?

[solkarlus]

Les fonctions sont exteriorisées de la vue matérialisées pour pouvoir etre capitalisées par d'autre projet.

le truc c'est qu'on ne fait pas toujours ce que l'on veut à son travail....

en fait j'ai l'impression que le fait d'appeler des fonctions dans la MV empeche Oracle de faire de fast refresh...

[orafrance]

bien sûr qu'Oracle peut pas faire un fast refresh... soit tu conserves les fonctions mais tu mets à jour les colonnes de manière globale après chaque refresh soit tu intégres le code des fonctions dans la vue.

On fait pas toujours ce qu'on veut mais on a le droit de démontrer que le choix technique imposé ne convient pas à son besoin et le remettre en cause

[solkarlus]

Imposer sa solution est bien joli, encore faut il avoir le poids hierarchique, car en pratique un developpeur n'aura jamais raison face à l'architecte n+2.
j'ajouterais que c'est bien dommage mais typique francais.

bref sur ce projet, l'archi a d'abord preconniser des vues logiques, puis comme ca plombait les perfs des services J2EE, on est passé à des vues materialisées, avec tout ce que cela implique sur les job de refresh, de temps et de decalage de données ainsi que dispo des services, et maintenant que l'on passe au test de perf sur volumetrie de prod on souleve le pb de temps de refreh en quotidien.

en fait de maniere plus generale ils veulent mettre en place une couche de precalcul accessible au Web , mais bidouille maison ( OLAP connais pas .... )
et comme d'ab tt la mer.... retombe sur le dos des dev.

bref je m'emporte et ca fait pas avancer le schmil ...

[solkarlus]

Apres tests il serait effectivement impossible de faire du fast refresh et à cause de ceci :
ORA-12015: cannot create a fast refresh snapshot from a complex query

donc ma vue est optimisée au max vue qu'elle ne peut pas etre autrement ! CQFD
c'est l'architecte qui va etre content !

pour moi le probleme clos.

merci à toute la communauté pour son aide !!

[boussafi]

salem

tu peux essaye l'option ON COMMIT puisque tu as une seulle table, cette option aide a faire refresh lorsqu'on commite les transactions.
donc tu as juste les 3 heures de creation de la Vue materialisee,puis qlq secondes de refreshs synchronisee.

Bon courage
Zoheir BOUSSAFI

[boussafi]

solkarlus !;je voudrais savoir si tu as essayer l'option ON COMMIT

bon courage