Discussion :

[rv78]

Bonjour,
Nous venons de passer depuis peu d'oracle 8i à oracle 10g. La base de données que je gère semble fonctionner sans aucun problème, si ce n'est que je viens de trouver une requête (fusion d'une 20aine de tables sur 1 colonne indexée dans chaque table) qui met un temps anormalement long à s'executer (1mn06 au lieu de 1.5 sec sur un vieux serveur en 8i).
Les autres requêtes que j'ai pu tester s'execute bien plus rapidement ce qui parait logique, puisque le serveur O10g est plus puissant.

Avez vous une idée de ce qui peu clocher dans les paramètres O10g ?
Je ne peu pas rester avec ce pb car il provoque des timeout (erreurs ORA-01013).
Merci pour votre aide.
Hervé

[orafrance]

les stats sont OK ?

[rv78]

Quelles genre de stats ?
Et comments est-ce que je peux les mettre en place ?
Merci
RV

[rv78]

Je viens de regarder dans les consommation de ressources des instructions. un paramètre parait très important pour cette requête en comparaison des autres requêtes :
- obtentions de tampon par execution = 3136.0 (contre 4.0 à 10 pour des requêtes simples)
- obtention de tampon par ligne = même valeurs que les précédentes.
- lecture sur disque = 30 (contre 0 pour les autres)
- lecture sur disque par execution = même valeurs que lesprécédentes
- temps UC bien supérieur aux autres requêtes.

Si ca peut aider à comprendre.
Serait ce une histoire de tampon ?
Merci
RV

[Fabien Celaia]

Pour les stats

Code :

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

1
2
3
begin
dbms_stats.gather_schema_stats(ownname=> 'VotreSchema' , cascade=> TRUE);
end;

[bouyao]

Il peut y avoir plusieurs choses.

Certains requête sont plus rapide en 8i qu'en 10g et inversement.
Par exemple not in dans une clause est plus rapide en 8i qu'en 10g.

[rv78]

->fadace :
la commande est passée. Où dois je aller pour voir les stats, et quelles sont celles qui sont importantes à regarder dans cette situation ?

->bouyao :
la requette ressemble à ca :
select <liste col>
from table1 t1, table2 t2, table3 t3,...
where t1.id='val'
and t1.id=t2.id and t2.date=(select MAX(date) from table2 where id=t1.id)
and t1.id=t3.id and t3.date=(select MAX(date) from table3 where id=t1.id)
...

Merci pour votre aide.
RV

[plabrevo]

Apres la mise a jour des statistiques, omission classique sous 10g, si le probleme persiste, essaye d'obtenir l'explain plan, avec autotrace (le plus simple), et regarde du cote de la pertinence des indexes desormais utilises sous 10g:

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
26
27
28
29
30
31
32
33
34
 
SQL> SET AUTOTRACE ON
SQL> SELECT 'x'
  2  FROM   sys.dual
  3  /
 
'
-
x
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1)
   1    0   FAST DUAL (Cost=2 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        324  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

Pour une aide supplementaire et efficace sur ce forum, poster imperativement les indexes sur les tables[1-3], leurs selectivites et les volumes.

[rv78]

Voici ce que j'obtiens avec l'autotrace :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15 Card=1 Bytes=500)
 
   1    0   FILTER
   2    1     NESTED LOOPS (OUTER) (Cost=15 Card=1 Bytes=500)
   3    2       NESTED LOOPS (OUTER) (Cost=14 Card=1 Bytes=477)
   4    3         NESTED LOOPS (OUTER) (Cost=13 Card=1 Bytes=454)
   5    4           NESTED LOOPS (OUTER) (Cost=12 Card=1 Bytes=431)
   6    5             NESTED LOOPS (OUTER) (Cost=11 Card=1 Bytes=408)
   7    6               NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=385)
   8    7                 NESTED LOOPS (OUTER) (Cost=9 Card=1 Bytes=362)
   9    8                   NESTED LOOPS (OUTER) (Cost=7 Card=1 Bytes=339)
  10    9                     NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=316)
  11   10                       NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=293)
  12   11                         NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=270)
  13   12                           NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=245)
  14   13                             NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=220)
  15   14                               NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=195)
  16   15                                 HASH JOIN (OUTER) (Cost=4 Card=1 Bytes=170)
  17   16                                   NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=145)
  18   17                                     NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=120)
  19   18                                       NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=95)
  20   19                                         NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=70)
  21   20                                           NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=45)
  22   21                                             TABLE ACCESS (BY INDEX ROWID) OF 'ANIMAL' (TABLE) (Cost=2 Card=1 Bytes=22)
  23   22                                               INDEX (UNIQUE SCAN) OF 'PK_ANIMAL' (INDEX (UNIQUE)) (Cost=1 Card=1)
  24   21                                             TABLE ACCESS (BY INDEX ROWID) OF 'ANIM_ETAT' (TABLE) (Cost=1 Card=1 Bytes=23)
  25   24                                               INDEX (UNIQUE SCAN) OF 'PK_ANIM_ETAT' (INDEX (UNIQUE)) (Cost=0 Card=1)
  26   20                                           TABLE ACCESS (BY INDEX ROWID) OF 'PRET' (TABLE)
  27   26                                             INDEX (RANGE SCAN) OF 'PART EN_FK' (INDEX)
  28   19                                         INDEX (RANGE SCAN) OF 'PK_CYCLICITE' (INDEX (UNIQUE))
  29   18                                       TABLE ACCESS (BY INDEX ROWID) OF 'COLL_EMBR' (TABLE)
  30   29                                         INDEX (RANGE SCAN) OF 'CE_IPG_IDX' (INDEX)
  31   17                                     TABLE ACCESS (BY INDEX ROWID) OF 'TRAIT_HORM' (TABLE)
  32   31                                       INDEX (RANGE SCAN) OF 'RECOIT_1_FK' (INDEX)
  33   16                                   TABLE ACCESS (BY INDEX ROWID) OF 'PAIL_SPERME' (TABLE)
  34   33                                     INDEX (SKIP SCAN) OF 'DONNEE_PAR_FK' (INDEX)
  35   15                                 INDEX (RANGE SCAN) OF 'PK_POINT_AD' (INDEX (UNIQUE))
  36   14                               INDEX (RANGE SCAN) OF 'PK_POINT_PH' (INDEX (UNIQUE))
  37   13                             INDEX (RANGE SCAN) OF 'PK_POINT_NO' (INDEX (UNIQUE))
  38   12                           TABLE ACCESS (BY INDEX ROWID) OF 'MENSURATION' (TABLE)
  39   38                             INDEX (RANGE SCAN) OF 'EST_2_FK' (INDEX)
  40   11                         INDEX (RANGE SCAN) OF 'PK_ALLOT_EXPE' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  41   10                       INDEX (RANGE SCAN) OF 'PK_POINT_ALL' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  42    9                     INDEX (RANGE SCAN) OF 'PK_PV' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  43    8                   TABLE ACCESS (BY INDEX ROWID) OF 'MISE_BAS' (TABLE) (Cost=2 Card=1 Bytes=23)
  44   43                     INDEX (RANGE SCAN) OF 'PK_MISE_BAS' (INDEX (UNIQUE)) (Cost=1 Card=1)
  45    7                 INDEX (RANGE SCAN) OF 'PK_POINT_CUL' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  46    6               INDEX (RANGE SCAN) OF 'PK_ETAT_ENGRAIS' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  47    5             INDEX (RANGE SCAN) OF 'PK_ALLOTEMENT' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  48    4           INDEX (RANGE SCAN) OF 'PK_SAILLIES' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  49    3         INDEX (RANGE SCAN) OF 'PK_DG' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  50    2       INDEX (RANGE SCAN) OF 'PK_OESTRUS' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
  51    1     SORT (AGGREGATE)
  52   51       TABLE ACCESS (BY INDEX ROWID) OF 'PRET' (TABLE) (Cost=1 Card=1 Bytes=25)
 
  53   52         INDEX (RANGE SCAN) OF 'PART EN_FK' (INDEX) (Cost=1 Card=1)
 
  54    1     SORT (AGGREGATE)
  55   54       FIRST ROW (Cost=2 Card=3 Bytes=69)
  56   55         INDEX (RANGE SCAN (MIN/MAX)) OF 'PK_ALLOTEMENT' (INDEX (UNIQUE)) (Cost=2 Card=3 Bytes=69)
 
  57    1     SORT (AGGREGATE)
  58   57       INDEX (RANGE SCAN) OF 'PK_ALLOT_EXPE' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=23)
 
  59    1     SORT (AGGREGATE)
  60   59       FIRST ROW (Cost=2 Card=8 Bytes=184)
  61   60         INDEX (RANGE SCAN (MIN/MAX)) OF 'PK_OESTRUS' (INDEX (UNIQUE)) (Cost=2 Card=8 Bytes=184)
 
  62    1     SORT (AGGREGATE)
  63   62       TABLE ACCESS (BY INDEX ROWID) OF 'CYCLICITE' (TABLE) (Cost=1 Card=1 Bytes=25)
 
  64   63         INDEX (RANGE SCAN) OF 'VIENT_FK' (INDEX) (Cost=1 Card=1)
 
  65    1     SORT (AGGREGATE)
  66   65       INDEX (RANGE SCAN) OF 'PK_SAILLIES' (INDEX (UNIQUE)) (Cost=2 Card=3 Bytes=69)
 
  67    1     SORT (AGGREGATE)
  68   67       TABLE ACCESS (BY INDEX ROWID) OF 'COLL_EMBR' (TABLE)
  69   68         INDEX (RANGE SCAN) OF 'ENGENDRE_FK' (INDEX)
  70    1     SORT (AGGREGATE)
  71   70       INDEX (RANGE SCAN) OF 'PK_DG' (INDEX (UNIQUE)) (Cost=2 Card=5 Bytes=115)
 
  72    1     SORT (AGGREGATE)
  73   72       INDEX (RANGE SCAN) OF 'PK_PV' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=23)
 
  74    1     SORT (AGGREGATE)
  75   74       TABLE ACCESS (BY INDEX ROWID) OF 'MISE_BAS' (TABLE) (Cost=3 Card=1 Bytes=23)
 
  76   75         INDEX (RANGE SCAN) OF 'MB_IPG_IDX' (INDEX) (Cost=1 Card=1)
 
  77    1     SORT (AGGREGATE)
  78   77       TABLE ACCESS (BY INDEX ROWID) OF 'TRAIT_HORM' (TABLE) (Cost=1 Card=1 Bytes=25)
 
  79   78         INDEX (RANGE SCAN) OF 'RECOIT_1_FK' (INDEX) (Cost=1 Card=1)
 
  80    1     SORT (AGGREGATE)
  81   80       TABLE ACCESS (BY INDEX ROWID) OF 'PAIL_SPERME' (TABLE)
  82   81         INDEX (SKIP SCAN) OF 'DONNEE_PAR_FK' (INDEX)
  83    1     SORT (AGGREGATE)
  84   83       INDEX (FAST FULL SCAN) OF 'PK_POINT_ALL' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=23)
 
  85    1     SORT (AGGREGATE)
  86   85       INDEX (FAST FULL SCAN) OF 'PK_POINT_CUL' (INDEX (UNIQUE)) (Cost=2 Card=2 Bytes=46)
 
  87    1     SORT (AGGREGATE)
  88   87       TABLE ACCESS (BY INDEX ROWID) OF 'POINT_AD' (TABLE) (Cost=1 Card=1 Bytes=25)
 
  89   88         INDEX (RANGE SCAN) OF 'POINTE_3_FK' (INDEX) (Cost=1 Card=1)
 
  90    1     SORT (AGGREGATE)
  91   90       TABLE ACCESS (BY INDEX ROWID) OF 'POINT_PH' (TABLE) (Cost=1 Card=1 Bytes=25)
 
  92   91         INDEX (RANGE SCAN) OF 'POINTE_4_FK' (INDEX) (Cost=1 Card=1)
 
  93    1     SORT (AGGREGATE)
  94   93       TABLE ACCESS (BY INDEX ROWID) OF 'POINT_NO' (TABLE) (Cost=1 Card=1 Bytes=25)
 
  95   94         INDEX (RANGE SCAN) OF 'POINTE_5_FK' (INDEX) (Cost=1 Card=1)
 
  96    1     SORT (AGGREGATE)
  97   96       INDEX (RANGE SCAN) OF 'PK_ETAT_ENGRAIS' (INDEX (UNIQUE)) (Cost=2 Card=3 Bytes=69)
 
  98    1     SORT (AGGREGATE)
  99   98       INDEX (RANGE SCAN) OF 'PK_MENSURATION' (INDEX (UNIQUE))
 
 
