Discussion :

[jo007]

J'ai une table partitionnée :
T1 (C1 ,C2,C3) avec 2 index Partitionnés I1 (C1,C2) et I2 (C1,C3)
je fais :

Code :

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

select * from T1 where c1=:1 and c2=:2

Il passe bien par l index I1

Code :

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

select * from T1 where c1=:1 and c3=:2

Il passe bien par l index I2

Par contre si je fais un dbms_stat sur la table en cascade = true (inclus les index) , dans la 2 eme requete il fait un FULL .
Avez vous une idée la dessus ?

[Modération, Orafrance : Pensez à utiliser la balise [CODE] qui améliore la lisibilité, merci]
[Modération, LeoAnderson : Titre original ("Optimiseur Oracle fou") édité pour plus de précision]

[lalystar]

Comment est partitionée ta table, et tes indexes ? Sont ils locaux/globaux ?


Laly.

[orafrance]

L'index n'est probablement pas intéressant c'est tout... si la requête n'est pas suffisamment discriminante ça peut être plus avantageux de faire un FULL SCAN d'autant que là, il ne fait qu'un FULL d'une partition

[jo007]

Partitionnement Local . Par le champ C1
Au niveau du coup c'est de l'ordre de 120 par L'index et 2000 en Full , il aurait plutot interet d'utiliser l'index .
Surtout que c'est ce qu'il fait avant de passer les stats dessus .

[bouyao]

Le meilleur plan d'execution ca veut pas dire que la requette SQL soit la plus rapide.

Le fait de lancer DBMS_STAT ca rend inutilisable dans le SGA les anciens plan d'executions et Oracle recomence de nouveau à trouver un autre plan d'execution.

[SheikYerbouti]

Pouvez-vous donner la syntaxe de l'instruction DBMS_STATS.GATHER_TABLE_STATS() que vous utilisez ?

[jo007]

Code :

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

1
2
3
4
dbms_stat.gather_table_stats(
   ownname =>'TOTO',
   tabname=>'T1',
   cascade=>true);

Et au final je vous assure que la requete est beaucoup plus longue en Full que par l'index ...

Je viens de refaire le test et la requete met 10 s en full , et moins de 1 s par l'Index ..

[SheikYerbouti]

Essayez avec granularity => 'ALL'

[jo007]

Merci je vais essayer ca , par contre ca n'explique pas pourquoi oracle choisit un plan aussi mauvais .

[thomasjcj]

Es ce que la table est ou a été soumise à des DELETE massifs ?

[jo007]

Ce sont des Tables (2 dans le meme cas) , avec plus de 20 Millions de lignes , et en effet il y a de nombreux traitements qui les attaquent directement et en l'occurence des delete massifs .
Explicitement tu entends quoi par cette question ?

[thomasjcj]

Apres un Delete massif, les statistiques calculees sur les valeur de la colonne (cardinalité..) indexees seront logiquement differentes.
Par contre le nombre de blocs reservé par la table et par l'index lui reste identique (la HWM ne bouge pas).
Donc faible nombre de donnees pour une tres grand nombre de blocs. Dans ce cas de figures, les statistiques en particulier sur les donnees ne sont peut etre plus tres fiables, ce qui fait que l'optimiseur choisi un chemin d'acces erroné. C'est juste une supposition que je fais la.

Tu pourrais peut etre poster les infos relatives a ton indexe et ta table tirés des vue V$. Ca aiderait a anlayzer.

[thomasjcj]

Une autre idee:
La requete utilise des variables bindees. Dans ce cas l'optimiseur ne sait pas quelle valeur de l'index est a rechercher et fait un postulat a priori sur le nombre de lignes a recuperer. Ceci peut se reveler tres loin de la realité si la distribution des valeurs de l'index est tres heterogenes par ex.

Es ce que tu as les memes plans avec des valeurs fixees pour c1 et c3?

[bouyao]

Voir Note:239672.1 sur metalink

You defined a routine to gather tables and indexes statistics and you noticed that gathering them using DBMS_STATS generated a different execution plan. If you use ANALYZE TABLE, it generates better execution plan and performance of database is not affected

Symptom(s)
~~~~~~~~~~

Bad performance of sql statements Performance of database degradated Users are complaining about response time of database

Change(s)
~~~~~~~~~~

You used to gathering statistics with analyze table statement but now is using DBMS_STAT's procedures

Cause
~~~~~~~

Check how the procedure DBMS_STAT.GATHER_SCHEMA|TABLE_stats is invoked.

exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',NULL,FALSE,NULL,2,'ALL',TRUE); In this case, method_opt assumes null and DBMS_STATS doesn´t collect statistics for columns and it results in bad executions plans

Fix
~~~~

Make sure that you use named parameters and avoid use values without parameter to invoke the procedure DBMS_STAT.GATHER_SCHEMA|TABLE_stats

exec DBMS_STATS.GATHER_SCHEMA_STATS( - OWNNAME => 'SCOTT', BLOCK_SAMPLE => FALSE, DEGREE => 2, - GRANULARITY => 'ALL' ,CASCADE => TRUE );

Also make sure that the ANALYZE command to the DBMS_STATS command are equivalent by comparing them

[jo007]

Oui j'ai essayé avec des valeurs pour c1 et c3 , c'est idem.
Par contre la note metalink me semble bien pertinente a ce sujet
Ca confirme ton conseil pour le granularity=>'ALL'.
Encore Merci j'essaye ca ce week end , et je vous donne le resultat lundi .
Jonathan

[LeoAnderson]

Il faudrait également vérifier si vous êtes bien dans le contexte décrit par cette note, notamment :

If you use ANALYZE TABLE, it generates better execution plan and performance of database is not affected

[jo007]

Bon et bien j'ai essayé le stat en granularity=>'ALL' et c pareil il fait desormais un FUll table scan.
Je vais donc essayer un ANALYSE TABLE ... meme si ca me derange un peu suite a tout ce qui se dit sur cette vieille methode .

[jo007]

J'ai peut etre une idee :
Ce serait le fait d'avoir une table T1 partitionnée sur le champ C1 avec un index partitionnée LOCAL (dc sur C1) et indexée sur C1,C2,C3 , peut etre que trop d'index tue l'index .

J'espere que ma petite explication est assez clair .

Je suis en test la dessus pour le moment , je vous tiens au courant .

[jo007]

Bon et bien pour info , apres avoir posé un TAR sur metalink , Quand on a des tables partitionnées avec index locaux , il faut ABSOLUMENT faire un calcul de stat AVEC HISTOGRAMMES , c'est à dire rajouter :
(... method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY')
au dbms_stats.gather_table_stats.

Merci à tous .

Jo