IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

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


Sujet :

SQL Oracle

  1. #1
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut [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 ?

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    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.

  3. #3
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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

  4. #4
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    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 :
    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 !

  5. #5
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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à.

  6. #6
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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
    Fichiers attachés Fichiers attachés

  7. #7
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    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.

  8. #8
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    exec DBMS_STATS.GATHER_SCHEMA_STATS('USRINT0002', cascade => true);
    J'ai aussi changé le paramètre que vous indiquiez :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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.

  9. #9
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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'
    )

  10. #10
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    Citation Envoyé par Waldar Voir le message
    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.

    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)

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

  11. #11
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    D'abord voilà votre 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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.

  12. #12
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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)

  13. #13
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    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 ?

  14. #14
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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é.

  15. #15
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Il suffit d'exécuter la requête par bout :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : 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
    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 :
    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.

  16. #16
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    J’essayerai de réécrire la première partie ainsi
    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
     
    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
    ...
    )

  17. #17
    Membre Expert

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Par défaut
    Citation Envoyé par punkoff Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  18. #18
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Par défaut
    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 Voir le message
    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
    Fichiers attachés Fichiers attachés

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Problème de performances sur site PHP - Zend
    Par trashyquaker dans le forum Firefox
    Réponses: 2
    Dernier message: 08/09/2008, 21h44
  2. problème de performance sur requête avec Tsearch2
    Par Morpheas dans le forum PostgreSQL
    Réponses: 0
    Dernier message: 05/02/2008, 12h25
  3. Problèmes de performances sur une base oracle 10g
    Par ORAMEL dans le forum Oracle
    Réponses: 3
    Dernier message: 11/09/2007, 09h11
  4. Réponses: 3
    Dernier message: 20/04/2007, 12h19
  5. Problème de performance sur une "grosse" BD
    Par frechy dans le forum Installation
    Réponses: 9
    Dernier message: 19/09/2005, 16h52

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo