optimisation requête Oracle 10

**LeoAnderson** · 27/12/2007, 15h12

non, vous prenez le problème par le mauvais bout !

1. On ne cherche pas à optimiser avec des hints ou autres si on n'a pas de sérieux problèmes de performances. (ce qui ne veut pas dire qu'on doit écrire le schéma et les requêtes avec les pieds, mais pas de tuning "poussé" à tout bout de champs sur chaque requête).
2. Le CBO est le moteur Oracle qui cherche, en fonction différents éléments, le meilleur moyen d'exécuter la requête (parcours de telle ou telle table en premier, jointure comme ci ou comme ça, ...). Les hints forcent le CBO à passer par telle ou telle "route"
3. Dans votre cas, s'il exécute 90000 fois la fonction, c'est que le CBO a calculé que c'était le chemin le plus rapide. Pas le plus logique. Le plus rapide.

Si vous avez un doute, et si vous avez la licence pour l'utiliser, le SQL Tuning advisor pourrait vous aider (c'est comme le CBO mais à la différence de celui-ci, il a tout le temps nécessaire).
Le CBO doit fournir le moins mauvais chemin dans le meilleurs temps. Parfois, il n'a donc pas le temps de déterminer le chemin idéal.

**Michel SALAIS** · 27/12/2007, 15h46

Envoyé par LeoAnderson

Le CBO doit fournir le moins mauvais chemin dans le meilleurs temps. Parfois, il n'a donc pas le temps de déterminer le chemin idéal.

Tu as parfaitement rasion mais malheureusement, il se trompe aussi des fois non pas à cause du temps mais à cause de manque d'information indisponible ou impossible à fournir !

Effectivement, l'optimisation ne doit pas toucher toutes les requêtes. Elle est nécessaire si le temps de réponse n'est pas acceptable ...

**LeoAnderson** · 27/12/2007, 16h07

bien sûr, mais si on n'a pas les infos (i.e. les stats) le SQL Tuning ne fera pas mieux que le CBO car lui aussi se basera sur des données fausses...

C'est pour ça que je ne parlais que du temps d'analyse de la requête

**Michel SALAIS** · 27/12/2007, 16h12

Envoyé par zorino

je ne pense que du bien de tes propositions

Mais bon, celle-là, faut être franc, je n'y comprends rien !
Je suis quand même le débutant de base avec Oracle alors faut être patient...
Mais de toutes façons, pour ce cas, s'il faut toucher au paramétrage d'Oracle, tu peux laisser tomber parce que je n'y ai pas accès chez le client.

Je ne touche pas non plus au prog, il faut juste que la requête booste et c'est tout !

Me voilà flatté ...
Mais passons aux choses sérieuses ...
En fait, avant 9i, l'optimiseur Oracle ne prend en considération que l'entrée/sorite et ignore totalement le temps cpu.

A partir de 9i, le calcul de statistiques système permet à l'optimiseur de prendre le cpu aussi en considération.

La collecte de statostoques système est une opération qui se fait une fois et n'a pas à être répétée si le matériel n'est pas miodifié ou si la charge n'est pas trop modifiée

**pifor** · 27/12/2007, 20h34

Envoyé par zorino

Comment tournerais-tu cette requête pour utiliser ce hint NO_MERGE ?

Pour utiliser le hint NO_MERGE par rapport à l'exemple de votre premier message, voici un exemple d'utilisation avec Oracle 10.2.0.1.
Cet exemple utilise le champ CLIENT_INFO du contexte de la session pour compter les appels de fonction.

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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL> 
SQL> alter session set nls_date_format='DD/MM/YYYY';
 
Session modifiee.
 
SQL> 
SQL> drop table t2;
 
Table supprimee.
 
SQL> drop table t1;
 
Table supprimee.
 
SQL> 
SQL> create table t1
  2  as select level x,  to_char(rownum) c, level*2 x2, level*3 x3,
  3     case when level  <= 1600 then to_date('01/01/2007')
  4          else to_date('01/01/2006') end d1,
  5     case when level <= 1600 then to_date('01/01/2007')
  6          else to_date('01/01/2006') end d2
  7     from dual connect by level <= 150000;
 
