[11gR2] Problème de performance sur un CUBE

Version imprimable

06/06/2011, 10h00
punkoff

[11gR2] Problème de performance sur un CUBE

Bonjour,

La version de ma base est :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Alors mon schéma où j’exécute ma requête est assez petit (10Mo).

Pour l'instant je vous épargne le statement sql qui ne rimerait à rien sans avoir les données.

Mon problème est le suivant :
J'ai créé une requête (un cube en l’occurrence) qui marche assez bien dans 95% des cas.
Temps de réponse inférieur à 500ms.

La requête s’exécute sur une application via le driver jdbc fournit par oracle.

Mais de temps en temps, avec les même variables bindées ma requête s'emballe et utilise jusqu'à 5Go d'espace temporaire (dans le tablespace TEMP), et du coup les temps de réponse deviennent catastrophique. (sans compter l'espace temporaire utilisée qui est une aberration)

J'aimerai découvrir pourquoi une telle chose survient et surtout par quel moyen puis-je analyser ça.

Tout type de piste me serait utile.

Merci !

Question subsidiaire, y a-t-il un moyen de visualiser des traces sql via l'interface de Enterprise management ? Ou suis-je obligé d'utiliser tkproof ?
06/06/2011, 10h19
Waldar

Qu'est-ce que vous appelez un cube au juste, un vrai cube OLAP créé avec Analytic Workspace Manager ou bien un modèle en étoile / flocon basé sur un modèle R-OLAP ?

Effectivement pour 10 Mo de données, 5 Go d'espace temporaire paraît surréaliste.

La piste des traces me paraît la meilleure solution.
Je ne sais pas si OEM permet la lecture de ces dernières, par contre vous n'êtes pas cantonné à tkprof, il y a aussi TVD$XTAT qui produit un fichier html plus "XXIème siècle", même si in fine vous y retrouverez les mêmes informations qu'avec tkprof à peu de chose près.
06/06/2011, 10h42
punkoff

Bonjour,

Non ca n'est pas un vrai cube OLAP.

C'est une base "standard" relationnelle où on utilise une fonction OLAP (CUBE BY en l'occurance) pour réaliser des statistiques.

C'est un modèle en ..étoile si je ne m'abuse :
J'ai une table centrale composée de 4 foreign key + une colonne numérique sur laquelle je fais le cube by.

Cette table fait 2 Mo et à 24k d'enregistrement...
J'ai indexé toutes les fk de ma base et le plan que me propose Oracle ne me semble pas "mauvais" (bien que mon interprétation puisse être mauvaise.. concernant Oracle! )

Est-ce que cela répond à votre question ?

Je vais essayer de chopper ces traces et reviendrai avec.

Merci
06/06/2011, 10h56
Waldar
Ok, pour les modèles en étoile, les recommandations Oracle sont, dans les grandes lignes :
1. Utiliser des index bitmap sur toutes les FK de la table de faits
2. Vérifier que le paramètre STAR_TRANSFORMATION_ENABLED est TRUE
D'ailleurs en fait ce sont les seules.
La documentation Oracle décrit bien la problématique (tout le chapitre sur le datawarehousing est plutôt bon) :
http://download.oracle.com/docs/cd/B...s.htm#i1006335

Bon, il y a un cas de figure qui pourrait s'appliquer à votre table :

Citation:

The star transformation may not be chosen by the optimizer for the following cases :
[...]
Tables that are too small for the transformation to be worthwhile

Ah oui, en 11.1.0.7, on a rencontré beaucoup d'ORA-00600 sur un datawarehouse, et un des conseils du support Oracle a été de remettre le paramètre STAR_TRANSFORMATION_ENABLED à false...
Et ça a résolu notre problème (sic). J'espère que ça fonctionne mieux en 11gR2 !
06/06/2011, 11h34
punkoff

Merci Waldar, ce liens est intéressant mais il faut que je prenne le temps de le tester :)

Sinon mon plan d’exécution n'est peut-être pas si bon que ça, car j'ai des MERGE JOIN CARTESIAN qui trainent ... je vais miner dans ce sens là.
06/06/2011, 13h46
punkoff

2 pièce(s) jointe(s)

Bon, j'avance un peu mais ce que je découvre me laisse perplexe.

