Optimisation requete oracle

Version imprimable

30/08/2006, 20h11
Mehdilis

Optimisation requete oracle

Bonjour à tous,
Mon prbm est le suivant:
Sur ma base de production oracle, l’optimizer_mode est choose, quand je lance ma requête depuis sqlplus avec le hint _/* + CHOOSE */ le résultat est rapide, 7 minutes environs, par contre, quand je la en mode rule _/* +RULE*/ elle ne répond pas, je serai obligé de killer la session oracle. Mais ça c’est récent, car avant elle répondait dans les deux modes ? Qu’est ce qu’il faut faire ? et pourquoi avant elle répondait et maintenant non ?
Merci d'avance.
31/08/2006, 08h26
orafrance

il est rare d'avoir une requête qui est aussi adaptée en RULE qu'en CHOOSE. Je pense que désormais les stats sont suffisantes pour que le mode CHOOSE soit favorable.
31/08/2006, 09h19
Oraman

comme a dit FRED_D il,faut mettre à jour les stats sinon il faut voir aussi de plus prêt les 2 plan d'execution
31/08/2006, 09h43
remi4444

le mode /*+ RULE */ est utiliser quand on veux avoir un plan d'exécution fixe, mais attention, si il y a une évolution dans la répartition des données ou la structure (par ex: la création d'un nouvel index, une table de 5 ligne passant à 2000 lignes, un critère auparavant trés discriminant qui ne l'est plus etc...)

Y a-t-il eu une évolution de la sorte sur ta base ?

Attention les HINT sont très sensible et lorsque tu fais une erreur de syntaxe, oracle ne lève aucune exception et exécute la requête comme si le hint n'existait pas. Dans ton exemple, il y a un espace entre "*" et "+" ce qui fait qu'il ne sera pas pris en compte. Pour valider ton hint, il faut donc impérativement éditer le plan d'exécution.

Bonjour,
Je vous remercie pour votre réactivité, voici la requete et le plan d'execution:

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
 
SELECT /*+ rule */ pr.imsi, pr.msisdn, TO_CHAR(pr.entdate, 'DD/MM/YYYY'),
pc.offre_id,pc.sale_syst_cust,pc.iccid, pc.imei, TO_CHAR(pc.gencod), pc.co_id,
pc.vente_id, pc.flag_feh_sale, pc.vidpmsold, pc.msisdn,pc.code_plan 
FROM ppc_sim_cust_hst pc, ppctn_hist pr, info_contr_text ict 
WHERE pr.imsi = pc.imsi
AND ict.co_id = pc.co_id AND ict.text15 IS NULL AND pr.old_state = 1 AND
pr.new_state = 2 AND pr.entdate < (sysdate-(60/1440)) AND (pr.msisdn,
pr.ppctn_reqno) IN (SELECT pp.msisdn, MIN(pp.ppctn_reqno) FROM ppctn_hist pp
WHERE pp.old_state = 1 AND pp.new_state = 2 group by pp.msisdn) AND
pc.STAMSI='P' ;
 
 
OPERATION                                               OBJECT                              OPTIMIZER              COST       CARD
------------------------------------------------------- ----------------------------------- ---------------- ---------- ----------
SELECT STATEMENT                                                                            HINT: RULE
  FILTER
    SORT (GROUP BY)
      TABLE ACCESS (BY INDEX ROWID)                     PPC_SIM_CUST_HST                    ANALYZED
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              TABLE ACCESS (FULL)                       INFO_CONTR_TEXT                     ANALYZED
              TABLE ACCESS (BY INDEX ROWID)             PPCTN_HIST                          ANALYZED
                INDEX (RANGE SCAN)                      PPCTN_HISTI3 (NON-UNIQUE)           ANALYZED
            TABLE ACCESS (BY INDEX ROWID)               PPCTN_HIST                          ANALYZED
              AND-EQUAL
                INDEX (RANGE SCAN)                      IDX_PPCTN_HIST_MSISDN (NON-UNIQUE)  ANALYZED
                INDEX (RANGE SCAN)                      PPCTN_HISTI3 (NON-UNIQUE)           ANALYZED
          INDEX (RANGE SCAN)                            PPC_SIM_CUST_HST_CO_ID (NON-UNIQUE) ANALYZED
 
OWNER        TABLE_NAME                 NUM_ROWS AVG_ROW_LEN      CHAIN_COUNT     BLOCKS EMPTY_BLOCKS  AVG_SPACE LAST_ANALYZED    GS
------------ ------------------------ ---------- ----------- ---------------- ---------- ------------ ---------- ---------------- --
SYSADM       PPC_SIM_CUST_HST              16118         158          0              385          126       1298 27/08/2006 00:11 N
             INFO_CONTR_TEXT             1244636         164     440717   35%      39557         9594       2336 27/08/2006 00:13 N
             PPCTN_HIST                 12822418          90        164    0%     240420         5339       3050 27/08/2006 00:56 N
 
 
