[Oracle 8.1.7] Choix d'une mauvaise stratégie

**mendosa** · 26/10/2005, 16h20

Bonjour,

j'ai un problème de choix d'index par Oracle.

Version Oracle : 8.1.7
Serveur Unix Aix 5.1
Methode : RULE

Soit 2 tables
CAREC ( 60'000'000 de lignes)
ET GLFAC ( 6'000'000 de lignes)

CAREC (
CAREC_NUM NOT NULL NUMBER,
CAREC_ORD NOT NULL NUMBER,
GLFAC_REF NUMBER,
CAINT_NUM NUMBER,
... )

GLFAC
(
GLFAC_REF NOT NULL NUMBER,
GLFAC_DTD NOT NULL DATE,
GLFAC_DTF NOT NULL DATE,
GLFAC_DATECH NOT NULL DATE,
...)

Sur la table CAREC, on a plusieurs indexs dont 2 en particulier
CARECI5 et CAREC_SPI9

CREATE INDEX CARECI5 ON
CAREC(CAINT_NUM, CAREC_STATUT, CAREC_DATREF)
TABLESPACE CA_INDX PCTFREE 20
STORAGE(INITIAL 104857600 NEXT 104857600 PCTINCREASE 0 ) ;

CREATE INDEX ULISM.CAREC_SPI9 ON
CAREC(CAINT_NUM,GLFAC_REF)
TABLESPACE CA_INDX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 200M NEXT 200M PCTINCREASE 0 ) ;

La requete SQL

select max(glfac.glfac_datech)
from carec,glfac
where carec.glfac_ref = glfac.glfac_ref
and carec.caint_num = 123323
and glfac.glfac_datech < '01/10/2005'
and glfac.glfac_ref != 1223333
and glfac.glfac_numfac is not null
;

La requête doit normalement utilisé l'index CAREC_SPI9, mais force est de constater qu'en fonction de la date de création, ORACLE prend l'index le plus recent ( CARECI5 quand ce dernier est recréer ).

