Création d'index incohérent

**Robinson8171** · 23/11/2007, 11h39

Bonjour à tous

J'utilise ORACLE 9i.
J'ai une table que je remplis avec un million de lignes. Sur cette table, je n'ai pas créé (encore d'index) ni fait d'analyse.
Je crée ensuite un index (sans analyse).
Lorsque j'exécute une requête devant prendre l'index (pas de doute possible), elle ne le prend pas.
Je supprime l'index puis le recrée (avec le même script), là, la requête prend l'index.

Petit détail : à aucun moment, je n'ai analysé la table ou l'index.
Je ne souhaite pas faire d'analyse des objets (je suis dans le cadre d'une étude qui cherche à valider l'intérêt d'une analyse systématique des objets).

Pourquoi le premier index n'est-il pas "correct" ?

**LeoAnderson** · 23/11/2007, 11h42

sans code ni explain plan, il va être difficile de répondre

**orafrance** · 23/11/2007, 12h27

Envoyé par Robinson8171

Petit détail : à aucun moment, je n'ai analysé la table ou l'index.
Je ne souhaite pas faire d'analyse des objets (je suis dans le cadre d'une étude qui cherche à valider l'intérêt d'une analyse systématique des objets).

donc tu es en mode RULE donc l'ordre des tables et des colonnes est important... évidemment ce genre de pratique est à proscrire, Oracle n'appréciant guère le mélange avec et sans stats

**Robinson8171** · 23/11/2007, 12h37

Envoyé par orafrance

donc tu es en mode RULE donc l'ordre des tables et des colonnes est important... évidemment ce genre de pratique est à proscrire, Oracle n'appréciant guère le mélange avec et sans stats

Non, je suis en mode CHOOSE et je travaille SANS aucune analyse.

**orafrance** · 23/11/2007, 13h48

donc tu es en RULE

pas de stats = RBO... si ton appli n'impose pas ce mode c'est une très mauvaise idée

**mnitu** · 23/11/2007, 14h03

Envoyé par orafrance

donc tu es en RULE

pas de stats = RBO... si ton appli n'impose pas ce mode c'est une très mauvaise idée

En 9I je ne pense pas que c'est vrai

Comment faites-vous pour savoir que votre requête ne prends pas l'index ?

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
 
SQL> alter session set optimizer_mode=CHOOSE;
 
Session modifiée.
 
SQL> set autotrace traceonly explain
SQL> create table titi as select * from big_table;
 
Table créée.
 
SQL> create index ix_titi on titi (owner, object_name);
 
Index créé.
 
SQL> select * from titi where owner='MNI';
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TITI'
   2    1     INDEX (RANGE SCAN) OF 'IX_TITI' (NON-UNIQUE)
 
 
 
SQL>

**Robinson8171** · 23/11/2007, 14h23

Je vois ce que tu veux me dire, mais j'ai (pardon) un peu de mal à tout saisir.
Lorsque j'exécute ma requête (select en jointure que ma table et sur une autre de 15 millions de lignes), j'ai le plan d'exécution suivant :
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 522 K 13391
SORT ORDER BY 522 K 42 M 13391
MERGE JOIN 522 K 42 M 6320
SORT JOIN 14 M 576 M 42
PARTITION RANGE ALL 1 17
INDEX FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 14 M 576 M 42 1 17
SORT JOIN 840 K 34 M 6278
TABLE ACCESS FULL HB_ETOIL_IN.TT_F_ELM_PASSAGE_TP 840 K 34 M 2

La table incriminée est TT_F_ELM_PASSAGE_TP (1 millions de lignes).

L'explain plan indique bien que le mode est CHOOSE... d'où mon incompréhension

**mnitu** · 23/11/2007, 14h27

Envoyé par mnitu

En 9I je ne pense pas que c'est vrai

Non mais quelle connerie j'ai dit

CHOOSE
The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