Statistics
----------------------------------------------------------
      12236  recursive calls
          0  db block gets
       3586  consistent gets
         50  physical reads
          0  redo size
   13493907  bytes sent via SQL*Net to client
       1697  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
        116  sorts (memory)
          0  sorts (disk)
          1  rows processed

Comment interprétez vous ces résultats ?
Merci
RV

[rv78]

Sous oracle 8i, pas de plan d'execution mais les statistiques :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Statistics
----------------------------------------------------------
       1864  recursive calls
         11  db block gets
        745  consistent gets
         41  physical reads
          0  redo size
       1607  bytes sent via SQL*Net to client
       3675  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         28  sorts (memory)
          0  sorts (disk)
          1  rows processed

Une chose me surprend entre les 2 stats, c'est le nombre de bytes renvoyés au client : 13Mo sur le serveur 10g !!!

[orafrance]

comment se fait-il qu'il y a un tel écart dans la quantité de données renvoyée

10g 13493907 bytes
8i 1607 bytes

T'es spur qu'il y a autant de lignes ?

[rv78]

Bah oui, ca m'étonne aussi...
ces données renvoyées correspondent-elles uniquement au résultat de la requête ou elles prennent en compte l'autotrace ? parceque je n'ai pas eu l'execution plan sous 8i (enfin, ce n'est que qq ko cet execution plan...).
En tout cas la requête me renvoie la même chose au niveau des données (mêmes tables, mêmes index et quasi mêmes données), donc 1 ligne renvoyée !

