[SQL] [Oracle 9.2] fonctions analytiques grosses tables

Bonjour,

dans un datawarehouse, j'ai classiquement une table facture et lignes de factures. La table facture fait environ 1Go, contre 10Go :( pour les lignes de factures. Je n'ai pas l'option de partitionning.

Lors de la construction de gros agrégats, j'ai besoin de :
- récupérer x axes temps (year to date, month to date, derniere semaine, les historiques équivalents)
- des informations référentielles (la classification des clients et la classification des articles, ...)
- sommer le CA, compter le nombre de clients, le nombre de lignes de livraison
- faire des sommes équivalentes, mais en ne prenant que les clients qui ont fait plus de 200€ au cours de la dernière période (glissantes).

La première approche conduit à une requête du type :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

select id_lap -- période de temps , id_sfc -- sous famille client , id_sfa -- sous famille article , avg(nb_lg_par_livraison) -- nombre de lignes par livraison , sum(ca) -- CA , count(distinct id_cln) -- nombre de clients , sum(case when id_cln_200 then ca) -- CA des clients ayant fait plus de 200€ sur le mois , count(distinct id_cln_200) from ( select facture.id_cln, facture.id_sfc, article.id_sfa, ligne_de_facture.ca -- id_per est une période temps (4 ou 5 semaines suivant les mois) , case when sum(ca) over (partition by facture.id_cln, calendrier.id_per) > 200 then id_cln end id_cln_200 from facture, ligne_de_facture, calendrier_periode, article, client where facture.id_fac = ligne_de_facture.id_fac and facture.dat_fac = calendrier_periode.id_jour and article.id_art = ligne_de_facture.id_art and client.id_cln = facture.id_cln ) , calendrier where fact.dat_fact between calendrier.id_sem and calendrier.id_sem + 6

---

Modéle de données

Citation:

---

calendrier_periode : id_jour (05/05/2006) et la période correspondante (01/05/2006)
facture : id_cln (id du client), id_fac (id de la facture), dat_fac (date)...
ligne_de_facture : id_fac, id_art (id de l'article), CA (prix de la ligne de facture)
article : id_art, id_sfa (sous famille article)
client : id_cln, id_sfc (sous famille client)
calendrier : id_lap (code de la période. IE YTD 2006, MTD 09/2006), id_sem (la liste de tous les lundis qui sont dans la période)

---

Coté optim, j'ai joué sur le paramétre non documentation _smm_max_size et _pga_max_size pour avoir jusque 500Mo de mémoire pour les tris :mouarf: (dans la PGA)
Je suis en train de faire un bench en stockant toutes les jointures dans une table intermédiaire qui est stocké sous la forme d'un IOT (clé = id_cln + dat_fac + id_art + id_fac)
Je construit bien sur une table intermédiaire pour filtrer les dates qui ne m'interressent pas (pas d'option partitionning), en utilisant la compression d'extent (4Go au final)

Est-ce que quelqu'un a déjà été confronté à ce genre de requête ? Quelles sont les méthodes d'optimisation qui ont été retenue / envisagée ?

L'agrégation que je dois construire est très large en terme d'historique, mais conduit à une table trés petite (30Mo) : j'ai en effet besoin d'avoir les périodes suivantes :
période en cours (1 mois), dernière période échue (1 mois), derniere semaine échue,
year to date (max 1 an), 12 mois glissants
les périodes de temps qui me permettent de comparer avec la valeur année précédente

par avance merci

bonjour, j'ai reformaté le message pour plus de clarté ;)

Est-ce que tu as regardé ce que peut proposer CUBE, ROLLUP et autres joyeusetés du genre ?

Je te déconseille fortement de toucher aux paramètres cachés, surtout pour un seul besoin très spécifique :?

Bonjour,

merci pour le reformatage du code et pour la réponse...

les fonctions cube et rollup auraient un intérêt pour limiter le nombre de période temps (id_lap) mais mes périodes d'analyse sont trop complexe pour que ce soit utilisable. :(

