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.

[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.