ouala, ce que je peux dire
RV

[orafrance]

ha oui bien sûr, c'est le plan d'exécution qui raméne plus de données

Crée la table PLAN_TABLE sur ta base 8i

[rv78]

Eh bien non... finalement, la qté de données ramenée sur 8i avec ou sans le plan d'execution est la même :

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
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=784 Bytes=338688)
 
   1    0   FILTER
(...)
 
Statistics
----------------------------------------------------------
          8  recursive calls
         41  db block gets
        151  consistent gets
        152  physical reads
          0  redo size
       1606  bytes sent via SQL*Net to client
       1653  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          1  rows processed

:
RV

[orafrance]

étrange... j'avoue que ça me laisse perplexe

[yvesguep]

Le plan d'execution sur 8i et 10g est t'il identique?
Je note déja que le paramétre de l'optimizer n'est pas le même:
10g --> ALL_ROWS et 8i --> CHOSE peut être faudrait t'il employer des HINTS.

c a voir

[plabrevo]

Il y a un bug sur 'autotrace on' concernant les statistiques sur les premieres 10g. Il serait peut-etre judicieux de recuperer une trace classique et de la mouliner avec tkprof, ou bien se contenter d'un "set autotrace on explain", pour verifier si les informations affichees sont du meme ordre de magnitude.

[rv78]

D'une manière générale, les 'cost' et les bytes sont plus importants sur le plan d'execution de 10g que 8i (par exemple, ne regarder que la ligne 0 mentionnée dans le code des msg précédents).
Aussi, sur la 10g, les 'Card' sont tous à 1 (ou presque) alors qu'ils peuvent prendre des valeurs plus importantes sur la 8i.

Ne sachant pas interpréter ces valeurs, avez vous un article (Fr si possible, car moins de confusion de terme) qui me permettrait de le faire ?
Merci pour votre aide.

Une autre question, mentionnée par yvesguep, est le paramètre de l'optimizer : quelle est la différence entre CHOSE et ALL_ROWS ?

RV

[orafrance]

CHOOSE permet de choisir entre les modes RULE et COST. Le premier est basé sur des régles strictes alors que le deuxiéme tiens compte des statistiques. En 10g, le mode par défaut est ALL_ROWS (=COST). Le mode RULE n'existe plus.

[yvesguep]

Peut tu poster la totalité du plan d'execution des 2 bases.
Il me semble qu'il manque une partie des informations sur la 10g.