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

Version imprimable

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:

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:

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:

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:

VIEW PB10G.BP 347 K 27 M 1156

D'où vient ce VIEW ?

Merci d'avance pour vos idées ;)

20/06/2006, 18h40
bobunny

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 :roll:
20/06/2006, 18h58
bpprive

Citation:

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 :

Citation:

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!
21/06/2006, 11h47
zestrellita

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

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:

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:

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:

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

21/06/2006, 15h20
bobunny

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 :(
21/06/2006, 15h35
orafrance

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

8O 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 ;))
21/06/2006, 15h37
orafrance

apparemment le problème vient du HASH, faudrait voir si la hash_area_size est suffisante :)
21/06/2006, 15h56
zestrellita

Citation:

Envoyé par Fred_D

8O 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 ?
22/06/2006, 09h22
Wurlitzer

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
22/06/2006, 10h09
zestrellita

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 ! ;)
22/06/2006, 10h10
orafrance

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 ;)
22/06/2006, 10h26
orafrance

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