J'ai fait 2 traces (ci-jointe) une avec la requête qui part en live et la 2eme où c'est ok.

Le plan d’exécution est différent.

Ce qui change entre les deux c'est une variable.

Donc pour expliquer un peu plus en profondeur ce qui est réalisé.

J'ai une table de vente (T_SALE_SAL) où sont stockés des chiffres d'affaire selon certain critère.

Ces critères sont en vrac : un trimestre, une activité, un account, un type de vente.

C'est sur cette table là que le cube se fait (entre autres).

En amont je dois sélectionner une liste d'account selon les droits de l'utilisateur afin de diminuer le nombre de lignes sélectionnables pour le cube.

Dans la requête c'est la partie With .... jusqu'a V_TOT qui représente une liste d'account.

Je joins ensuite cette vue V_TOT avec la table T_SALE_SAL afin de diminuer la sélectivité.

Dans le cas joint, seul l'id de l'utilisateur a changé. Mais en sortie de V_TOT j'ai la même liste d'account (pour les 2 users bien qu'ils aient des droits un peu différents).

Et ceci fait changer le plan d’exécution !
Aurais-je oublié de faire quelque chose au niveau des stats .. index .. autres ?

Je n'ai pas encore tester votre solution Waldar, afin d'activer le star join, mais j'aimerais comprendre pourquoi l'optimiseur part en live à cet endroit :)
06/06/2011, 14h52
mnitu

En analysant les deux plans on peut constater des écarts assez important entre la cardinalité estimé et celle réelle. Vous devez investiguer pourquoi cela arrive. Vérifiez les statistiques pour T_CLUSTSEG_CLS et autres tables.
Essayez d’augmenter optimizer dynamique sampling (au moins la valeur 5) pour voir si cela aide.
06/06/2011, 16h13
punkoff
Bonjour,

Mes stats ont été collectés ce we.

Mais par acquis de conscience je les ai relancées (j'espère que c'est comme ceci qu'il faut procéder ..)
Code:

1 2 exec DBMS_STATS.GATHER_SCHEMA_STATS('USRINT0002', cascade => true);
J'ai aussi changé le paramètre que vous indiquiez :
Code:

1 2 alter system set optimizer_dynamic_sampling = 5 scope=both;
J'ai de nouveau lancé le test avec ces 2 même requêtes.

La requête qui marchait mal n'a pas changé de plan d'exécution et a le même problème.

La requête qui marchait bien, a changé de plan et c'est exécutée un peu plus rapidement.

je peux vous fournir les nouvelles traces si vous le souhaitez.

Dans la première vue, ça donne quoi si vous utilisez une variable de liaison pour le usr_id (ou alors j'ai mal compris et c'est déjà une variable de liaison) ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH V_CLE as
(
SELECT usr.id as usr_id, cle.clu_id, cle.cou_id, cle.gsa_id, cle.apc_id, rty.rty_code
  FROM T_ROLE_TYPE_RTY rty
       INNER JOIN T_ROLE_ROL rol
         ON rol.rty_id = rty.id
       INNER JOIN T_CLEARANCE_CLE cle
         ON cle.rol_id = rol.id
       INNER JOIN T_USER_USR usr
         ON usr.id = cle.usr_id
 WHERE usr_id = :1
)
...

Par contre, quelle requête !
Ça me paraît vraiment complexe pour un modèle en étoile : l'avantage de ces derniers étant leur facilité d'interrogation !

On peut simplifier l'écriture à quelques endroit, mais ça ne changera pas fondamentalement la requête :

Code:

1
2
3
4
5
6
7
8
9
10
  , V_TOT as
(
SELECT distinct apc_id
  FROM ( select apc_id from LVL_NONE non    union all
         select apc_id from LVL_CLUSTER clu union all
         select apc_id from LVL_COUNTRY cou union all
         select apc_id from LVL_GSA gsa     union all
         select apc_id from LVL_APC apc     union all
         select apc_id from LVL_CLC clc ) tmp
)

Devient :

Code:

1
2
3
4
5
6
7
8
9
  , V_TOT as
(
select apc_id from LVL_NONE    union
select apc_id from LVL_CLUSTER union
select apc_id from LVL_COUNTRY union
select apc_id from LVL_GSA     union
select apc_id from LVL_APC     union
select apc_id from LVL_CLC
)

Ici avec le constructeur de ligne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
   WHERE ( yea_year = 2011
     AND (  (grp_cou = 1 and grp_qua = 1 and grp_cls = 1 and grp_gsa = 1)
         OR (grp_cou = 1 and grp_qua = 0 and grp_cls = 1 and grp_gsa = 1)
         OR (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 1)
         OR (grp_cou = 1 and grp_qua = 0 and grp_cls = 0 and grp_gsa = 1)
         OR (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0)
         OR (grp_cou = 1 and grp_qua = 0 and grp_cls = 0 and grp_gsa = 0)
         OR (grp_cou = 0 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0)
         OR (grp_cou = 0 and grp_qua = 0 and grp_cls = 0 and grp_gsa = 0)) )
      OR ( yea_year BETWEEN (2011 -2) AND (2011 -1)
     AND (  (grp_cou = 1 and grp_qua = 1 and grp_cls = 1 and grp_gsa = 1)
         OR (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 1)
         OR (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0)
         OR (grp_cou = 0 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0)) )

Devient :

Code:

1
2
3
4
   WHERE (yea_year BETWEEN (2011-2) AND 2011
     AND (grp_cou, grp_qua, grp_cls, grp_gsa) in ((1,1,1,1), (1,1,0,1), (1,1,0,0), (0,1,0,0)))
      OR (yea_year = 2011
     AND (grp_cou, grp_qua, grp_cls, grp_gsa) in ((1,0,1,1), (1,0,0,1), (1,0,0,0), (0,0,0,0)))

Cette jointure :

Code:

1
2
3
4
5
6
7
8
  ,  LVL_NONE as
(
SELECT apc.id as apc_id
  FROM V_CLE cle
       INNER JOIN T_GSA_COU_APC apc
         on 1 = 1
 WHERE rty_code = 'NON'
)

En produit cartésien :

Code:

1
2
3
4
5
6
7
  ,  LVL_NONE as
(
SELECT apc.id as apc_id
  FROM V_CLE cle
       CROSS JOIN T_GSA_COU_APC apc
 WHERE rty_code = 'NON'
)

06/06/2011, 17h09
punkoff

Citation:

Envoyé par Waldar

Dans la première vue, ça donne quoi si vous utilisez une variable de liaison pour le usr_id (ou alors j'ai mal compris et c'est déjà une variable de liaison) ?

Alors oui, dans l'application le usr_id est une variable liée.
Mais vous avez raison, pour reproduire le cas je ne l'ai pas lié.

Ceci dit de cette manière j'arrive à reproduire le problème (plusieurs giga d'espace temporaire utilisée pour servir la requête).

Je vais refaire des traces de la bonne manière pour voir s'il y a des changements.

Citation:

Par contre, quelle requête !
Ça me paraît vraiment complexe pour un modèle en étoile : l'avantage de ces derniers étant leur facilité d'interrogation !

Disons que le coeur de métier est modélisé "correctement", mais la gestion des droits étant assez complexe j'ai peut être loupée cette partie.

Je suis obligé de faire ceci car un utilisateur peut ne pas avoir le droit de voir tous les chiffres d'affaire de tous les accounts.
Il peut être associé à des profils de plusieurs niveau et chaque niveau peut englober un ou plusieurs accounts.

D'où le bordel pour arriver à V_TOT.
Ceci dit la base est petite et le nombre de ligne sortant de V_TOT ne dépasse pas (et ne dépassera pas) un millier de ligne.
Ceci va influer sur la sélectivité de la table T_SALE_SAL qui elle pourra atteindre quelques centaines de milliers de lignes :
donc si on a le maximum de ligne en sortie de V_TOT on fait un cube sur toute la table T_SALE_SAL, ou presque, et dès que l'on à moins de ligne en sortie de V_TOT le nombre de ligne de T_SALE_SAL pour le cube diminue drastiquement)

Citation:

On peut simplifier l'écriture à quelques endroit, mais ça ne changera pas fondamentalement la requête ...

Je prends note et adapte.