Mode CHOOSE
 
OPERATION                                               OBJECT                              OPTIMIZER              COST       CARD
------------------------------------------------------- ----------------------------------- ---------------- ---------- ----------
SELECT STATEMENT                                                                            CHOOSE                41053          1
  HASH JOIN (SEMI)                                                                                                41053          1
    TABLE ACCESS (BY INDEX ROWID)                       PPCTN_HIST                          ANALYZED                 10      38119
      NESTED LOOPS                                                                                                   72          1
        NESTED LOOPS                                                                                                 62          1
          TABLE ACCESS (FULL)                           PPC_SIM_CUST_HST                    ANALYZED                 60          1
          TABLE ACCESS (BY INDEX ROWID)                 INFO_CONTR_TEXT                     ANALYZED                  2     200437
            INDEX (UNIQUE SCAN)                         PKINFO_CONTR_TEXT (UNIQUE)          ANALYZED                  1          6
        INDEX (RANGE SCAN)                              PPCTN_HIST_IMSI (NON-UNIQUE)        ANALYZED                  3         10
    VIEW                                                VW_NSO_1                                                  40978     575464
      SORT (GROUP BY)                                                                                             40978     575464
        TABLE ACCESS (FULL)                             PPCTN_HIST                          ANALYZED              36491     762371

Merci d'avance,

Edit par bouyao : pense à :tagcode: :scarymov:

31/08/2006, 10h08
remi4444

tu va te faire gronder tu n'a pas utiliser les balises [ CODE ] [/ CODE ] ce qui aurait rendu les choses plus lisible. Pour le plan en rule, il faudrait avoir le plan à l'époque ou ça fonctionnait bien pour comprendre, sans ça, tu sera réduit à faire des suppositions quand à la modif de ta base comme je le disais plus haut.

Attention utiliser le mode rule signifie que tu maitrise de A à Z tout ton plan et surtout la méthode pour y parvenir.

Sinon dans l'absolu, heureusement que le mode CHOOSE arrive à un meilleur résultat que RULE, sinon ça voudrait dire que l'optimiseur ne sert à rien.... :mouarf:
31/08/2006, 10h32
Oraman

une autre info qui peut eclaircir les choses encore plus : la taille des tables en nombre d'enregistrement ? je suppose que INFO_CONTR_TEXT est la plus grande non ?peux tu mettre à jour les stats et refaire le test qu'est ce que ça donne ?
31/08/2006, 10h38
remi4444

Citation:

Envoyé par Oraman

une autre info qui peut eclaircir les choses encore plus : la taille des tables en nombre d'enregistrement ? je suppose que INFO_CONTR_TEXT est la plus grande non ?peux tu mettre à jour les stats et refaire le test qu'est ce que ça donne ?

Si je ne m'abuse, les stats n'auront aucun effet sur le mode rule... par contre, il faut effectivement mettre régulièrement à jour les stats pour les autres modes.
31/08/2006, 10h47
bouyao

Juste un petit plus.

Si tu utilise Bitmap Index ou les partitions ou d'autres nouvelles fonctionalités, Normalement !oracle utilisera toujours CHOOSE, car ces nouvelles fonctionalités n'etait pas implenté dans le mode RULE.

si tu a un index bitmap et tu veut utiliser le mode RULE alors :
A vérifier si c'est 1 ou 2
1. soit oracle ignore le mode RULE et utilise CHOOSE
2. ou bien il n'utilise pas l'index bitmap
31/08/2006, 10h55
remi4444

2 petites questions:

- Quand le mode RULE à été choisi, est-ce que c'était réfléchi avec l'ordre des tables, l'ordre des clauses where etc... ou était-ce juste issus de tests empiriques ?

- Quel est le nombre de lignes ramenées par la requête ? (histoire de savoir si ça vaux le coup de tenter d'optimiser mieux que ça... parcequ'avec une table à 12 milions de lignes en accès complet, c'est tout de même assez copieux...
01/09/2006, 10h36
Mehdilis

Bonjour,
J’ai apprécie beaucoup vos réponses que je vous en remercie.
Une information supplémentaire, sachant que le mode CHOOSE fait l’affaire. Mais j’aimerais bien savoir svp la raison pour le mode RULE, pour quoi la requête s’exécutée en mode rule depuis Trois ans, mais depuis un mois que la requête ne répondait pas( toujours en RULE), ça pourrait venir de quoi ? (ci-après le plan d’exécution de la requête ,
Merci pour votre réponse
01/09/2006, 10h58
remi4444

Je pensais t'avoir répondu, à mon avis ça s'explique par une évolution dans ta base de donnée qui peu provenir:
- Changement du contenu de certaines tables (ex: peut etre qu'avant il n'y avait que 10 lignes qui vérifiait la condition "pp.new_state = 2" alors que maintenant il y en a 10 000)
- Ajout ou suppression d'index.