optimisation sql et vue matéralisée

**bertini15** · 08/09/2009, 01h07

bonjour,
sous oralce 9.2, nous avons une requête sql ("ma_requete") qui met 10 min à s'exécuter car elle parcourt plusieurs partitions. trop long pour nos utilisateurs, alors pour y remédier, j'ai créé une vue matérialisée avec le code suivant:

Code :

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

1
2
3
4
5
6
7
8
 
CREATE MATERIALIZED VIEW ma_MV
   PARALLEL (degree 2)
   BUILD IMMEDIATE
   REFRESH COMPLETE
   ENABLE QUERY REWRITE
AS
   ma_requete ;

j'ai modifié les paramètres du moteur oracle comme suit:

Code :

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

1
2
3
4
 
   ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FORCE;
   ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;
   ALTER SYSTEM SET OPTIMIZER_MODE = CHOOSE;

Une fois la vue créée, je repasse la requete de départ (ma_requete) qui s'exécute encore en 10 min.
Lorsque je fais un explain plan de la requete, il me dit bien que je passe bien par la vue matérialisée.
Je ne comprend donc pas pourquoi la requête met toujours autant de temps. Je pensais qu'en passant par la vue matérialisée grâce à la réécriture de requête, le moteur oracle ferait un simple "select * from ma_MV" qui serait ainsi beaucoup plus rapide.

aurais-je zapper quelques chose à faire ? Pourriez-vous SVP m'éclairer sur ce point ?

L'objectif est que cela soit transparent pour les utilisateurs (i.e pas select directement dans la MV) lorsqu'ils font leurs requêtes. Je suis donc preneur de toute solution pouvant améliorer les performances de cette requête sql.

NB: le code da la requête en lui même est déjà optimisé.

Merci d'avance de votre aide.

**mnitu** · 08/09/2009, 08h59

Il faut plus des détails sur ma_requête, etc. pour diagnostiquer votre problème.

**pachot** · 08/09/2009, 09h19

Bonjour,
Si le plan d'execution montre la vue, c'est qu'elle est utilisée.
Il faudrait voir où passent ces 10 minutes (autotrace, tkprof,...).
Elle ramène combien de lignes ta requête ?
Cordialement,
Franck.

**pacmann** · 08/09/2009, 10h03

Salut !

Juste pour vérifier :
As-tu lancé l'explain plan dans le même contexte que l'exécution de la requête ?
Si ta requête est exécutée par une application quelconque, il se peut que les paramètres de la session soient altérés, par rapport à l'exécution de la requête sous SQL Plus par exemple.

**bertini15** · 08/09/2009, 10h39

Merci pour vos réponses.