**Robinson8171** · 23/11/2007, 14h40

Je commence à comprendre, mais j'ai toujours un souci :
Après chargement de la table et création de l'index, j'ai le plan d'exécution suivant :

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 41 24
SORT ORDER BY 41 3 K 24
NESTED LOOPS 41 3 K 22
TABLE ACCESS FULL HB_ETOIL_IN.TT_F_ELM_PASSAGE_TP 82 3 K 2
PARTITION RANGE ITERATOR KEY KEY
INDEX FAST FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 1 43 4 KEY KEY

Je droppe l'index et je le recrée. J'ai alors le plan suivant :
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 10874
SORT ORDER BY 1 86 10874
MERGE JOIN 1 86 10872
SORT JOIN 22 M 932 M 42
PARTITION RANGE ALL 1 17
INDEX FULL SCAN HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK 22 M 932 M 42 1 17
SORT JOIN 1 M 59 M 10830
INDEX FAST FULL SCAN HB_ETOIL_IN.IX_TT_F_ELM_PASSAGE_TP_01 1 M 59 M 4

Pourquoi un changement de comportement ???

Je joins le script de création de l'index...

Code :

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

1
2
3
4
create index IX_TT_F_ELM_PASSAGE_TP_01 on TT_F_ELM_PASSAGE_TP(DW_SY_PARCOURS, DW_SY_PASSAGE, DW_CO_DA_EFFET_PARCOURS, DW_CO_FAM_CONTENANT) 
NOLOGGING 
TABLESPACE HB_ETOIL_IN_INDX01 
STORAGE(INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) INITRANS 2 MAXTRANS 255 PCTFREE 0 PARALLEL (DEGREE 1)

**orafrance** · 23/11/2007, 14h49

Envoyé par mnitu

En 9I je ne pense pas que c'est vrai

Je ne raconte pas toujours n'importe quoi

http://download.oracle.com/docs/cd/B...mops.htm#38218

CHOOSE

The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

* If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
* If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
* If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

CHOOSE sans stats = RULE... sauf éventuellement avec l'autosampling mais j'en suis même pas sûr

Edit : argh... grillé

**orafrance** · 23/11/2007, 14h51

Robinson8171

t'as vu comme des balises CODE rendent ton message plus lisible ?

Et j'insiste, l'optimiseur utilise les règles donc l'ordre des tables est important... je ne saurais trop te conseiller de calculer les stats

**mnitu** · 23/11/2007, 15h05

Pour orafrance (je l'ai déjà dit)

Pourriez-vous faire une trace sql dans les deux cas ?

**Robinson8171** · 23/11/2007, 15h05

1° Pardon pour les balises "code" : je débute... La prochaine fois...

2° revenons à nos moutons : en inversant l'ordre des 2 tables, le plan d'exécution est le même (avec les balises "code"

)

Code :

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

1
2
3
4
5
6
7
8
 
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
SELECT STATEMENT Optimizer Mode=CHOOSE		41  	 	24  	 	      	             	 
  SORT ORDER BY		41  	3 K	24  	 	      	             	 
    NESTED LOOPS		41  	3 K	22  	 	      	             	 
      TABLE ACCESS FULL	HB_ETOIL_IN.TT_F_ELM_PASSAGE_TP	82  	3 K	2  	 	      	             	 
      PARTITION RANGE ITERATOR		  	 	 	 	      	            KEY	KEY
        INDEX FAST FULL SCAN	HB_ETOIL_IN.IX_DWR_F_ELM_PASSAGE_TP_PK	1  	43  	4  	 	      	            KEY	KEY

3° Admettons qu'il soit préférable de calculer les stats des tables, quid si les stats ne sont pas à jour (et qu'il ne soit pas possible que les stats soient à jour au moment de la requête).
Quelle est le mieux adapté ? pas d'analyse ? des analyses incorrectes ?

**orafrance** · 23/11/2007, 15h08