Table creee.
 
SQL> 
SQL> alter table t1 add primary key(x);
 
Table modifiee.
 
SQL> 
SQL> create table t2 as select * from t1 where 1=0;
 
Table creee.
 
SQL> alter table t2 add primary key(x);
 
Table modifiee.
 
SQL> 
SQL> select count(*) from
  2  (
  3  select *
  4  from   t1, t2
  5  where  t1.x = t2.x(+)
  6  and    nvl(t2.d2, t1.d1) >= '01/01/2007'
  7  and    nvl(t2.d2, t1.d1) <  '01/02/2007'
  8  );
 
  COUNT(*)
----------
      1600
 
SQL> 
SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname => 'O', tabname=> 'T1', cascade => TRUE);
 
Procedure PL/SQL terminee avec succes.
 
SQL> exec dbms_stats.gather_table_stats(ownname => 'O', tabname=> 'T2', cascade => TRUE);
 
Procedure PL/SQL terminee avec succes.
 
SQL> 
SQL> create or replace function f(p number) return number
  2  as
  3  begin
  4     dbms_application_info.set_client_info( userenv('client_info')+1 );
  5     if  (p <=1600 )
  6     then
  7        return 0;
  8     else
  9        return 1;
 10     end if;
 11  end;
 12  /
 
Fonction creee.
 
SQL> 
SQL> 
SQL> exec dbms_application_info.set_client_info(0);
 
Procedure PL/SQL terminee avec succes.
 
SQL> select count(*) from
  2  (
  3  select *
  4  from   t1, t2
  5  where  t1.x = t2.x(+)
  6  and    nvl(t2.d2, t1.d1) >= '01/01/2007'
  7  and    nvl(t2.d2, t1.d1) <  '01/02/2007'
  8  and    f(t1.x) = 0
  9  );
 
  COUNT(*)
----------
      1600
 
SQL> select userenv('client_info' ) from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
150000
 
SQL> 
SQL> exec dbms_application_info.set_client_info(0);
 
Procedure PL/SQL terminee avec succes.
 
SQL> select count(*) from
  2  (
  3   select /*+ materialize */ t1.x x1, t2.x x2
  4   from      t1, t2
  5   where     t1.x = t2.x(+)
  6   and       nvl(t2.d2, t1.d1) >= '01/01/2007'
  7   and       nvl(t2.d2, t1.d1) <  '01/02/2007'
  8  ) t3
  9  where  f(t3.x1) = 0
 10  ;
 
  COUNT(*)
----------
      1600
 
SQL> select userenv('client_info' ) from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
150000
 
SQL> 
SQL> exec dbms_application_info.set_client_info(0);
 
Procedure PL/SQL terminee avec succes.
 
SQL> select count(*) from
  2  (
  3   select /*+ no_merge */ t1.x x1, t2.x x2
  4   from      t1, t2
  5   where     t1.x = t2.x(+)
  6   and       nvl(t2.d2, t1.d1) >= '01/01/2007'
  7   and       nvl(t2.d2, t1.d1) <  '01/02/2007'
  8  ) t3
  9  where  f(t3.x1) = 0
 10  ;
 
  COUNT(*)
----------
      1600
 
SQL> select userenv('client_info' ) from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1600

Dans ma configuration le hint MATERIALIZE ne fonctionne pas alors que NO_MERGE fonctionne.

**orafrance** · 27/12/2007, 20h39

Envoyé par Michel SALAIS

La collecte de statostoques système est une opération qui se fait une fois et n'a pas à être répétée si le matériel n'est pas miodifié ou si la charge n'est pas trop modifiée

un bémol quand même. Si la base fait du batch la nuit et du temps réel la journée il peut être intéressant de calculer les stats durant chacune des périodes. En effet, l'activité peut changer de manière significative la collecte effectuée par Oracle.

