Discussion :

[deldin]

j'ai un gros problème de performance sur ma bdd. Après analyse du problème, la requête qui plombe les perfs est la suivante :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT count(table_a.profil)
FROM table_a, table_b
WHERE table_a.so0_type = :b1
      AND table_a.code = table_b.code
      AND table_b.etat = 'Actif'
      AND table_a.type = 'Client'
      AND table_b.id = :b2;

Il y a deux index sur la table_b qui contient 27 000 000 lignes :
- un index non unique sur la colonne id : 18 000 000 clés pour l'index
- un index non unique sur la colonne code : 540 000 clés pour clés
Il n'y a aucun index sur la table_a qui contient 50 lignes

Le plan d'exécution indique que c'est l'index sur la colonne code qui est utilisé. Pourtant, il est beaucoup moins discriminant. Avec l'index code, la vitesse de traitement est de 40 lignes/minutes. Avec l'index id, la vitesse passe à 1500 lignes/minutes.
Comment se fait-il qu'oracle utilise en priorité cet index ?
Y'a-t-il un moyen de forcer l'utilisation de l'index sur l'id, sans modifier la requête, ie sans mettre un hint ?

Merci d'avance

[LeoAnderson]

Quel est l'explain plan ?

que donnes les explains plans avec les hints ?

Quelle est la version ?

Comment sont calculées les stats ? avec des histogrammes ?
sont-elles à jour ?

[deldin]

La version est 8.1.7.2, comme noté dans le titre du sujet.

L'explain plan "normal", pour la requête lente :
1 Every row in the table table_a is read.
2 One or more rows were retrieved using index IX_CODE . The index was scanned in ascending order..
3 Rows from table table_b were accessed using rowid got from a local (single-partition) index.
4 PARTITION RANGE SINGLE
5 For each row retrieved by step 1, the operation in step 4 was performed to find a matching row.
6 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
7 Rows were returned by the SELECT statement.

L'explain plan rapide, avec le hint "/*+ INDEX (table_b IX_ID) */" :
1 Every row in the table table_a is read.
2 One or more rows were retrieved using index IX_ID . The index was scanned in ascending order..
3 Rows from table table_b were accessed using rowid got from a local (single-partition) index.
4 PARTITION RANGE SINGLE
5 For each row retrieved by step 1, the operation in step 4 was performed to find a matching row.
6 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
7 Rows were returned by the SELECT statement.

Les stats sont à jour, générées quotidiennement, tout comme les index. La façon dont sont calculées les stats, ca je n'en sais rien...

[phili_b]

Peut être faut-il un index sur table_b contenant les deux colonnes.

[LeoAnderson]

il manque des éléments à l'explain plan.

quid des colonnes cost ? bytes ? cpu ?

et il est IMPERATIF de savoir comment sont calculées les stats

@phili_b => le tuning n'est pas de la vision ni de l'art divinatoire. Il faut analyser le pourquoi et ensuite, créer les indexes !

[deldin]

Explain plan plus complet pour la requête "lente" :
7 SELECT STATEMENT CHOOSE Cost: 2 Bytes: 70 Cardinality: 1
6 SORT AGGREGATE Bytes: 70 Cardinality: 1
5 NESTED LOOPS Cost: 2 Bytes: 70 Cardinality: 1
1 TABLE ACCESS FULL table_a Cost: 1 Bytes: 138 Cardinality: 3
4 PARTITION RANGE SINGLE Partition #: 4
3 TABLE ACCESS BY LOCAL INDEX ROWID table_b Cost: 1 Bytes: 24 Cardinality: 1 Partition #: 4
2 INDEX RANGE SCAN NON-UNIQUE IX_CODE Cardinality: 1 Partition #: 4

Et pour la requête rapide :
7 SELECT STATEMENT CHOOSE Cost: 2 Bytes: 70 Cardinality: 1
6 SORT AGGREGATE Bytes: 70 Cardinality: 1
5 NESTED LOOPS Cost: 2 Bytes: 70 Cardinality: 1
1 TABLE ACCESS FULL table_a Cost: 1 Bytes: 138 Cardinality: 3
4 PARTITION RANGE SINGLE Partition #: 4
3 TABLE ACCESS BY LOCAL INDEX ROWID table_b Cost: 1 Bytes: 24 Cardinality: 1 Partition #: 4
2 INDEX RANGE SCAN NON-UNIQUE IX_ID Cardinality: 1 Partition #: 4

voici les paramètre utilisés pour le calcul des stats :
estimate_percent : 30
block_sample : null
method_opt : FOR ALL INDEXED COLUMNS SIZE 250
degree : null
granularity : null
cascade : true
stattab : null
statid : null
statown : null
J'espère que ces informations t'aideront LeoAnderson

[LeoAnderson]

ce sont mes yeux ou les explains sont identiques ?

[deldin]

ca doit être tes yeux

j'ai rajouté les cost, bytes, cardinality et type d'opération

[LeoAnderson]

je parle entre les versions "rapides" et "lentes", avec les costs !

[deldin]

oh... effectivement, la seule différence est sur l'index utilisé.
mais niveau coût de requête, c'est à priori le même. c'est bien pour ça que je ne comprends pas pourquoi la requête est plus lente

[phili_b]

@phili_b => le tuning n'est pas de la vision ni de l'art divinatoire. Il faut analyser le pourquoi et ensuite, créer les indexes !

Je vois bien le smiley mais si c'est pour faire passer de l'ironie mordante.

D'autre part pour régler une requête on l'écrit bien et on mets les bons index et ensuite seulement on fait du réglage en finesse avec l'explain plan. J'ai travaillé pendant des années sans explain plan et en optimisant quand même bien mes requêtes.

Il n'y a pas besoin d'explain plan pour suggérer qu'une requête ayant comme critère 2 colonnes ayant 2 index séparés aurait sans doute besoin d'un index composé de ces 2 colonnes. Il va bien falloir qu'on se penche sur ma suggestion vu que les explain plan sont identiques.

ha oui:

edit: L'intérêt de prendre un index composé est d'augmenter son facteur discriminant (avec moins de données susceptibles de se répeter), ce qui sera le cas vu que les index initiaux sont non uniques c'est-à-dire avec des données susceptibles de se répéter.

[LeoAnderson]

oui, avec des explains plan identiques, il peut devenir pertinent de créer un index composite.
mais avant, il me semble important de répondre aux points suivants:

1. Ce qui est très étrange, c'est que même la requête rapide fasse un FULL SCAN.
Et pourquoi le hint est aussi efficace ? (i.e. pourquoi le CBO ne le décide pas de lui même d'utiliser l'index ?)
2. on remarque également que la table est partitionnée. Qu'en est-il des indexes ?
3.par contre, je calculerais les stats en mettant GRANULARITY = 'GLOBAL AND PARTITION' ou 'ALL'
toujours à ce sujet, il faudrait essayer en laissant le nombre d'histogrammes calculés gérés par Oracle ('FOR ALL INDEXED COLUMNS');
4.pourquoi la requête présentée comme "rapide" est celle qui se base sur l'index qui contient le plus de lignes ?
il manque des éléments de répartitions et de volumétrie
5.et une jointure que ne se fait pas sur l'ID, ça ressemble peut-être à un défaut de conception du schéma ?

Après, je ne dis pas qu'un index composite marche (ou ne marchera pas). Je dis simplement qu'avant de créer, il me semble nécessaire de comprendre et cerner les problèmes.