Discussion :

[nax71]

Bonjour,

Voila mon pb.

Je suis sur une base Oracle 9i.

J’ai une requête SQL qui implique 3 tables.
Il n’y a pas des statistiques calculées sur ces 3 tables.
Du a la taille des tables la requête s’exécute en 9 heures.

Si j’ai calcule les statistiques Oracle n’utilise plus les indexes et il fait que des FULL TABLE SCAN ce qui fait que la requête s’exécute dans 16 heures.
Il n’y a pas des hints sur les requêtes.
L’optimiser mode est CHOOSE.

C’est une des rares fois ou les statistiques n’améliorent pas l’exécution d’une requête.

Y a-t-il un moyen d’investiguer (tracer) les décisions du CBO ?

Merci

[orafrance]

Quelle est la requête ? sur quoi portent les indexes ? Comment les stats sont calculées ?

[nax71]

Merci pour la réponse.

Voila les infos:

Code :

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

1
2
3
4
5
6
7
8
SELECT t1.c1,
        t2.c4, 
	    t3.c1
 FROM t1, t3, t2
 WHERE    t2.c1
       || t2.c2
       || t2.c3 = t1.id
  AND t3.c2 = 'TOTO'

La table t3 a 11 lignes.
La table t2 200 000 lignes
La table t1 plusieurs millions.

t1.id => primary key est la concaténation de t2.c1 + t2.c2+t2.c3
Sans statistiques j'utilise l'index correspondant a la primary key.
Avec statistiques je fait un FTS sur t1

Les statistiques sont calcules:

Code :

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

1
2
3
analyze table t1 compute statistiques.  
analyze table t2 compute statistiques. 
analyze table t3 compute statistiques.

J'ai essaye aussi avec

Code :

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

1
2
3
4
5
6
7
8
9
10
begin
 dbms_stats.gather_schema_stats(
    ownname          => 'MY SCHEMA',
    options          => 'GATHER AUTO',
    estimate_percent => dbms_stats.auto_sample_size,
    method_opt       => 'for all columns size auto',
    cascade          => true,
    degree           => 10
)
end

Mais les perfs étaient si mauvaises que j'ai du les enlever complètement et calculer au cas par cas.

[nax71]

Aussi, sa peut aider

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
SQL> select sum(bytes)/1024/1024 as Mo from dba_extents where segment_name= 'T1';
 
        MO
----------
        78
 
SQL> show parameter db_file_multiblock_read_count;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     64
 
 
SQL> show parameter PGA
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 3327131648

Ce que je cherche est de tracer la "reflection" du CBO pour voir a quel moment decide de faire un FTS qui est visiblement la mauvaise decision.

[couak]

à vue de nez je dirais que c'est le concat qui bouffe... tu peux envoyer ton plan d'exécution ?

[orafrance]

la PK c'est la concaténation des 3 colonnes ou les 3 colonnes les unes après les autres ? Parce que je ne savais pas qu'un index de fonction pouvait être une PK

Le problème c'est surtout qu'il manque une jointure en t3 et les autres tables, donc tu te payes un produit cartésien

[nax71]

Voila le plan d'execution

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
-----------------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |    45M|  1859M|       |   479K (46)|
|*  1 |  HASH JOIN                    |                        |    45M|  1859M|    52M|   479K (46)|
|   2 |   NESTED LOOPS                |                        |  1385K|    37M|       |  2835  (32)|
|   3 |    TABLE ACCESS BY INDEX ROWID| T3				       |     1 |    13 |       |     2  (50)|
|*  4 |     INDEX UNIQUE SCAN         | PK_T3				   |     1 |       |       |            |
|   5 |    TABLE ACCESS FULL          | T1				       |  1385K|    19M|       |  2834  (32)|
|   6 |   TABLE ACCESS FULL           | T2                     |    45M|   648M|       |   382K (49)|
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."ID"="T2"."C1"||"T2"."C2"||"T2"."C3")
   4 - access("T3"."C2"='TOTO')