Il faut aussi préciser que les stats systémes ne sont pas très bien utilisé en 9i, en 10g des stats supplémentaires permettent au CBO d'être plus efficace. D'ailleurs, j'en profite pour rappeler que des stats sur SYS peuvent être intéressantes aussi en 9i contrairement à ce qui se dit.

Sont ce mes yeux où nous n'avons pas eu de réponses à mes questions notamment les num_rows des objets ?

**pifor** · 27/12/2007, 20h40

Envoyé par zorino

merci pour ton aide, ça a effectivement l'air de correspondre à ce que je cherche, je vais fouiner de ce côté...
Mais, du coup, je me demande quel est le moyen "recommandé" de tourner une requête de ce genre !

Par exemple, admettons que je veuille appliquer une fonction assez coûteuse en temps seulement sur les filles.
La requête de base serait

Code :

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

1
2
3
4
5
 
select * 
from   table
where  sexe='F'
and   fonction(table.cle) = 'OK'

C'est ce que je faisais, mais je me suis aperçu que la fonction n'était pas exécutée QUE sur les filles. Je voudrais donc qu'il y ait une préséance de la clause "WHERE SEXE = 'F'" sur celle AND FONCTION(CLE) = 'OK'

Comment tournerais-tu cette requête pour utiliser ce hint NO_MERGE ?

Dans ma configuration, il n'y a pas besoin de hint ...

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
SQL> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                
PL/SQL Release 10.2.0.1.0 - Production                                          
CORE    10.2.0.1.0      Production                                                      
TNS for Linux: Version 10.2.0.1.0 - Production                                  
NLSRTL Version 10.2.0.1.0 - Production                                          
 
SQL> 
SQL> drop table t;
 
Table supprimee.
 
SQL> 
SQL> create table t
  2  as select level x,  to_char(rownum) c, level*2 x2, level*3 x3,
  3     case when(mod(level,15)) = 0 then 'F' else 'M' end s
  4     from dual connect by level <= 150000;
 
Table creee.
 
SQL> 
SQL> select count(*) from t;
 
  COUNT(*)                                                                      
----------                                                                      
    150000                                                                      
 
SQL> select count(*) from t where s ='F';
 
  COUNT(*)                                                                      
----------                                                                      
     10000                                                                      
 
SQL> 
SQL> alter table t add primary key(x);
 
Table modifiee.
 
SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname => 'O', tabname=> 'T', cascade => TRUE);
 
Procedure PL/SQL terminee avec succes.
 
SQL> 
SQL> create or replace function f(p number) return varchar2
  2  as
  3  begin
  4     dbms_application_info.set_client_info( userenv('client_info')+1 );
  5     if (mod(p, 15)) = 0
  6     then
  7        return 'OK';
  8     else
  9        return 'KO';
 10     end if;
 11  end;
 12  /
 
Fonction creee.
 
SQL> 
SQL> exec dbms_application_info.set_client_info(0);
 
Procedure PL/SQL terminee avec succes.
 
SQL> 
SQL> select count(*) from
  2  ( select *
  3  from  t
  4  where
  5  s ='F'
  6  and f(t.x) = 'OK');
 
  COUNT(*)                                                                      
----------                                                                      
     10000                                                                      
 
SQL> select userenv('client_info' ) from dual;
 
USERENV('CLIENT_INFO')                                                          
----------------------------------------------------------------                
10000                                                                           
 
SQL> 
SQL> exec dbms_application_info.set_client_info(0);
 
Procedure PL/SQL terminee avec succes.
 
SQL> select count(*) from
  2  (
  3  select * from
  4  (select /*+ no_merge */ * from t where s = 'F') t1
  5  where f(t1.x) = 'OK'
  6  );
 
  COUNT(*)                                                                      
----------                                                                      
     10000                                                                      
 
SQL> select userenv('client_info' ) from dual;
 
USERENV('CLIENT_INFO')                                                          
----------------------------------------------------------------                
10000                                                                           
 
SQL> 
SQL> exit

**pifor** · 27/12/2007, 20h43