ci-dessous ma requête.

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
SELECT
FT_REPART_AP_ARR_CORR.D_NDEBOURS,
FE_TIERS_SIGNATAIRE.D_CTIERS,
SUM(DECODE(FT_REPART_AP_ARR_CORR.D_CSENS,'60',FT_REPART_AP_ARR_CORR.D_NMTVENTHT*-1,FT_REPART_AP_ARR_CORR.D_NMTVENTHT)),
SUM(DECODE(FT_REPART_AP_ARR_CORR.D_CSENS,'60',(FT_REPART_AP_ARR_CORR.D_NMTVENTTTC-FT_REPART_AP_ARR_CORR.D_NMTVENTHT)*-1,FT_REPART_AP_ARR_CORR.D_NMTVENTTTC-FT_REPART_AP_ARR_CORR.D_NMTVENTHT)),
FE_TIERS_SIGNATAIRE.D_LTIERS,
FE_GL_CAFIS_UNION.D_CCOMPTE,
FE_NAT_CHARGES_CIPOSTE.D_CCOMPO3,
FE_NAT_CHARGES_CIPOSTE.D_LCOMPO,
TO_NUMBER(FE_TAUX_TVA_CIPOSTE.D_LCOMPO)/100,
FE_METIER.D_CCOMPO1,
FE_PARAM.D_DFACT_MIN,
FE_PARAM.D_DFACT_MAX,
SUM(DECODE(FT_REPART_AP_ARR_CORR.D_CSENS,'60',FT_REPART_AP_ARR_CORR.D_NMTVENTTVA_ND*-1,FT_REPART_AP_ARR_CORR.D_NMTVENTTVA_ND)),
FE_PARAM.C_DTRAIT
FROM
(
SELECT FT_REPART_AP_ARR.D_NCAFIS, FT_REPART_AP_ARR.C_NTIERS_LOC_CORR as C_NTIERS_LOCCORR, FT_REPART_AP_ARR.C_NTIERS_LOC,FT_REPART_AP_ARR.C_NTIERS_PRE, FT_REPART_AP_ARR.D_CCENTRE_LOC_CORR, --FT_CAFIS_INTER.D_CCENTRE_PREN, FT_REPART_AP_ARR.D_CCENTRE_IMM, FT_REPART_AP_ARR.C_NTIERS_PRO, FT_REPART_AP_ARR.D_DBVENTIL, FT_REPART_AP_ARR.D_DFNVENTIL, FT_REPART_AP_ARR.C_NNATCHARG_PAR, FT_REPART_AP_ARR.D_NMTVENTHT, FT_REPART_AP_ARR.D_NMTVENTTVA_ND, FT_REPART_AP_ARR.D_NMTVENTTTC, FT_REPART_AP_ARR.D_NFLAG_PROP, FT_REPART_AP_ARR.D_CCOMPTE, FT_REPART_AP_ARR.D_CSENS, FT_REPART_AP_ARR.D_CNUMFAC, FT_REPART_AP_ARR.D_NMTARR, FT_REPART_AP_ARR.D_NDEBOURS, FT_REPART_AP_ARR.D_NMTARR_TVA, FT_REPART_AP_ARR.D_NTAUX_TVA, FT_REPART_AP_ARR.D_NTAUX_TVA_ND, FT_REPART_AP_ARR.C_NTRAIT, FT_REPART_AP_ARR.D_NTAUX_TVA_INDIC, FT_REPART_AP_ARR.D_NNUM_FACT, FT_REPART_AP_ARR.D_CTYPE_METIER, FT_REPART_AP_ARR.D_NTAUXFG, FT_REPART_AP_ARR.D_NMTTHTFG, FT_REPART_AP_ARR.D_DECHEANFG, FT_REPART_AP_ARR.D_NTVAFG, FT_REPART_AP_ARR.D_DEMIFACFG, FT_REPART_AP_ARR.D_NNUMFACFG, FT_REPART_AP_ARR.D_LNOM_FLUX FROM FT_REPART_AP_ARR
) FT_REPART_AP_ARR_CORR,
(
SELECT D_NCAFIS, D_CTYPENREG, D_CENTITEGLCAFIS, D_CCOMPTE, D_CCENTRE, D_CNUMCNT, D_CNUMLCT, D_DEFFET, D_NMONTANT, D_CSENS, D_CACTION, D_CTYPECR, D_CNFOUR, D_LNOMFOUR, D_CNUMFAC, D_DFACT, D_DDEBPREST, D_DFINPREST, D_CMARQUAGE, D_CETAT, C_NTRAIT, D_CTYPELIGNE, D_DTRAIT_INIT,D_LNOM_FLUX,D_CINDTVA FROM FE_GL_CAFIS UNION ALL SELECT D_NCAFIS, D_CTYPENREG, D_CENTITEGLCAFIS, D_CCOMPTE, D_CCENTRE, D_CNUMCNT, D_CNUMLCT, D_DEFFET, D_NMONTANT, D_CSENS, D_CACTION, D_CTYPECR, D_CNFOUR, D_LNOMFOUR, D_CNUMFAC, D_DFACT, D_DDEBPREST, D_DFINPREST, D_CMARQUAGE, 'N', C_NTRAIT, D_CTYPE_LIGNE, D_DTRAIT_INIT,D_LNOM_FLUX,D_CINDTVA FROM FE_HISTO_CAFIS
) FE_GL_CAFIS_UNION,
FE_TABREF FE_NAT_CHARGES_CIPOSTE,
FE_TABREF FE_TAUX_TVA_CIPOSTE,
FE_TABREF FE_METIER,
FE_PARAM,
FE_TIERS FE_TIERS_SIGNATAIRE
WHERE
( FT_REPART_AP_ARR_CORR.C_NTRAIT=FE_TIERS_SIGNATAIRE.C_NTRAIT and FT_REPART_AP_ARR_CORR.C_NTIERS_PRE=FE_TIERS_SIGNATAIRE.D_NTIERS )
AND ( FE_NAT_CHARGES_CIPOSTE.C_NTRAIT=FE_GL_CAFIS_UNION.C_NTRAIT and FE_NAT_CHARGES_CIPOSTE.D_CCOMPO1=SUBSTR(FE_GL_CAFIS_UNION.D_CCOMPTE,1,6) and FE_NAT_CHARGES_CIPOSTE.D_ICIPOSTE='O' )
AND ( FE_NAT_CHARGES_CIPOSTE.C_LCTABREF='fe_compte' )
AND ( FE_TAUX_TVA_CIPOSTE.C_NTRAIT(+)=FE_GL_CAFIS_UNION.C_NTRAIT and FE_TAUX_TVA_CIPOSTE.D_CCOMPO1(+)=substr(FE_GL_CAFIS_UNION.D_CCOMPTE,7,2) )
AND ( FE_TAUX_TVA_CIPOSTE.C_LCTABREF='fe_TVA_compte' )
AND ( FE_TIERS_SIGNATAIRE.C_NTRAIT=FE_METIER.C_NTRAIT and FE_TIERS_SIGNATAIRE.C_NMETIER_PAR=FE_METIER.D_NIDENT )
AND ( FE_GL_CAFIS_UNION.D_NCAFIS=FT_REPART_AP_ARR_CORR.D_NCAFIS and FE_GL_CAFIS_UNION.C_NTRAIT=FT_REPART_AP_ARR_CORR.C_NTRAIT )
AND ( FE_METIER.C_LCTABREF='fe_metier' )
AND ( FE_GL_CAFIS_UNION.C_NTRAIT=FE_PARAM.D_NTRAIT )
AND
(
FE_METIER.D_CCOMPO1 In ( 'LPGP' )
AND
TO_NUMBER(TO_CHAR(FE_PARAM.C_DTRAIT,'SYYYY')) In @prompt('Année de ventilation :','N',,Multi,Free,Persistent,,User:0)
AND
TO_NUMBER(TO_CHAR(FE_PARAM.C_DTRAIT,'MM')) In @prompt('Mois de ventilation :','N',,Multi,Free,Persistent,,User:1)
AND
( FE_PARAM.D_NTRAIT <> 2 )
AND
( FE_PARAM.D_CTYPETRAIT='CIPOSTE' )
)
GROUP BY
FT_REPART_AP_ARR_CORR.D_NDEBOURS,
FE_TIERS_SIGNATAIRE.D_CTIERS,
FE_TIERS_SIGNATAIRE.D_LTIERS,
FE_GL_CAFIS_UNION.D_CCOMPTE,
FE_NAT_CHARGES_CIPOSTE.D_CCOMPO3,
FE_NAT_CHARGES_CIPOSTE.D_LCOMPO,
TO_NUMBER(FE_TAUX_TVA_CIPOSTE.D_LCOMPO)/100,
FE_METIER.D_CCOMPO1,
FE_PARAM.D_DFACT_MIN,
FE_PARAM.D_DFACT_MAX,
FE_PARAM.C_DTRAIT

La MV est sur le même tablespace que les partitions des tables (plus de 50), est-ce que cela peut poser problème ?

Pour l'instant, je n'ai pas accès à l'environnement pour avoir le nombre de lignes retourné et testé autotrace.

Pour répondre à Pacmann, je travaille sous SQL Developer de Oracle. C'est avec cet outil que je lance ma requête et que je vois aussi le plan d'exécution via la table PLAN_TABLE.

Je ne pige toujours pas pourquoi le temps n'est pas diminué considérablement (par rapport aux 10 min de la requête normale) avec le FULL SCAN de la MV suite à la réécriture.

Merci d'avance de vos futures contributions.

**bertini15** · 10/09/2009, 09h18

Bonjour,

La requête me ramène 1535 lignes.
Je ne trouve toujours pas où passe les 10 min.

Aidez moi SVP.

Merci

**GoLDoZ** · 10/09/2009, 11h04

A quoi sert la vue ft_repart_ap_arr_corr? Vous pouvez intégrer directement la table ft_repart_ap_arr dans la requête principale et ne garder que les colonnes indispensables.

**farenheiit** · 10/09/2009, 11h08

combien de temps met la requête suivante:

Code :

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

select * from ma_MV;

**mnitu** · 10/09/2009, 13h21

Envoyé par bertini15