j'ai pas vu la requête SQL que tu exécutes

le hint INDEX t'aidera surement

3°) vaut mieux des stats à jour

sachant que les stats n'ont pas forcément besoin d'être à jour à la ligne prêt... un ESTIMATE fonctionne déjà très bien

pour info : http://www.oracle.com/technology/pro...ily/Aug17.html

**Robinson8171** · 23/11/2007, 15h37

Voici la requête : elle est toute "simple", pas de hint, rien.
D'ailleurs, avec un hint "INDEX", le temps de réponse n'est pas meilleure.
une jointure entre 2 tables :
- DWR_F_ELM_PASSAGE_TP (partitionnée DW_CO_DA_EFFET_PARCOURS de la clé) sur le champ avec 15 millions de lignes et un index sur la clé de jointure
- TT_F_ELM_PASSAGE_TP avec 1 million de lignes et un index sur la clé de jointure
Aucune des tables n'a d'analyse

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT TT_F_ELM_PASSAGE_TP.DW_SY_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_SY_PASSAGE, TT_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT 
FROM
 DWR_F_ELM_PASSAGE_TP, TT_F_ELM_PASSAGE_TP 
WHERE
 DWR_F_ELM_PASSAGE_TP.DW_SY_PARCOURS =TT_F_ELM_PASSAGE_TP.DW_SY_PARCOURS
AND
DWR_F_ELM_PASSAGE_TP.DW_SY_PASSAGE = TT_F_ELM_PASSAGE_TP.DW_SY_PASSAGE
AND
DWR_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS = TT_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS
AND
DWR_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT = TT_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT 
ORDER BY
 TT_F_ELM_PASSAGE_TP.DW_SY_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_SY_PASSAGE, TT_F_ELM_PASSAGE_TP.DW_CO_DA_EFFET_PARCOURS, TT_F_ELM_PASSAGE_TP.DW_CO_FAM_CONTENANT

Mon problème concernant les analyses est que cela coûte en temps. Il est également problématique de les calculer lorsqu'on charge les tables avec un ETL (nous sommes dans un entrepôt).
Je cherche donc le meilleur compromis pour améliorer les performances : c'est pour ça que j'étudie la solution sans analyse (et j'ai bien dit "étudier").

**mnitu** · 23/11/2007, 20h35

D'abord vous utilisez le CBO!

Le chemin d'accès INDEX FAST FULL SCAN n'est pas disponible pour le RBO

Oracle 9.2 performance tunning guide
...
Fast full scan is available only with the CBO ...

Très probablement vous avez analysé les tables une fois dans le passé et ensuite vous n'avez pas supprimé les statistiques (examinez la colonne last_analyzed dans dba_tables ou dba_indexes pour les objets concernés). Donc vous est dans la pire de situations.

Mon problème concernant les analyses est que cela coûte en temps. Il est également problématique de les calculer lorsqu'on charge les tables avec un ETL (nous sommes dans un entrepôt).
Je cherche donc le meilleur compromis pour améliorer les performances : c'est pour ça que j'étudie la solution sans analyse (et j'ai bien dit "étudier").

Si vous trouvez que le RBO vous suffit mettez optimiser goal = RULE.

Ça marche y compris pour ORACLE10. Si vous voulez utiliser CBO vous n'avez pas besoin forcement d'analyser les tables, indexes ,etc. chaque fois que vous chargez les tables avec ETL. L'idée est que si vous connaissez les bons «*statistiques*» vous pouvez le charger dans les tables sans faire l'analyse.

**orafrance** · 25/11/2007, 19h09

Envoyé par Robinson8171

D'ailleurs, avec un hint "INDEX", le temps de réponse n'est pas meilleure.
une jointure entre 2 tables :

voila pourquoi l'index n'est pas utilisé... il n'est pas intéressant pour ta requête

Création d'index incohérent

SQL Oracle

Discussions similaires

Partager

Partager