Envoyé par orafrance

Sont ce mes yeux où nous n'avons pas eu de réponses à mes questions notamment les num_rows des objets ?

Ce sont tes yeux

Table1 contient 150 000 enregistrements, Table2 est vide

**orafrance** · 27/12/2007, 20h46

Ca j'ai bien vu, mais je voudrais savoir si ce sont les résultats d'un COUNT ou de NUM_ROWS ce qui a toute son importance dans le choix du plan d'exécution

**Michel SALAIS** · 27/12/2007, 21h04

Envoyé par orafrance

un bémol quand même. Si la base fait du batch la nuit et du temps réel la journée il peut être intéressant de calculer les stats durant chacune des périodes. En effet, l'activité peut changer de manière significative la collecte effectuée par Oracle.

Tu as raison sauf que je ne qualifierai pas ça comme un bémol ...

Effectivement, ceci peut être considéré l'un des cas où la charge change considérablement. Il faut alors collecter les stats système disons pour la journée et pour la nuit. Puis trouver un moyen permettant de les basculer pour chaque période.

**LeoAnderson** · 27/12/2007, 21h51

La question était "pourquoi la fonction est appellée 90 000 fois".
On a apporté la réponse.
Etant donné qu'il n'y a pas de problèmes de performances, pour moi, le sujet est résolu.

zorino ==> tu confirmes ?

**Michel SALAIS** · 27/12/2007, 23h17

Envoyé par pifor

Pour utiliser le hint NO_MERGE par rapport à l'exemple de votre premier message, voici un exemple d'utilisation avec Oracle 10.2.0.1.
Cet exemple utilise le champ CLIENT_INFO du contexte de la session pour compter les appels de fonction.

Dans ma configuration le hint MATERIALIZE ne fonctionne pas alors que NO_MERGE fonctionne.

J'ai repris ton exemple et J'ai constaté que materialize sera uniquement efficace si nous utilisons la syntaxe "with"

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
 
exec dbms_application_info.set_client_info(0);
 
anonymous block completed
0ms elapsed
with t3 as
(
  SELECT /*+ materialize */ t1.x x1, t2.x x2
  FROM   t1, t2
  WHERE  t1.x = t2.x(+)
  AND    nvl(t2.d2, t1.d1) >= '01/01/2007'
  AND    nvl(t2.d2, t1.d1) <  '01/02/2007'
)
SELECT count(*) FROM t3
WHERE  f(t3.x1) = 0
 
COUNT(*)               
---------------------- 
1600                   
 
1 rows selected
 
157ms elapsed
SELECT userenv('client_info' ) FROM dual
 
USERENV('CLIENT_INFO')                                           
---------------------------------------------------------------- 
1600                                                             
 
1 rows selected
 
15ms elapsed
 
exec dbms_application_info.set_client_info(0);
 
anonymous block completed
0ms elapsed
with t3 as
(
  SELECT t1.x x1, t2.x x2
  FROM   t1, t2
  WHERE  t1.x = t2.x(+)
  AND    nvl(t2.d2, t1.d1) >= '01/01/2007'
  AND    nvl(t2.d2, t1.d1) <  '01/02/2007'
)
SELECT count(*) FROM t3
WHERE  f(t3.x1) = 0
 
COUNT(*)               
---------------------- 
1600                   
 
1 rows selected
 
2 750ms elapsed
SELECT userenv('client_info' ) FROM dual
 
USERENV('CLIENT_INFO')                                           
---------------------------------------------------------------- 
150000                                                           
 
1 rows selected
 
16ms elapsed

Effectivement, dans ce cas "materialize" signifie d'exécuter d'abord la requête désignée par "with" ...

Ceci ne marche pas pour les autres syntaxes

**zorino** · 28/12/2007, 09h17

Merci à tous pour votre aide, je crois que je vais m'en sortir !

Mention spéciale pour Pierre et Michel pour l'analyse et la pédagogie :

optimisation requête Oracle 10

Oracle

Discussions similaires

Partager

Partager