Au fait la table T1.ID est cree a partir d'un script et non pas a travers une fonction. Mais le script cree la table effectivement an concatenant les C1,C2,C3 de T2.


couak:
J’ai soupçonné le concat aussi mais le pb du FTS viens clairement de la présence ou non des statistiques. Je répète les stats déterminent le FTS. Sans stats le passage se fait bien par l’index.

[LeoAnderson]

Comment sont calculées les stats ?

pour avoir des infos sur le fonctionnement du CBO, activer la trace event 10053 : http://wiki.oracle.com/page/HOW+TO+t...event+10053%29

[orafrance]

C'est forcément pas la requête que tu as donnée

Quel est le pourcentage de ligne de t1 est ramené ?

[sgora]

l
Le problème c'est surtout qu'il manque une jointure en t3 et les autres tables, donc tu te payes un produit cartésien

En plus, il vaut mieux analyser avec dbms_stats à partit de la 9i, mais ne pas mettre n'importe quelles options :

begin
dbms_stats.gather_schema_stats(
ownname => 'MY SCHEMA',
options => 'GATHER',
estimate_percent => 10,
method_opt => 'for all columns size 1',
cascade => true,
degree => 4
);
end; me paraît mieux.

Ensuite, vu que tu n'as pas de filtres sur t1 et t2 dans ta clause where, il me semble qu'un full scan + hash join entre t1 et t2 doit être une bien meilleure solution que la passage par les index. Ce qui te plombe, c'est le nested loops que tu as sur la table t3 avec le join t1 t2, tu fais n fois la jointure au lieu de la faire une fois.

[orafrance]

Ensuite, vu que tu n'as pas de filtres sur t1 et t2 dans ta clause where, il me semble qu'un full scan + hash join entre t1 et t2 doit être une bien meilleure solution que la passage par les index.

Pas si la concaténation des colonnes de t2 permets de limiter le nombre de lignes extraites de t1... dans ce cas, il devrait utiliser l'index sur t1.id

[nax71]

sgora: j'ai utilisé dbms_stats si tu regarde plus haut dans le post mais mahlereusement le resultat est decevant en temre de perfs.

La requette est generee par un outil Informatica.

Ce qui me trouble est que dans toute la documentation lue les statistiques sont censés d’améliorer les performances et de faire que CBO prends les bonnes décisions.
Or sur cette base le résultat est contraire.
Je voulais avoir une explication ou un moyen d’investigation.

Quand je suis arrivé (il y a 3 mois) le DBA ma conseillé de ne pas calculer les stats sur cette base. Ca me gêne de ne pas comprendre.

[orafrance]

Ce qui me trouble est que dans toute la documentation lue les statistiques sont censés d’améliorer les performances et de faire que CBO prends les bonnes décisions.

C'est pas une science exacte sinon on se triturerait pas l'esprit en optimisation

Quand je suis arrivé (il y a 3 mois) le DBA ma conseillé de ne pas calculer les stats sur cette base. Ca me gêne de ne pas comprendre.

Si l'application a été développée pour tenir compte des régles (RBO) alors ça peut être catastrophique d'utiliser le CBO, ça peut être pourquoi le DBA t'as donné une telle recommandation.

Ce serait pas mal que tu répondes aux questions sinon : Quel est le pourcentage de ligne de t1 est ramené ?

[nax71]

Quel est le pourcentage de ligne de t1 est ramené ?

Comment je peux trouver ca ? Sans executer la requette, si non la reponse demain

Ca peut etre une piste. La base vien de d'avant la version 8. avec des migration succesives.

[orafrance]

Code :

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

1
2
3
4
5
SELECT count(1)
 FROM t1
 WHERE  t1.id in (SELECT t2.c1
       || t2.c2
       || t2.c3 FROM t2)

et

Code :

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