D'abord voilà votre 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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
WITH V_CLE as (
  SELECT usr.id as usr_id, 
	 cle.clu_id, 
	 cle.cou_id, 
	 cle.gsa_id, 
	 cle.apc_id, 
	 rty.rty_code
    FROM T_ROLE_TYPE_RTY rty 
	 INNER JOIN 
         T_ROLE_ROL rol 
      ON rol.rty_id = rty.id 
	 INNER JOIN 
         T_CLEARANCE_CLE cle 
      ON cle.rol_id = rol.id 
         INNER JOIN 
         T_USER_USR usr 
      ON usr.id = cle.usr_id
   WHERE usr_id = 4 
), LVL_NONE as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
	 INNER JOIN 
         T_GSA_COU_APC apc 
      on 1=1
   WHERE rty_code = 'NON' 
), LVL_CLUSTER as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
	 INNER JOIN 
         T_CLUSTER_CLU clu 
      ON clu.id = CLE.CLU_ID AND clu.deleted = 0 
         INNER JOIN 
         T_CLUSTSEG_CLS cls 
      ON clu.id = cls.clu_id AND cls.deleted = 0 
	 INNER JOIN 
         T_GSA_GSA gsa 
      ON gsa.cls_id = cls.id AND gsa.deleted = 0 
         INNER JOIN 
         T_GSA_COU_APC apc 
      ON APC.GSA_ID = gsa.id AND apc.deleted = 0
   WHERE rty_code = 'CLU' 
), LVL_COUNTRY as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
         INNER JOIN 
         T_COUNTRY_COU cou 
      on cou.id = cle.cou_id AND cou.deleted = 0 
         INNER JOIN 
         T_GSA_COU_APC apc 
      on APC.COU_ID = cou.id AND apc.deleted = 0
   WHERE rty_code = 'COU' 
), LVL_GSA as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
         INNER JOIN 
         T_GSA_GSA gsa 
      ON cle.gsa_id = gsa.id AND gsa.deleted = 0 
         INNER JOIN 
         T_GSA_COU_APC apc 
      ON apc.gsa_id = gsa.id AND apc.deleted = 0
   WHERE rty_code = 'ACC' 
), LVL_APC as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
	 INNER JOIN 
	 T_GSA_COU_APC apc 
      ON cle.apc_id = apc.id AND apc.deleted = 0
   WHERE rty_code = 'APC' 
), LVL_CLC as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
	 INNER JOIN 
	 T_CLUSTER_CLU clu 
      ON clu.id = CLE.CLU_ID AND clu.deleted = 0 
	 INNER JOIN 
         T_CLUSTSEG_CLS cls 
      ON clu.id = cls.clu_id AND cls.deleted = 0 
	 INNER JOIN 
         T_GSA_GSA gsa 
      ON gsa.cls_id = cls.id AND gsa.deleted = 0 
	 INNER JOIN 
         T_COUNTRY_COU cou 
      ON cle.cou_id = cou.id AND cou.deleted = 0 
	 INNER JOIN 
         T_GSA_COU_APC apc 
      ON APC.GSA_ID = gsa.id AND apc.cou_id = cou.id and apc.deleted = 0
    WHERE rty_code = 'CLC' 
), V_TOT as (
  SELECT distinct apc_id
    FROM (
           select apc_id
             from LVL_NONE non 
           union all
           select apc_id
             from LVL_CLUSTER clu 
           union all
           select apc_id
             from LVL_COUNTRY cou 
           union all
           select apc_id
             from LVL_GSA gsa 
           union all
	   select apc_id
             from LVL_APC apc 
	   union all
           select apc_id
            from LVL_CLC clc 
        ) tmp 
), tmp AS (
  SELECT yea.yea_year, 
	 cou.id as cou_id, 
	 cou.cou_name, 
	 cls.id as cls_id, 
	 cls.cls_name, 
	 gsa.id as gsa_id, 
	 gsa.gsa_name, 
	 qua.id as qua_id, 
	 qua.qua_quarter, 
	 cast(round(sum(sal.SAL_AMO_EUR) / 1000000, 3) as number(10, 3)) as amount, 
	 grouping_id(qua.qua_quarter) as grp_qua, 
	 grouping_id (cou.cou_name) as grp_cou, 
	 grouping_id (cls.cls_name) as grp_cls, 
	 grouping_id (gsa.gsa_name) as grp_gsa
    FROM t_sale_sal sal 
	 INNER JOIN 
         v_tot tot 
      on tot.apc_id = sal.apc_id 
	 INNER JOIN 
         t_gsa_cou_apc apc 
      on apc.id = sal.apc_id and apc.deleted = 0 and apc.apc_enabled = 1 
	 INNER JOIN 
         t_country_cou cou 
      on cou.id = apc.cou_id and cou.deleted = 0 
	 INNER JOIN 
         t_gsa_gsa gsa 
      on gsa.id = apc.gsa_id and gsa.deleted = 0 
	 INNER JOIN 
         t_gsa_status_sta sta 
      on sta.id = gsa.sta_id and sta_code = 'OPR' 
	 INNER JOIN 
         t_clustseg_cls cls 
      on cls.id = gsa.cls_id and cls.deleted = 0 
	 INNER JOIN 
         t_sale_type_sat sat 
      on sal.sat_id = sat.id and sat.sat_code = 'NON' 
	 INNER JOIN 
         t_activity_act act 
      on sal.act_id = act.id and act.deleted= 0 and act.act_enabled = 1 
	 INNER JOIN 
         t_act_type_tac tac 
      on tac.id = act.tac_id and tac_code = 'ACT' 
	 INNER JOIN 
         t_quarter_qua qua 
      on qua.id = sal.qua_id 
	 INNER JOIN 
         t_year_yea yea 
      on yea.id = qua.yea_id
   WHERE (yea.yea_year BETWEEN (2011 -2) AND (2011 -1) 
          AND trim(qua.qua_quarter) IN ('Q1', 'Q2', 'Q3', 'Q4') 
         ) 
      OR ( yea.yea_year = 2011 AND trim(qua.qua_quarter) IN ('Q1', 'Q2', 'Q3', 'Q4') ) 
      OR ( yea.yea_year = 2011 AND qua.qua_quarter = 'Q3' ) 
      OR ( yea.yea_year = (2011 +1) AND qua_quarter = 'FQ1' ) 
  GROUP BY yea.yea_year, CUBE ((qua.id, qua_quarter), (cls.id, cls.cls_name), (gsa.id, gsa.gsa_name), (cou.id, cou.cou_name)) 
)
SELECT tmp.*, 
       TRIM(typ.typ_code) as typ_code, 
       apc.id as apc_id
  FROM tmp 
       LEFT JOIN 
       t_gsa_gsa gsa 
    on gsa.id = tmp.gsa_id and gsa.deleted = 0 
       LEFT JOIN 
       t_gsa_type_typ typ 
    on typ.id = gsa.typ_id 
       LEFT JOIN 
       t_gsa_cou_apc apc 
    on apc.gsa_id = tmp.gsa_id 
       and apc.cou_id = tmp.cou_id 
       and apc.deleted = 0 
       and apc.apc_enabled = 1
 WHERE ( yea_year = 2011 
         AND ((grp_cou = 1 and grp_qua = 1 and grp_cls = 1 and grp_gsa = 1) OR 
              (grp_cou = 1 and grp_qua = 0 and grp_cls = 1 and grp_gsa = 1) OR 
              (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 1) OR 
              (grp_cou = 1 and grp_qua = 0 and grp_cls = 0 and grp_gsa = 1) OR 
              (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0) OR 
              (grp_cou = 1 and grp_qua = 0 and grp_cls = 0 and grp_gsa = 0) OR 
              (grp_cou = 0 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0) OR 
              (grp_cou = 0 and grp_qua = 0 and grp_cls = 0 and grp_gsa = 0)) 
       ) 
    OR ( yea_year BETWEEN (2011 -2) AND (2011 -1) 
         AND ((grp_cou = 1 and grp_qua = 1 and grp_cls = 1 and grp_gsa = 1) OR 
              (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 1) OR 
              (grp_cou = 1 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0) OR 
              (grp_cou = 0 and grp_qua = 1 and grp_cls = 0 and grp_gsa = 0)) 
       ) 
 ORDER BY tmp.cls_name NULLS FIRST, tmp.cou_name NULLS FIRST, tmp.gsa_name NULLS FIRST, yea_year

