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

Oracle Discussion :

[10g] Plan d'exécution modifié sur les tables protégées


Sujet :

Oracle

  1. #1
    Membre du Club
    Profil pro
    Inscrit en
    mars 2004
    Messages
    90
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mars 2004
    Messages : 90
    Points : 59
    Points
    59
    Par défaut [10g] Plan d'exécution modifié sur les tables protégées
    Bonjour,

    Je teste depuis quelque temps le fonctionnement de notre application maison sur Oracle 10g, en vue d'une prochaine migration.

    Le schéma de cette application comprend 4 tables protégées à l'aide du Fine Grained Access Control et d'un contexte utilisateur (ce système a été mis en place sous 8i).

    Lors de la migration 9i, aucun problème de performances au niveau de ces tables. En revanche, sous 10g, je constate que les plans d'exécution des requêtes impliquant ces tables sont totalement modifiés, et les coûts explosent !
    Si je désactive le système de protection, les plans d'exécution et les coûts redeviennent similaires à la 9i avec protection.

    Voici un example de requête, bp étant une table protégée :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT *
      FROM bp bp, bp bp1, ro ro, baitclng baitclng, bp_uses_bo bpusesbo
     WHERE (baitclng.ID = bp.ID)
       AND (bp.id_bp_sup = bp1.ID(+))
       AND (bp.id_ro = ro.ID(+))
       AND ((bpusesbo.id_bo = :"SYS_B_0") AND (bpusesbo.id_bp = baitclng.ID))
    Plan d'exécution sous 9i :
    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
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
     
    SELECT STATEMENT Optimizer Mode=CHOOSE		5  	 	5  	 	      	             	 
      NESTED LOOPS OUTER		5  	1 K	5  	 	      	             	 
        NESTED LOOPS OUTER		5  	670  	4  	 	      	             	 
          NESTED LOOPS		5  	540  	3  	 	      	             	 
            NESTED LOOPS		5  	125  	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	PBEXP.BP_USES_BO	5  	60  	1  	 	      	             	 
                INDEX RANGE SCAN	PBEXP.LIEN_3922_FK	5  	 	3  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	PBEXP.BAITCLNG	1  	13  	1  	 	      	             	 
                INDEX UNIQUE SCAN	PBEXP.PK_BAITCLNG	1  	 	 	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	PBEXP.BP	1  	83  	1  	 	      	             	 
              INDEX UNIQUE SCAN	PBEXP.PK_BP	1  	 	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PBEXP.RO	1  	26  	1  	 	      	             	 
            INDEX UNIQUE SCAN	PBEXP.PK_RO	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	PBEXP.BP	1  	83  	1  	 	      	             	 
          INDEX UNIQUE SCAN	PBEXP.PK_BP	1  	 	1
    Plan d'exécution sous 10g :
    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
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
     
    SELECT STATEMENT Optimizer Mode=CHOOSE		5  	 	1165  	 	      	             	 
      NESTED LOOPS OUTER		5  	1 K	1165  	 	      	             	 
        NESTED LOOPS OUTER		5  	670  	1164  	 	      	             	 
          HASH JOIN		5  	540  	1163  	 	      	             	 
            NESTED LOOPS		5  	125  	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	PB10G.BP_USES_BO	5  	60  	1  	 	      	             	 
                INDEX RANGE SCAN	PB10G.LIEN_3922_FK	5  	 	1  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	PB10G.BAITCLNG	1  	13  	1  	 	      	             	 
                INDEX UNIQUE SCAN	PB10G.PK_BAITCLNG	1  	 	1  	 	      	             	 
            VIEW	PB10G.BP	347 K	27 M	1156  	 	      	             	 
              TABLE ACCESS FULL	PB10G.BP	347 K	27 M	1156  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	PB10G.RO	1  	26  	1  	 	      	             	 
            INDEX UNIQUE SCAN	PB10G.PK_RO	1  	 	1  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	PB10G.BP	1  	83  	1  	 	      	             	 
          INDEX UNIQUE SCAN	PB10G.PK_BP	1  	 	1
    Désolée, ce n'est pas très lisible, mais le problème réside vraisemblablement dans cette ligne du plan :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
            VIEW	PB10G.BP	347 K	27 M	1156
    D'où vient ce VIEW ?

    Merci d'avance pour vos idées
    Expérience: nom dont les hommes baptisent leurs erreurs. Oscar Wilde

  2. #2
    Membre du Club
    Inscrit en
    février 2003
    Messages
    59
    Détails du profil
    Informations forums :
    Inscription : février 2003
    Messages : 59
    Points : 41
    Points
    41
    Par défaut
    Bonjour,

    Moi aussi je suis en train d'étudier une migration d'une base 8i vers une 10g, et ce que l'on constate pour beaucoup de requêtes, c'est que beaucoup de requêtes prennent beaucoup plus de temps genre de 5 min à 1h. Pourtant les stats sont calculées, les index ont l'air corrects ...

    En regardant les plans d'exécution, je constate que l'optimiseur met en place dans beaucoup de cas des hash join au lieu de nested loop. Et pourtant je n'ai pas de tables protégées ...

    Je ne sais pas si l'optimiseur privilégie davantage ce type de jointure hash join. Sur google, des personnes rencontrent aussi des pb de perf, et en regardant les plan d'exécution on voit encore le type de jointure utilisé est le hash à la place du nested loop

  3. #3
    Membre habitué
    Profil pro
    Inscrit en
    décembre 2005
    Messages
    138
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : décembre 2005
    Messages : 138
    Points : 166
    Points
    166
    Par défaut
    D'où vient ce VIEW ?
    Ce view vient du fait qu'ORACLE a probablement modifié le select pour le rendre plus performant en résolvant une partie de ton select (probablement le outer join) et faire une jointure en hash après.

    La jointure en hash est loin d'être le diable incarnée! S'il la choisit, c'est probablement que la mémoire allouée dans le hash sort area est suffisante pour résoudre la requête avec ce type de jointure.

    De plus, si l'optimisateur est en mode all_rows, ce qui veut dire qu'il choisira le plan le plus rapide pour sortir toutes les rangées, Souvent, le hash join est préconisé dans ce mode. Autrement, si on spécifie le mode first_rows, souvent le nested loop sera préconisé. Mais le temps d'exécution est plus long si on sort toutes les rangées.

    Autre fait intéressant :
    Pourtant les stats sont calculées
    Les stats présentes sont-elles valides, représentatives de l'environnement de production? Qu'est-ce qui a été calculé? (toutes les colonnes indexées, toutes les colonnes, seulement les pk, etc...) Comment, sous quelles classes (bucket)?

    Vive l'optimisation!

  4. #4
    Membre du Club
    Profil pro
    Inscrit en
    mars 2004
    Messages
    90
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mars 2004
    Messages : 90
    Points : 59
    Points
    59
    Par défaut
    Dans mon cas, le view ne rend absolument pas la requête plus performante, puisqu'elle est tellement longue qu'elle n'aboutit qu'au bout de plus d'1 heure, voire pas du tout...

    bobunny, dans ton cas, quelle est la valeur du paramètre OPTIMIZER_INDEX_COST_ADJ, car j'ai découvert récemment que ce paramètre avait un effet majeur sur les plans d'exécution, et la performance des requêtes. Par défaut il est à 100 je crois. Je l'ai mis à 1 et c'est le jour et la nuit
    Expérience: nom dont les hommes baptisent leurs erreurs. Oscar Wilde

  5. #5
    Membre du Club
    Inscrit en
    février 2003
    Messages
    59
    Détails du profil
    Informations forums :
    Inscription : février 2003
    Messages : 59
    Points : 41
    Points
    41
    Par défaut
    En ce qui concerne les stats, il me semble que ce sont toutes les colonnes indexés et des histogrammes pour certaines tables.

    Sinon j'ai essayé de jouer avec le paramètre OPTIMIZER_INDEX_COST_ADJ, et pas trop de résultat. Par défaut il était à 50. J'ai essayé de mettre à 1, la requête prenait plus de temps. Pour les autres valeurs, je restais à peu dans les mêmes temps d'exécution.

    Voicic 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
    INSERT INTO txtnresrebdettpe
                (numres, idftra, coddev, datdeb, datfin, nbrjou, qte, tau, basmon,
                 coddevres, resreb)
       SELECT   :b1, tmpresreb.idftra, tmpresreb.coddev, MIN (cal.datcal),
                MAX (cal.datcal), MAX (cal.datcal) - MIN (cal.datcal) + 1,
                qcv.qte, tmpresreb.valtau, tmpresreb.basmon, tmpresreb.coddev,
                -qcv.qte * (tmpresreb.valtau / 100) / tmpresreb.basmon
           FROM txtntmpresrebtpe tmpresreb, txtnqcvtpe qcv, txtncaltpe cal
          WHERE qcv.idfcps = tmpresreb.idfcps
            AND tmpresreb.indmodpai = 'F'
            AND cal.datcal >= tmpresreb.datdeb
            AND cal.datcal < NVL (tmpresreb.datfin, TO_DATE ('09-JUL-3069'))
            AND qcv.datdeb = (SELECT MAX (qcv2.datdeb)
                                FROM txtnqcvtpe qcv2
                               WHERE qcv2.idfcps = tmpresreb.idfcps)
            AND tmpresreb.codtrscli <> 2471
            AND NOT EXISTS (SELECT 1
                              FROM txtndcktpe dck
                             WHERE dck.idfcps = tmpresreb.idfcps)
       GROUP BY tmpresreb.idftra,
                tmpresreb.coddev,
                qcv.qte,
                tmpresreb.valtau,
                tmpresreb.basmon,
                qcv.datdeb,
                tmpresreb.datdeb
    et le plan associé :

    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
    Operation                                      Object Name                          Rows  Bytes Cost  Object Node In/Out  PStart  PStop
     
    INSERT STATEMENT Optimizer Mode=CHOOSE                                                1   21466                            
       HASH GROUP BY                                                                      1   143   21466                            
          HASH JOIN ANTI                                                                  1   143   21465                            
             TABLE ACCESS BY INDEX ROWID           OPS$TPEMANAG.TXTNQCVTPE                1   21    3                            
                NESTED LOOPS                                                              1   137   21462                            
                   HASH JOIN                                                              1   116   21459                            
                      NESTED LOOPS                                                        1   101   3                            
                         TABLE ACCESS FULL         OPS$TPEMANAG.TXTNTMPRESREBTPE          1   93    2                            
                         INDEX RANGE SCAN          OPS$TPEMANAG.CXTNCALTPE_1ID            250 1K    1                            
                      VIEW                         SYS.VW_SQ_1                            454K 6M   21450                            
                         SORT GROUP BY                                                    454K 8M   21450                            
                            TABLE ACCESS FULL OPS$TPEMANAG.TXTNQCVTPE                     5M   105M 7313                             
                   INDEX RANGE SCAN  OPS$TPEMANAG.CXTNQCVTPE_1ID 12      1                            
             TABLE ACCESS FULL OPS$TPEMANAG.TXTNDCKTPE 2   12    3

    en 8i, j'ai le plan suivant :

    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
    Operation	                                      Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
     
    INSERT STATEMENT Optimizer Mode=RULE		  	 	 	 	      	             	 
      SORT GROUP BY		  	 	 	 	      	             	 
        FILTER		  	 	 	 	      	             	 
          NESTED LOOPS		  	 	 	 	      	             	 
            NESTED LOOPS		  	 	 	 	      	             	 
              TABLE ACCESS FULL	                    OPS$TPESAGE.TXTNTMPRESREBTPE	  	 	 	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	          OPS$TPESAGE.TXTNQCVTPE	  	 	 	 	      	             	 
                AND-EQUAL		  	 	 	 	      	             	 
                  INDEX RANGE SCAN	                OPS$TPESAGE.CXTNQCVTPE_1ID	  	 	 	 	      	             	 
                  INDEX RANGE SCAN	                OPS$TPESAGE.CXTNQCVTPE_2ID	  	 	 	 	      	             	 
                    SORT AGGREGATE		  	 	 	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	  OPS$TPESAGE.TXTNQCVTPE	  	 	 	 	      	             	 
                        INDEX RANGE SCAN	          OPS$TPESAGE.CXTNQCVTPE_1ID	  	 	 	 	      	             	 
            INDEX RANGE SCAN	                      OPS$TPESAGE.CXTNCALTPE_1ID	  	 	 	 	      	             	 
          TABLE ACCESS FULL	                        OPS$TPESAGE.TXTNDCKTPE

  6. #6
    Membre du Club
    Inscrit en
    février 2003
    Messages
    59
    Détails du profil
    Informations forums :
    Inscription : février 2003
    Messages : 59
    Points : 41
    Points
    41
    Par défaut
    J'ai oublié de préciser que j'avais aussi une ligne VIEW dans le plan 10g???? Je ne sais pas d'où ça sort

  7. #7
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 070
    Points
    19 070
    Par défaut
    Citation Envoyé par zestrellita
    OPTIMIZER_INDEX_COST_ADJ, car j'ai découvert récemment que ce paramètre avait un effet majeur sur les plans d'exécution, et la performance des requêtes. Par défaut il est à 100 je crois. Je l'ai mis à 1 et c'est le jour et la nuit
    grosse erreur... en tout cas, extrémement dangereux... dans ce cas, Oracle privilégiera toujours les indexes même dans les cas où un FTS est meilleur. Les notes Oracle prévienne bien sur ce point il faut être très prudent avec ce paramètre. Il me semble par ailleurs avoir lu qu'en dessous de 80 ça n'avait aucun impact (i.e. 80 ou 2 c'est pareil )

  8. #8
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 070
    Points
    19 070
    Par défaut
    apparemment le problème vient du HASH, faudrait voir si la hash_area_size est suffisante

  9. #9
    Membre du Club
    Profil pro
    Inscrit en
    mars 2004
    Messages
    90
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mars 2004
    Messages : 90
    Points : 59
    Points
    59
    Par défaut
    Citation Envoyé par Fred_D
    grosse erreur... en tout cas, extrémement dangereux... dans ce cas, Oracle privilégiera toujours les indexes même dans les cas où un FTS est meilleur. Les notes Oracle prévienne bien sur ce point il faut être très prudent avec ce paramètre. Il me semble par ailleurs avoir lu qu'en dessous de 80 ça n'avait aucun impact (i.e. 80 ou 2 c'est pareil )
    Ah bon, dans ce cas il faut laisser à 100 ?
    Le problème c'est que dans le cas de nos requêtes les plus coûteuses, c'est réellement efficace, donc j'ai peur que le changement soit visible pour les utilisateurs, même si certaines petites requêtes s'avèrent mieux optimisées
    Le truc c'est que c'est l'ancien DBA qui positionnait ce paramètre à 1, déjà dès 8i, et lorsque j'ai migré en 9i et testé tout d'abord avec le paramètre par défaut (c-à-d 100), j'ai constaté une vraie dégradation de performances...

    Pour revenir à la 10g et l'utilisation de VIEW dans le plan d'exécution, j'ai trouvé le coupable : le paramètre OPTIMIZER_SECURE_VIEW_MERGING, un nouveau paramètre de 10g, à TRUE par défaut. En le mettant à FALSE, tout redevient normal, mais là encore il doit y avoir une raison pour l'avoir mis à TRUE ?
    Expérience: nom dont les hommes baptisent leurs erreurs. Oscar Wilde

  10. #10
    Membre averti Avatar de Wurlitzer
    Profil pro
    Inscrit en
    avril 2006
    Messages
    469
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : avril 2006
    Messages : 469
    Points : 408
    Points
    408
    Par défaut
    J'ai moi aussi constaté des améliorations en jouant sur le OPTIMIZER_INDEX_COST_ADJ. Moins de Hash join et plus de Nested loop et surtout puisque c'est le seul but, de meilleurs temps de réponse.

    D'ailleurs voilà ce qu'en dit Tom Kyte


    Citation Envoyé par AskTom
    o The default settings of OPTIMIZER_INDEX_CACHING = 0 and
    OPTIMIZER_INDEX_COST_ADJ = 100. These are typically appropriate for many data
    warehouse/reporting systems.

    o The settings of OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ =
    25. These are typically appropriate for many transactional/OLTP systems.

    http://http://asktom.oracle.com/pls/ask/f?p=4950:8:2108463103941163103::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6601251003901

  11. #11
    Membre du Club
    Profil pro
    Inscrit en
    mars 2004
    Messages
    90
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mars 2004
    Messages : 90
    Points : 59
    Points
    59
    Par défaut
    Merci Wurlitzer pour cette info. Je n'ai jamais joué sur le paramètre OPTIMIZER_INDEX_CACHING, ne connaissant pas vraiment son rôle, mais je vais essayer !
    Expérience: nom dont les hommes baptisent leurs erreurs. Oscar Wilde

  12. #12
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 070
    Points
    19 070
    Par défaut
    En effet, c'est une solution mais je reste convaincu que si les indexes sont mal utilisés c'est soit qu'ils sont mal définies soit les analyses sont incomplétes (manque l'histogramme par exemple) soit un soucis dans les paramètres. En tout cas, pour faire une nesteed loop à la place d'un hash j'aime autant mettre un hint

    Attention à l'utilisation de FIRST_ROWS aussi qui peut poser des problèmes

  13. #13
    Expert éminent sénior
    Avatar de orafrance
    Profil pro
    Inscrit en
    janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 44
    Localisation : France

    Informations forums :
    Inscription : janvier 2004
    Messages : 15 967
    Points : 19 070
    Points
    19 070
    Par défaut
    Pour info,

    J'ai trouvé ceci pour déterminer la valeur à indiquer : http://www.oracleadvice.com/Tips/optind.htm

    L'idée étant de regarder le rapport entre les attentes sur FTS et sur accés via index

    Nota : attention, la différence d'attente peut provenir d'autre chose que le simple choix entre FTS et index (charge CPU, migration de lignes, etc...)... le résultat n'est donc qu'une indication et non une valeur sûre

Discussions similaires

  1. infos sur les tables modifiées en DML
    Par debdba dans le forum Oracle
    Réponses: 7
    Dernier message: 27/08/2007, 13h16
  2. [MYSQL] Commentaires sur les tables et les champs
    Par luc2verga dans le forum Requêtes
    Réponses: 10
    Dernier message: 29/05/2007, 23h49
  3. [access] question sur les tables liées
    Par maxdwarf dans le forum Access
    Réponses: 4
    Dernier message: 29/06/2006, 09h30
  4. Question sur les tables inserted et deleted
    Par critok dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 27/06/2006, 22h03
  5. verrous sur les tables
    Par rv66 dans le forum Paradox
    Réponses: 2
    Dernier message: 04/09/2005, 20h15

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