Bonjour,

La requête me ramène 1535 lignes.
Je ne trouve toujours pas où passe les 10 min.

Aidez moi SVP.

Merci

Exécute ta requête avec une trace SQL étendue. Ensuite tu le saurais en analysant le fichiers des traces (*.trc) ainsi généré.

**bertini15** · 10/09/2009, 17h06

Pour farenheiit, la requête

Code :

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

select * from ma_MV

est quasi instantanée, d'où mon incompréhension. Je vais essayer de voir avec les fichiers de traces comme proposé par mnitu.

Merci pour vos réponses.

**farenheiit** · 10/09/2009, 17h18

Et le plan d'execution c'est le même ?
Effectivement il faut tracer ta session:

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
ALTER SESSION SET events '10046 trace name context forever, level 8';
 
SELECT
FT_REPART_AP_ARR_CORR.D_NDEBOURS,
FE_TIERS_SIGNATAIRE.D_CTIERS,
SUM(DECODE(FT_REPART_AP_ARR_CORR.D_CSENS,'60',FT_REPART_AP_ARR_CORR.D_NMTVENTHT*-1,FT_REPART_AP_ARR_CORR.D_NMTVENTHT)),
SUM(DECODE(FT_REPART_AP_ARR_CORR.D_CSENS,'60',(FT_REPART_AP_ARR_CORR.D_NMTVENTTTC-FT_REPART_AP_ARR_CORR.D_NMTVENTHT)*-1,FT_REPART_AP_ARR_CORR.D_NMTVENTTTC-FT_REPART_AP_ARR_CORR.D_NMTVENTHT)),
FE_TIERS_SIGNATAIRE.D_LTIERS,
FE_GL_CAFIS_UNION.D_CCOMPTE,
FE_NAT_CHARGES_CIPOSTE.D_CCOMPO3,
FE_NAT_CHARGES_CIPOSTE.D_LCOMPO,
TO_NUMBER(FE_TAUX_TVA_CIPOSTE.D_LCOMPO)/100,
FE_METIER.D_CCOMPO1,
FE_PARAM.D_DFACT_MIN,
FE_PARAM.D_DFACT_MAX,
SUM(DECODE(FT_REPART_AP_ARR_CORR.D_CSENS,'60',FT_REPART_AP_ARR_CORR.D_NMTVENTTVA_ND*-1,FT_REPART_AP_ARR_CORR.D_NMTVENTTVA_ND)),
FE_PARAM.C_DTRAIT
FROM
(
SELECT FT_REPART_AP_ARR.D_NCAFIS, FT_REPART_AP_ARR.C_NTIERS_LOC_CORR AS C_NTIERS_LOCCORR, FT_REPART_AP_ARR.C_NTIERS_LOC,FT_REPART_AP_ARR.C_NTIERS_PRE, FT_REPART_AP_ARR.D_CCENTRE_LOC_CORR, --FT_CAFIS_INTER.D_CCENTRE_PREN, FT_REPART_AP_ARR.D_CCENTRE_IMM, FT_REPART_AP_ARR.C_NTIERS_PRO, FT_REPART_AP_ARR.D_DBVENTIL, FT_REPART_AP_ARR.D_DFNVENTIL, FT_REPART_AP_ARR.C_NNATCHARG_PAR, FT_REPART_AP_ARR.D_NMTVENTHT, FT_REPART_AP_ARR.D_NMTVENTTVA_ND, FT_REPART_AP_ARR.D_NMTVENTTTC, FT_REPART_AP_ARR.D_NFLAG_PROP, FT_REPART_AP_ARR.D_CCOMPTE, FT_REPART_AP_ARR.D_CSENS, FT_REPART_AP_ARR.D_CNUMFAC, FT_REPART_AP_ARR.D_NMTARR, FT_REPART_AP_ARR.D_NDEBOURS, FT_REPART_AP_ARR.D_NMTARR_TVA, FT_REPART_AP_ARR.D_NTAUX_TVA, FT_REPART_AP_ARR.D_NTAUX_TVA_ND, FT_REPART_AP_ARR.C_NTRAIT, FT_REPART_AP_ARR.D_NTAUX_TVA_INDIC, FT_REPART_AP_ARR.D_NNUM_FACT, FT_REPART_AP_ARR.D_CTYPE_METIER, FT_REPART_AP_ARR.D_NTAUXFG, FT_REPART_AP_ARR.D_NMTTHTFG, FT_REPART_AP_ARR.D_DECHEANFG, FT_REPART_AP_ARR.D_NTVAFG, FT_REPART_AP_ARR.D_DEMIFACFG, FT_REPART_AP_ARR.D_NNUMFACFG, FT_REPART_AP_ARR.D_LNOM_FLUX FROM FT_REPART_AP_ARR
) FT_REPART_AP_ARR_CORR,
(
SELECT D_NCAFIS, D_CTYPENREG, D_CENTITEGLCAFIS, D_CCOMPTE, D_CCENTRE, D_CNUMCNT, D_CNUMLCT, D_DEFFET, D_NMONTANT, D_CSENS, D_CACTION, D_CTYPECR, D_CNFOUR, D_LNOMFOUR, D_CNUMFAC, D_DFACT, D_DDEBPREST, D_DFINPREST, D_CMARQUAGE, D_CETAT, C_NTRAIT, D_CTYPELIGNE, D_DTRAIT_INIT,D_LNOM_FLUX,D_CINDTVA FROM FE_GL_CAFIS UNION ALL SELECT D_NCAFIS, D_CTYPENREG, D_CENTITEGLCAFIS, D_CCOMPTE, D_CCENTRE, D_CNUMCNT, D_CNUMLCT, D_DEFFET, D_NMONTANT, D_CSENS, D_CACTION, D_CTYPECR, D_CNFOUR, D_LNOMFOUR, D_CNUMFAC, D_DFACT, D_DDEBPREST, D_DFINPREST, D_CMARQUAGE, 'N', C_NTRAIT, D_CTYPE_LIGNE, D_DTRAIT_INIT,D_LNOM_FLUX,D_CINDTVA FROM FE_HISTO_CAFIS
) FE_GL_CAFIS_UNION,
FE_TABREF FE_NAT_CHARGES_CIPOSTE,
FE_TABREF FE_TAUX_TVA_CIPOSTE,
FE_TABREF FE_METIER,
FE_PARAM,
FE_TIERS FE_TIERS_SIGNATAIRE
WHERE
( FT_REPART_AP_ARR_CORR.C_NTRAIT=FE_TIERS_SIGNATAIRE.C_NTRAIT AND FT_REPART_AP_ARR_CORR.C_NTIERS_PRE=FE_TIERS_SIGNATAIRE.D_NTIERS )
AND ( FE_NAT_CHARGES_CIPOSTE.C_NTRAIT=FE_GL_CAFIS_UNION.C_NTRAIT AND FE_NAT_CHARGES_CIPOSTE.D_CCOMPO1=SUBSTR(FE_GL_CAFIS_UNION.D_CCOMPTE,1,6) AND FE_NAT_CHARGES_CIPOSTE.D_ICIPOSTE='O' )
AND ( FE_NAT_CHARGES_CIPOSTE.C_LCTABREF='fe_compte' )
AND ( FE_TAUX_TVA_CIPOSTE.C_NTRAIT(+)=FE_GL_CAFIS_UNION.C_NTRAIT AND FE_TAUX_TVA_CIPOSTE.D_CCOMPO1(+)=substr(FE_GL_CAFIS_UNION.D_CCOMPTE,7,2) )
AND ( FE_TAUX_TVA_CIPOSTE.C_LCTABREF='fe_TVA_compte' )
AND ( FE_TIERS_SIGNATAIRE.C_NTRAIT=FE_METIER.C_NTRAIT AND FE_TIERS_SIGNATAIRE.C_NMETIER_PAR=FE_METIER.D_NIDENT )
AND ( FE_GL_CAFIS_UNION.D_NCAFIS=FT_REPART_AP_ARR_CORR.D_NCAFIS AND FE_GL_CAFIS_UNION.C_NTRAIT=FT_REPART_AP_ARR_CORR.C_NTRAIT )
AND ( FE_METIER.C_LCTABREF='fe_metier' )
AND ( FE_GL_CAFIS_UNION.C_NTRAIT=FE_PARAM.D_NTRAIT )
AND
(
FE_METIER.D_CCOMPO1 IN ( 'LPGP' )
AND
TO_NUMBER(TO_CHAR(FE_PARAM.C_DTRAIT,'SYYYY')) IN @prompt('Année de ventilation :','N',,Multi,Free,Persistent,,User:0)
AND
TO_NUMBER(TO_CHAR(FE_PARAM.C_DTRAIT,'MM')) IN @prompt('Mois de ventilation :','N',,Multi,Free,Persistent,,User:1)
AND
( FE_PARAM.D_NTRAIT <> 2 )
AND
( FE_PARAM.D_CTYPETRAIT='CIPOSTE' )
)
GROUP BY
FT_REPART_AP_ARR_CORR.D_NDEBOURS,
FE_TIERS_SIGNATAIRE.D_CTIERS,
FE_TIERS_SIGNATAIRE.D_LTIERS,
FE_GL_CAFIS_UNION.D_CCOMPTE,
FE_NAT_CHARGES_CIPOSTE.D_CCOMPO3,
FE_NAT_CHARGES_CIPOSTE.D_LCOMPO,
TO_NUMBER(FE_TAUX_TVA_CIPOSTE.D_LCOMPO)/100,
FE_METIER.D_CCOMPO1,
FE_PARAM.D_DFACT_MIN,
FE_PARAM.D_DFACT_MAX,
FE_PARAM.C_DTRAIT;
 
ALTER SESSION SET events '10046 trace name context off'

**nuke_y** · 18/09/2009, 01h38

Bonjour.

Je peux te conseiller cette ébauche d'article sur le sujet. La partie III-P Benchmarks peut t'intéresser car en utilisant la méthode décrite j'arrive bien à faire fonctionner mes VM.

L'article en zip est dispo ici avec les scripts SQL (j'ai pas encore eu le temps de faire en sorte que les scripts SQL soient lisibles en ligne).

optimisation sql et vue matéralisée

SQL Oracle

Discussions similaires

Partager

Partager