Comme ça il est plus simple d'analyser.

La partie

Code:

1
2
3
4
5
6
7
8
9
10
11
 
...
), LVL_NONE as (
  SELECT apc.id as apc_id
    FROM V_CLE cle 
	 INNER JOIN 
         T_GSA_COU_APC apc 
      on 1=1
   WHERE rty_code = 'NON' 
)
...

est un fait un produit cartésien.

07/06/2011, 07h50
punkoff

oui, et c'est voulu.

Dans l'idée, les sous-requêtes qui permettent de construire la requête V_TOT vont lister des account (apc_id) selon les droits de l'utilisateur.

En l’occurrence, LVL_NONE est un niveau de droit administrateur, donc l'utilisateur n'aura pas de restriction.
De ce fait il doit pouvoir accéder à tous les chiffres de tous les accounts.

Il y a peut etre une autre solution pour arriver à mes fins.

La table T_CLEARANCE_CLE permet de relier un utilisateur à n profils.

Chaque profils à un "niveau" :
- NON : aucune restriction (il peut tout visionner)
- CLU : Niveau cluster, ceci englobe plusieurs global accounts (et donc plusieurs accounts apc_id)
- ACC: niveau global account, ceci englobe plusieurs accounts
- APC : niveau account, ceci englobe 1 seul account

Schématiquement voici le mcd :
Partie globale account (T_GSA_GSA) :
T_GSA_GSA 1,1 ---- 0,n T_CLUSTSEG_CLS 1,1 --- 0,n T_CLUSTER_CLU

Partie account :
1 account (T_GSA_COU_APC) est une relation entre la table global account (T_GSA_GSA) et la table des pays (T_COUNTRY_COU). Cette relation se compose donc de 2 FK sur chacune de ces tables.

Partie Sale :
Comme je l'ai stipulé plus haut, une sale (T_SALE_SAL) concerne 1 seul account (T_GSA_COU_APC).

Donc ! un utilisateur pouvant avoir plusieurs profiles rattachés, il aura des droits de visions sur différent accounts (T_GSA_COU_APC) et ceci va directement influer sur le résultat du cube.

Concernant les 2 requêtes testées :
- l'utilisateur pour lequel la requete a un problème n'a qu'un seul profile de niveau administrateur (NON)
- l'utilisateur pour lequel la requête fonctionne a un profile de niveau administrateur (NON) et un profile de niveau account (APC)
07/06/2011, 10h07
Waldar

Est-ce simplement la dernière étape de la requête qui est lente pour le second profil, ou bien est-ce une des étapes intermédiaires ?
07/06/2011, 10h39
punkoff

Y a-t-il des options dans tkprof pour avoir des plans plus détaillé ? avec les temps en particulier ?

D'après ce que je comprend des plans sorties, la partie V_TOT ce fait bien (1116 apc_id distinct) et c'est après que le problème se pose.