Explain plan quand CARECI5 est l'index créer le plus recemment

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CAREC'
4 3 INDEX (RANGE SCAN) OF 'CARECI5' (NON-UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'GLFAC'
6 5 INDEX (UNIQUE SCAN) OF 'GLFACP1' (UNIQUE)

Explain plan quand CAREC_SPI9 est l'index créer le plus recemment

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 INDEX (RANGE SCAN) OF 'CAREC_SPI9' (NON-UNIQUE)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'GLFAC'
5 4 INDEX (UNIQUE SCAN) OF 'GLFACP1' (UNIQUE)

Cette requête est utilisé dans un programme en PRO*C
Lorsqu'elle utilise CAREC_SPI9, temps de traitement 4h
Lorsqu'elle utilise CARECI5, temps de traitement 19h !!!

Pourquoi le fait de re-créer un index modifie la stratégie d'Oracle ?
Merci de votre aide.

**Jaouad** · 26/10/2005, 16h23

avez vous essayé un hint ou un outlines ?

**mendosa** · 26/10/2005, 16h30

J'ai forcé la requête à utiliser l'index CAREC_SPI9, mais la stratégie a été completement chamboulé ( Lecture de GLFAC par un autre index puis lecture de CAREC par CAREC_SPI9).

Pour le outline, honnêtement je ne sais pas ce que sait ?

**bouyao** · 26/10/2005, 16h40

Je te conseille la methode CBO au lieu de REGLE

**orafrance** · 26/10/2005, 16h46

Envoyé par mendosa

Pour le outline, honnêtement je ne sais pas ce que sait ?

cela permet de sauvegarder le plan d'exécution. Pratique quand tu veut appliquer le même plan dans toutes tes bases

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c16sqlpl.htm#8546

http://download-west.oracle.com/docs/cd/B10501_01/em.920/a86647/outlinem.htm#1001303

**orafrance** · 26/10/2005, 16h47

Envoyé par bouyao

Je te conseille la methode CBO au lieu de REGLE

+1 et il ne faut pas oublier de calculer les stats

Attention, le CBO n'est pas supporté par tous les ERP

**mendosa** · 26/10/2005, 16h53

Pour le CBO, je pense pas que je puisse l'utiliser.
La base de donnée est proprietaire (SOPRA) et ils recommandent vivement de rester en mode rule.

Donc je ne peux pas faire de stats sur les tables. (pour le moment)

Une solution consiste a recrer le bon index avant de lancer le traitement ( mensuel) mais cela ne m'explique pas le comportement d'Oracle, et si cela ne cache pas d'autres problemes car je constate des degradations sur un certain nombre de programme.

Meci

**bouyao** · 26/10/2005, 17h00

Le hint ne marchera pas.
Car des qu'on utilise un hint (exception le hint RULE) oracle choisi le CBO.
et comme il n'ya pas de stats, Orcale choisi un plan erroné.

**mendosa** · 27/10/2005, 09h55

Avez-vous d'autres pistes ?
Ou connaissance de bugg Oracle sur ce point.

Merci de votre aide

**orafrance** · 27/10/2005, 10h01

pas besoin d'autre piste, le problème vient du mode RULE, il faut jouer avec l'ordre des tables et/ou colonnes. Le mieux serait de demander à Sopra ce qui empêche d'utiliser le CBO et surtout d'optimiser leur traitement

**Jaouad** · 27/10/2005, 10h08

il ne me sembla ps qu'il y ai de bug sur la 817, mais le mode rule fonctionne en suivant des régles , et parfois il peut " déconner " notamment lorsqu'il y a un index de crée

C'est pour cela que le mode choose est profitable

**ducho** · 27/10/2005, 14h28

Bonjour,

Envoyé par bouyao

Le hint ne marchera pas.
Car des qu'on utilise un hint (exception le hint RULE) oracle choisi le CBO.
et comme il n'ya pas de stats, Orcale choisi un plan erroné

cela n' est pas toujours vrai, si dans init_ora est précisé le mode rule,
oracle n' utilisera pas le mode cbo et les hints fonctionnent .
j' en ai fait l' expérience avec le produit PLEIADES de sopra
qui effectivement ne souhaite pas qu' on utilise le mode cbo
pour la plupart de ses applicatifs .
chez nous : oracle 8.1.7.4 sous AIX 5.2

cdlt

**bouyao** · 27/10/2005, 14h40

Metalink :

Any hint, except RULE, causes CBO to be used.
It is very important to note that a HINT cannot be 'turned off' by any parameter settings

Note:35934.1

**childeric** · 27/10/2005, 16h26

Envoyé par bouyao

Metalink :

Any hint, except RULE, causes CBO to be used.
It is very important to note that a HINT cannot be 'turned off' by any parameter settings

Note:35934.1

Si aucune statistique n'est calculé alors ( en 8 et 9) on se retrouve en rule

néenmoins il faut mieux passer en CBO car cela est en moyenne plus rapide, de plus on rappel quen 10g le mode RULE n'est plus supporté.

CD · 27/10/2005, 17h02

Oui, mais mendosa le précise bien, c'est une base progiciel propriétaire.

Selon les cas, le support du produit peut être "dévalidé" si on change les paramètres obligatoires fixés par l'éditeur.

C'est le cas chez Siebel, ou pour les bases des applis Sopra aussi...

Sinon, pour ce qui concerne les hints, même en étant en mode rule et si on excepte le hint RULE, on passera toujours en CBO si le hint est syntaxiquement valable : (je prend une table toto1 avec comme colonnes Id et Texte)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
test@BROCANTE> alter session set optimizer_goal=RULE;
 
Session modifiÚe.
 
test@BROCANTE> set autotrace traceonly explain
16:51:54 test@BROCANTE> select * from toto1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (FULL) OF 'TOTO1'

J'utilise bien le mode RULE là (pas de cardinalité, pas de cost...)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
test@BROCANTE> select /*+ full(id) */ * from toto1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=1 Card=2000 Bytes=1300
          00)
 
   1    0   TABLE ACCESS (FULL) OF 'TOTO1' (Cost=1 Card=2000 Bytes=130
          000)

J'ai mis un hint, j'ai utilisé le mode CHOOSE automatiquement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
test@BROCANTE> select /*+ full(rien) */ * from toto1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=1 Card=2000 Bytes=1300
          00)
 
   1    0   TABLE ACCESS (FULL) OF 'TOTO1' (Cost=1 Card=2000 Bytes=130
          000)

Le hint ne porte pas sur une colonne existante, mais il est syntaxiquement correct. J'utilise donc bien le mode CHOOSE...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
test@BROCANTE> select /*+ full rien */ * from toto1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (FULL) OF 'TOTO1'

Je suis repassé sur le mode RULE défini pour ma session, le hint n'étant pas valide.

**ducho** · 27/10/2005, 17h08

en faisant aussi des essais, j' ai fait la même constatation.
alors si les statistiques ne sont pas calculés, qu' on est en mode rule,
et qu' on force l' utilisation d' un index par hint,
(qui est bien utilisé )
comment se comporte l' optimiseur ?
ou cela veut-il dire que c' est une aberration de placer des hints
dans des requêtes sur des tables pour lesquelles il n' y a pas de stats ?

cdlt

**bouyao** · 27/10/2005, 17h10

Envoyé par ducho

cela veut-il dire que c' est une aberration de placer des hints
dans des requêtes sur des tables pour lesquelles il n' y a pas de stats ?

OUI

**mendosa** · 27/10/2005, 17h31

Merci de vos précisions.

Il est clair que le mode RULE est a proscrire.
Mais sur cette base, nous ne pouvons pas, bien que mon DBA n'a cessé de le demander à SOPRA.

Pour ce traitement, je vais donc soit
- Recréer l'index ( index spécifque ) chaque mois
ou
- utiliser un outline.

Merci de votre aide.

[Oracle 8.1.7] Choix d'une mauvaise stratégie

Oracle

Discussions similaires

Partager

Partager