1
2
SELECT count(1)
 FROM t1

[Waldar]

Au vu de la requête, c'est normal qu'il y ait un FTS sur t1 et t2 puisqu'il n'y a aucun filtre sur aucune de ces deux tables.

Par contre je ne comprends pas pourquoi c'est plus long, mais "normalement", parcourir un index + table par rowid sur toute la table c'est plus long que de parcourir toute la table en FTS.

Vous avez exécuté les deux requêtes dans les mêmes conditions ?

[sgora]

Pas si la concaténation des colonnes de t2 permets de limiter le nombre de lignes extraites de t1... dans ce cas, il devrait utiliser l'index sur t1.id

Pas d'accord, la concaténation de t2 forme la condition de jointure avec t1, elle n'élimine aucune ligne; à l'inverse d'une condition x = :b1.

Si on a 200000 lignes dans t2 et plusieurs millions dans t1, la cardinalité risque donc d'être énorme, comme l'indique d'ailleurs l'explain plan fourni.

Donc aïe aïe aïe le passage par les index....

[nax71]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT count(1)
 FROM t1
 WHERE  t1.id IN (SELECT t2.c1
       || t2.c2
       || t2.c3 FROM t2)
 
1216634
 
select coutn(*) from t2 
45326447
 
SELECT count(1) FROM t1
1385878

Ceci est le resultat sur une base de test (plus petite). Sur la base de prod ca aurait pris plus de temps.
Par contre les plans d'executions sont les memes sur les 2 bases ainsi que le parametrage.

Donc aïe aïe aïe le passage par les index....

Tu peux developer stp. Tu pense a quoi ?


Es-t-il possible que l’augmentation de la taille d’une table détermine le CBO de faire un FTS vu que db_file_multiblock_read_count est à 64 (avec un block de 8k) donc 0,5M dans un seul accès disque.
Si les statistiques ne sont pas calculées il passe par l’index (il ne connait pas la taille de la table).
Si les statistiques sont calculées il peut estimer la taille de la table donc il arrive à la conclusion que lire la table (elle n’est pas enrome en terme de taille) c’est moins cher que le passage par l’index.

Ca semble logique ?

[orafrance]

Pas d'accord, la concaténation de t2 forme la condition de jointure avec t1, elle n'élimine aucune ligne; à l'inverse d'une condition x = :b1.

Si on a 200000 lignes dans t2 et plusieurs millions dans t1, la cardinalité risque donc d'être énorme, comme l'indique d'ailleurs l'explain plan fourni.

Donc aïe aïe aïe le passage par les index....

sauf si ça limite suffisamment le nombre de lignes de t1 Si la concaténation des colonnes de t2 limite l'échantillon de ligne de t1 alors l'index est intéressant.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT count(1)
 FROM t1
 WHERE  t1.id IN (SELECT t2.c1
       || t2.c2
       || t2.c3 FROM t2)
 
1216634
 
select coutn(*) from t2 
45326447
 
SELECT count(1) FROM t1
1385878

Bah voila, tu raménes plus de 87% des lignes de t1, alors pourquoi passer par un index ? Ce qu'il faudrait c'est une jointure avec t3 pour réduire encore le nombre de ligne ramenée.

Pourquoi t'as t3 qui traine tout seul ?

[orafrance]

Si les statistiques ne sont pas calculées il passe par l’index (il ne connait pas la taille de la table).

Oui, parce qu'il utilise le RBO, et les régles imposent d'utiliser les index quand c'est possible. Quand tu calcules les stats, le CBO estime que la sélection de 87% des lignes ne vaut pas la peine de passer par la table ET l'index... la table suffit.

Si les statistiques sont calculées il peut estimer la taille de la table donc il arrive à la conclusion que lire la table (elle n’est pas enrome en terme de taille) c’est moins cher que le passage par l’index.

C'est bien ça

Ca ressemble plus à un problème de modélisation qu'autre chose