En particulier au moment où il crée une table temporaire (mais je ne sais pas à partir de quoi, ca n'est pas explicite dans le plan) pour soit préparer le cube soit joindre sur V_TOT.

Je regarderai plus en profondeur à midi, et je n'ai pas encore pu essayer avec les modifs que vous avez apporté.

Il suffit d'exécuter la requête par bout :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 WITH V_CLE as ( SELECT usr.id as usr_id, cle.clu_id, cle.cou_id, cle.gsa_id, cle.apc_id, rty.rty_code FROM T_ROLE_TYPE_RTY rty INNER JOIN T_ROLE_ROL rol ON rol.rty_id = rty.id INNER JOIN T_CLEARANCE_CLE cle ON cle.rol_id = rol.id INNER JOIN T_USER_USR usr ON usr.id = cle.usr_id WHERE usr_id = 4 ) SELECT * from V_CLE
On rajoute le bloc suivant, on reexécute :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 WITH V_CLE as ( SELECT usr.id as usr_id, cle.clu_id, cle.cou_id, cle.gsa_id, cle.apc_id, rty.rty_code FROM T_ROLE_TYPE_RTY rty INNER JOIN T_ROLE_ROL rol ON rol.rty_id = rty.id INNER JOIN T_CLEARANCE_CLE cle ON cle.rol_id = rol.id INNER JOIN T_USER_USR usr ON usr.id = cle.usr_id WHERE usr_id = 4 ) , LVL_NONE as ( SELECT apc.id as apc_id FROM V_CLE cle INNER JOIN T_GSA_COU_APC apc on 1 = 1 WHERE rty_code = 'NON' ) SELECT * FROM LVL_NONE
Et cetera !

Car ce qui est sûr, c'est que lors de la construction d'une de ces vues, beaucoup de lignes sont chargées dans le mauvais plan :

Citation:

103493376 DIRECT LOAD INTO OF 'SYS_TEMP_0FD9D662E_48071A'

Si je lis bien le plan, c'est lors de la création de la vue "tmp", donc il faudrait voir si à ce moment-là il n'y a pas trop d'éléments, chose qu'on peut vérifier en exécutant la requête bout par bout pour vérifier que tout est conforme à ce que vous attendez.

J’essayerai de réécrire la première partie ainsi

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
 
WITH V_CLE AS (
  SELECT usr.id AS usr_id, 
         cle.clu_id, 
         cle.cou_id, 
         cle.gsa_id, 
         cle.apc_id, 
         rty.rty_code
    FROM T_ROLE_TYPE_RTY rty 
         INNER JOIN 
         T_ROLE_ROL rol 
      ON rol.rty_id = rty.id 
         INNER JOIN 
         T_CLEARANCE_CLE cle 
      ON cle.rol_id = rol.id 
         INNER JOIN 
         T_USER_USR usr 
      ON usr.id = cle.usr_id
   WHERE usr_id = 4 
) V_TOT As (
  SELECT apc.id AS apc_id
    From T_GSA_COU_APC apc 
   Where apc.deleted = 0      
     And Exists (Select Null
                   From V_Cle
                  Where rty_code = 'NON'
                )
      Or Exists (Select Null
                   From V_CLE
                        INNER JOIN 
                        T_CLUSTER_CLU clu 
                     ON clu.id = CLE.CLU_ID AND clu.deleted = 0 
                        INNER JOIN 
                        T_CLUSTSEG_CLS cls 
                     ON clu.id = cls.clu_id AND cls.deleted = 0 
                        INNER JOIN 
                        T_GSA_GSA gsa 
                     ON gsa.cls_id = cls.id AND gsa.deleted = 0 
                  Where APC.GSA_ID = gsa.id 
                    And rty_code = 'CLU' 
                )
      Or Exists
...
)

07/06/2011, 15h33
Mohamed.Houri
Citation:

Envoyé par punkoff

Y a-t-il des options dans tkprof pour avoir des plans plus détaillé ? avec les temps en particulier ?

D'après ce que je comprend des plans sorties, la partie V_TOT ce fait bien (1116 apc_id distinct) et c'est après que le problème se pose.

En particulier au moment où il crée une table temporaire (mais je ne sais pas à partir de quoi, ca n'est pas explicite dans le plan) pour soit préparer le cube soit joindre sur V_TOT.

Je regarderai plus en profondeur à midi, et je n'ai pas encore pu essayer avec les modifs que vous avez apporté.

Afin de pouvoir analyser exactement ce qu'est en train de faire le CBO, je vous conseille de faire ceci
Code:

1 2 3 4 5 (1) set linesize 150 (2) alter session set statistics_level=ALL (3) execute ta query; (4)select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
Ainsi, nous pourrions voir au travers de E-Rows, A-Rows, Starts et A-time les opérations du CBO sur les quelles un effort doit être dirigé

Bien à vous

Mohamed Houri
07/06/2011, 19h08
punkoff

1 pièce(s) jointe(s)

Citation:

Est-ce simplement la dernière étape de la requête qui est lente pour le second profil, ou bien est-ce une des étapes intermédiaires ?

Au vue de votre 2nd poste, j'avais effectivement testé bout par bout et c'est bien sur l'ajout de la dernière partie que tout chavire (la partie cube).

Citation:

Envoyé par mnitu

J’essayerai de réécrire la première partie ainsi

Bingo.

Les 2 plans semblent identiques maintenant.
Je vous ai joint le nouveau plan de la requête qui avait du mal.

Bon bein je ne commettrai plus cette erreur d'union qui semble faire bugger un peu l'optimiseur dans le cas présent ! (de plus on gagne en nombre de jointure effectué de cette manière)

Merci bien :)

Il me restera à bien tester tous les cas de figures pour voir comment ca réagit.

@Mohamed.Houri: je ne connaissais pas, je testerai :)

edit: c'est cool, plus de problème