Pour ce qui est du paramétre non documenté, c'est un classique en datawarehouse :
Oracle a introduit le pga_aggregate_target sur la 9i, mais également une limitation sur l'allocation 8O : une session peut prendre au maximum 5% du PGA totale, avec un plafond à 64Mo. Ces seuils passent (de mémoire) à 30% et 100Mo quand on a des process paralléle. (ils ont oubliés la BI...)
==> je modifie donc pour utiliser des choses qui sont plus en phase avec la volumétrie de mes traitements et le nombre d'utilisateur simultanné.

(j'ai 1,5Go de PGA et rarement plus de 3 users actifs simultanné, pour une cinquantaine de connections)
==> repasser en sort/hash_area_size n'est donc pas envisageable et les 64 Mo bien trop petit (temps de traitement divisés par 2 et tris en multipass devenu trés rare dans v$sql_workarea :yaisse2:).

Cdlt

Hugues

si tu as une base dédiée DWH c'est nickel en effet :bravo:

8O

Elle est où ta jointure entre ta table calendrier et ton sous-select ?????

La ça sent le produit cartésien.... :aie:

8O 2ieme....

T'es sur que tu t'es pas planté de requête ??
Comment peux-tu mettre une des fonctions de regroupement et des colonnes simples sans meme mettre de "GROUP BY" ?

Je fais une réponse globale...

effectivement, c'est une base datawarehouse... A moins de n'avoir que 3 tables et 5 utilisateurs, le mélange des genres n'est jamais trés heureux...

J'ai un triste souvenir d'un client avec 600 users OLTP ( mon appli ) qui avait eu la joyeuse idée de mettre un BO la dessus sans me le dire... Si ca n'avait pas été un client, je l'aurais... (la descence et l'éventuel présence d'enfants ne me permet pas d'ettayer plus mes propos)

Concernant la jointure, elle est présente... La semaine 35 de 2006 va se retrouver éventuellement en semaine précédante, mais peut etre dans le mois qui vient de s'écouler et très certainement en year to date... C'est tout l'intéret des datamarts dans un datawarehouse.... Faire des agrégats sur des calculs complexes qui nécessiteront forcement des full table scan et de la redondance....

Sinon, effectivement, il manque le group by dans le select de haut niveau. Ce n'est qu'un extrait de code (la requête fait 700 ou 800 lignes, avec des with, etc...). Mais mon soucis se situe plus au niveau du select imbriqué, avec les fonctions analytiques...
A part les IOT qui me permettente de "précalculer" les tris les plus fréquents, j'ai pas trop de pistes...
La global temporary table en est peut être une, mais bonjour les reprises sur incidentes...

En gros, je cherche soit un moyen d'optimiser les direct path read/write dans les grosses requêtes, ou (miracle) quelqu'un qui est déjà tombé sur un cas similaire au mien et a pris le temps de faire les différents bench
(effectivement, un client qui fait 50E par mois n'est pas un vrai client... mais c'est un casse tête pour le modèle de données dès que l'on a de la volumétrie)

A mon avis tu ne cherches pas dans la bonne direction. Tu te focalises sur le tri alors que la fonction analytique permet justement de considérablement limiter son coût. As-tu fais une trace et/ou regarder v$session_wait pendant l'exécution de la requête.

PS : j'ai pas trouvé le sujet dont tu m'as parlé :(

Effectivement, je vais tenter d'envisager une autre approche...

si je me focalise sur le tri, c'est que ce sont les étapes les plus lourdes dans mon traitement
==> dans v$session_wait, je trouve principalement des direct path read / write

parfois la requête plante par manque de place dans le temp (j'ai plus de 40Go à ma disposition).

Un petit truc qui m'interpelle : mon IOT a comme primary key :
id_cln, dat_fac, trn_fac, id_fac, id_lgn
(client, date de facture, code tournée de livraison, id de la facture, id de la ligne de facture)

les 2 dernières colonnes sont la pour avoir une vraie clé unique. Les 3 premières étaient la dans l'espoir de précalculer mon tri

si je fais

Code:

---

1 2 3

select * from CUBE_NOMADE_MENSUEL$648175_1 base order by id_cln, dat_fac, trn_fac, id_fac, id_lgn

---

j'ai un plan d'exécution avec un "sort order by" et il fait bien le full scan (index fast full scan) à l'exécution... Comme si l'optimiseur ne prenait pas en compte que c'est une table organisée en index.

Ma grosse requête fait des fonctions analytiques sur les partitions (id_cln, dat_fac, trn_fac) et sur (id_cln, id_per)... Donc il y a 2 windows sort qui sont fait, d'ou le temps important (c'est long de trié x Go).

Pour donner une vision globale, voici ma requête (c'est un with qui sera utilisée dans une dizaine de requête... Il est suivi d'un UNION ALL pour alimenter le champs mt_ric (constante 0 ici) mais qui ne dure que 3-4 mn)

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 67 68 69 70 71 72 73 74 75 76 77 78 79

SELECT id_lap ,id_cln ,id_cln_sup200 ,id_sfa ,id_prp ,NVL(SUM ( mt_ric ),0) mt_ric ,NVL(SUM ( mt_cab ),0) mt_cab ,NVL(SUM ( mt_mep ),0) mt_mep ,NVL(SUM ( mt_mvb ),0) mt_mvb ,NVL(SUM ( qt_kgl ),0) qt_kgl ,NVL(SUM ( nb_fac ),0) nb_fac ,NVL(SUM ( nb_lig_fac ),0) nb_lig_fac ,NVL(SUM ( nb_liv ),0) nb_liv ,NVL(SUM ( nb_liv_sup10lg ),0) nb_liv_sup10lg ,NVL(SUM ( nb_liv_inf100e ),0) nb_liv_inf100e ,NVL(SUM ( nb_avo ),0) nb_avo ,NVL(SUM ( nb_cln_new ),0) nb_cln_new ,NVL(SUM ( nb_cln_vivier ),0) nb_cln_vivier FROM (SELECT id_lap ,id_cln ,id_cln_sup200 ,id_sfa ,id_prp ,0 mt_ric ,NVL(SUM( can_fac ),0) mt_cab ,NVL(SUM( css_fac ),0) - NVL(SUM( pmp_fac ),0) mt_mep ,NVL(SUM( can_fac ),0) - NVL(SUM( css_fac ),0) mt_mvb ,NVL(SUM( qtk_fac ),0) qt_kgl ,COUNT(DISTINCT id_fac) nb_fac ,COUNT(DISTINCT id_fac || id_lgn) nb_lig_fac ,COUNT(DISTINCT dat_fac || trn_fac || id_cln) nb_liv ,COUNT(DISTINCT CASE WHEN nb_lg_livraison > 10 THEN dat_fac || trn_fac || id_cln END) nb_liv_sup10lg ,COUNT(DISTINCT CASE WHEN ca_par_livraison < 100 THEN dat_fac || trn_fac || id_cln END) nb_liv_inf100e ,COUNT(DISTINCT CASE WHEN typ_fac IN ('ZG2','ZGF','ZGR2') THEN id_fac END) nb_avo ,COUNT(DISTINCT lf.id_cln_new) nb_cln_new ,COUNT(DISTINCT lf.id_cln_vivier) nb_cln_vivier FROM ( SELECT cal.id_lap ,base.id_fac ,base.id_cln ,CASE WHEN SUM(base.can_fac) OVER (PARTITION BY base.id_cln, cal.id_lap, base.id_per) > 200 THEN base.id_cln END id_cln_sup200 ,base.dat_fac ,base.trn_fac ,base.typ_fac ,base.id_sfa ,TO_CHAR(NVL(base.id_prp,0)) id_prp ,base.id_lgn ,base.can_fac ,base.css_fac ,base.pmp_fac ,base.qtk_fac ,COUNT(DISTINCT base.id_lgn) OVER (PARTITION BY base.id_cln, base.dat_fac, base.trn_fac) nb_lg_livraison ,SUM(base.can_fac) OVER (PARTITION BY base.id_cln, base.dat_fac, base.trn_fac) ca_par_livraison ,CASE WHEN base.can_fac > 0 AND base.dcr_cln BETWEEN cal.id_sem AND cal.id_sem + 6 THEN base.id_cln END id_cln_new ,-- Regle de calcul a définir CASE WHEN base.can_fac > 0 AND base.dcr_cln BETWEEN cal.id_sem AND cal.id_sem + 6 THEN base.id_cln END id_cln_vivier FROM CUBE_NOMADE_MENSUEL$648175_1 base ,CALENDRIER_445$648175 cal WHERE cal.id_sem = base.id_sem ) lf GROUP BY id_lap ,id_cln ,id_cln_sup200 ,id_sfa ,id_prp

---

De toutes façons les "COUNT(DISTINCT ...)" et du "GROUP BY" sur une sous-requête tu aura forcément de la consomation de TEMP et du tri...

Ce qui me chiffonne dans la requête c'est les 3 niveaux avec à chaque fois du group_by etc..., est-ce que tu es sur qu'il n'y a pas moyen d'en économiser au moins 1 ?

Ou peut etre que la solution la plus éfficace (si tu as la place) serait de faire transiter les données dans des tables sans index, puis creer des indexs avant de les faire transiter dans une d'autres tables afin de controler à la main tes 3 niveaux....

Ca aura l'avantage aussi de fractionner les traitement et donc de cibler le (ou les) problème(s)

moi le DISTINCT dans tous les sens ça me fait un peu mal au coeur :?

Avec un DENSE_RANK il doit bien y avoir moyen de s'en sortir non ?

La table qui se trouve au niveau le plus fin stocke des informations au niveau ligne de facture + les données de l'entete de facture. C'est une table précalculé plus haut qui me permet de jouer avec des volumétries raisonables tout en n'ayant pas l'option partitionning.

Au niveau 1 (le plus fin de la requête - select le plus imbriqué) :
je récupére les infos dont j'ai besoin.
j'ajoute quelques fonctions analytiques :
- savoir si le client a fait plus de 200€ de CA sur une période de facturation - à peu de chose pres 1 mois)
- obtenir des informations au niveau de la livraison de la facture : le nombre de ligne livrée et le CA associé

Au niveau 2 :
je fais mes calculs principaux toujours au niveau du client, mais sur mes hiérarchie temps
les basiques sont CA, marge, quantité
En complexe, j'ai le nombre de factures, le nombre de lignes de factures, le nombre de livraison, le nombre de livraison de plus de 10 lignes, le nombre de livraison qui font moins de 100€, le nombre d'avoirs...

Au niveau 3 : (niveau macro)
il y a un troisieme group by : j'indiquais qu'il y avait un autre cube qui me permet de récupérer des informations complémentaire (les marges arrières), qui sont en fait un UNION ALL sur les mêmes colonnes. Je ne peux pas faire de jointure au niveau 1 car les axes temps sont différents (à la journée et à la semaine).

Remi4444 : mon problème se situe sur la requête de niveau 1 qui prend des plombes à s'exécuter. Pour le reste, je travaille avec quelques dizaine de Go, donc ça va trés vite...

Fred_D : je suis intérressé par tes propositions de dense_rank. Si tu as des exemples, je suis preneur.
Le principe, c'est quoi ? tu fait un dense_rank over (partition by ...) et après tu fait un max dessus ?

Par contre, je ne suis pas sur que ce soit applicable à cette requête en particulier :
je calcul
- le nombre de livraison => distinct dat_fac || trn_fac || id_cln
- le nombre de ligne de livraisons => id_fac || id_lgn (+ les mêmes avec des filtres)
- le nombre de facture => id_fac
- le nombre d'avoir => id_fac avec un filtre sur typ_fac

Est-ce qu'oracle ne va pas me générer un plan avec un windows sort buffer pour chaque fonction analytique qui se trouve sur un over (partition by ...) différent ?

merci de ton aide