optimisation sql et vue matéralisée

Version imprimable

08/09/2009, 01h07
bertini15

optimisation sql et vue matéralisée
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:

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:

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.
08/09/2009, 08h59
mnitu

Il faut plus des détails sur ma_requête, etc. pour diagnostiquer votre problème.
08/09/2009, 09h19
pachot

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.
08/09/2009, 10h03
pacmann

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.

Merci pour vos réponses.

ci-dessous ma requête.

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
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.

10/09/2009, 09h18
bertini15

Bonjour,

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

Aidez moi SVP.

Merci
10/09/2009, 11h04
GoLDoZ

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.
10/09/2009, 11h08
farenheiit

combien de temps met la requête suivante:

Code:

select * from ma_MV;
10/09/2009, 13h21
mnitu

Citation:

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é.
10/09/2009, 17h06
bertini15

Pour farenheiit, la requête

Code:

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.

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

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
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'

18/09/2009, 01h38
nuke_y

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).