Discussion :

[stof]

Bon, on a fait des tas de trucs avec les DBA.

Tout d'abord, plutôt que d'attendre d'un jour sur l'autre de refaire les tests de différence entre la 1ère exécution et la seconde, ils ont pu vider le cache de la base :

Code :

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

1
2
alter system flush buffer_cache; --blocs de données
alter system flush shared_pool; --analyse requete

Ensuite sur un exemple pris à la seconde exécution, les temps sont très différents d'un outil à l'autre bien que tous soient en mode CHOOSE :
DBA : TOAD : 22" / PL/SQL Developer : 9"
Moi : CAST et SQL Developer : <2"

On se met en mode trace pour analyser tout ça
Dans tous les cas, le plan d'exécution est le même (1ère ou 2e exécution)
Sur le pire des exemples, on a atteint 78" (CAST)
1ère exécution

2e exécution


On s'aperçoit dans le détail que c'est la lecture des données sur le disque sur qui prend le plus de temps :


Idée1 --> utiliser le conseiller Oracle (-->ne conseille rien)
Idée2 --> faire un rebuild des index concernés
Ca y est un "miracle" se produit et ce qui prenait 45" prend 5" et la requête ci-dessus qui prenait 78" ne prend plus que 45" (bon, c'est pas divisé par 5 comme j'espérais mais c'est nettement mieux).

Les DBA m'ont dit que sur certaines des bases de PROD, un export/import des données était prévu, ce qui permet à Oracle de mieux s'organiser (blocs de données, etc) et que parmi les conséquences, ça implique aussi le recalcul des index.

Par conséquent, le recalcul des stat ne suffit pas, il faut de temps à autre reconstruire aussi les index
alter index IDX_MY_INDEX rebuild; (si tout le monde est déconnecté)
alter index IDX_MY_INDEX rebuild online; (duplique l'index avant de le remplacer si personne n'est connecté : risque de dupliquer la mémoire)

[Waldar]

Je n'aime ni votre analyse ni votre conclusion.

Je vous le redis, la requête que vous exécutez ne répond pas à votre attente.
Soit il manque des bouts (importants) dans votre requête (avec au moins un ORDER BY et un ROWNUM), soit ça vous renvoie un résultat avec une part de hasard mais ça vous convient ainsi.

Commençons déjà par là.

[mnitu]

...
Ensuite sur un exemple pris à la seconde exécution, les temps sont très différents d'un outil à l'autre bien que tous soient en mode CHOOSE ...

Par conséquent, le recalcul des stat ne suffit pas, il faut de temps à autre reconstruire aussi les index
alter index IDX_MY_INDEX rebuild; (si tout le monde est déconnecté)
alter index IDX_MY_INDEX rebuild online; (duplique l'index avant de le remplacer si personne n'est connecté : risque de dupliquer la mémoire)

Un conseil, change de DBA il connait que dalle!

[stof]

Je n'aime ni votre analyse ni votre conclusion.

OK, on va prendre les choses dans l'ordre :

Je vous le redis, la requête que vous exécutez ne répond pas à votre attente.

Qu'est-ce qui ne va pas dans l'une ou l'autre de ces requêtes (équivalentes)

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
select EEECHAMP1, CCCCHAMP1 into V_EEECHAMP1, V_CCCCHAMP1 
from
(
SELECT max(t1.DAT), t5.EEECHAMP1, t3.CCCCHAMP1
    FROM AAA t1
    JOIN BBB t2 ON t2.AAAIDT = t1.AAAIDT
    JOIN CCC t3 ON t3.BBBIDT = t2.BBBIDT
    JOIN DDD t4 ON t4.DDDIDT = t3.DDDIDT
    JOIN EEE t5 ON t5.EEEIDT = t4.EEEIDT
    WHERE t1.AAACRIT1 = 2
    AND t1.DAT <= to_date('2012-07-05', 'YYYY-MM-DD')
    AND t2.AAACRIT2 = 'TOTO'
    AND t3.CCCCRIT1 = 123
    GROUP BY t5.EEECHAMP1, t3.CCCCHAMP1
)

et

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
select EEECHAMP1, CCCCHAMP1 into V_EEECHAMP1, V_CCCCHAMP1 from              
(
SELECT t5.EEECHAMP1, t3.CCCCHAMP1
    FROM AAA t1
    JOIN BBB t2 ON t2.AAAIDT = t1.AAAIDT
    JOIN CCC t3 ON t3.BBBIDT = t2.BBBIDT
    JOIN DDD t4 ON t4.DDDIDT = t3.DDDIDT
    JOIN EEE t5 ON t5.EEEIDT = t4.EEEIDT
    WHERE t1.AAACRIT1 = 2
    AND t1.DAT <= to_date('2012-07-05', 'YYYY-MM-DD')
    AND t2.AAACRIT2 = 'TOTO'
    AND t3.CCCCRIT1 = 123
    order by t1.DAT desc
)
where rownum= 1

Un conseil, change de DBA il connait que dalle!

Ceci n'étant pas vraiment du pouvoir du petit développeur que je suis, mieux vaut peut-être réagir objectivement sur nos tests voire proposer vos solutions (promis quand je passe grand chef je vire tout le monde )

[Waldar]

Qu'est-ce qui ne va pas dans l'une ou l'autre de ces requêtes (équivalentes)

Mais elles ne sont pas du tout équivalentes, c'est bien le problè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
22
23
24
25
26
27
28
29
30
31
32
33
With MaTable as
(
select sysdate as dt, 'A' as c1, '1' as c2 from dual union all
select sysdate - 1  , 'B'      , '2'       from dual union all
select sysdate - 2  , 'C'      , '3'       from dual
)
select c1, c2
  from ( select max(dt), c1, c2
           from MaTable
        group by c1, c2);
 
C1 C2
-- --
C  3 
A  1 
B  2 
 
 
With MaTable as
(
select sysdate as dt, 'A' as c1, '1' as c2 from dual union all
select sysdate - 1  , 'B'      , '2'       from dual union all
select sysdate - 2  , 'C'      , '3'       from dual
)
select c1, c2
  from ( select c1, c2
           from MaTable
       order by dt desc)
 where rownum = 1;
 
C1 C2
-- --
A  1

La seconde requête répond elle au besoin que vous avez exprimé.

[mnitu]

Pourriez-vous préciser la version de votre base Oracle qui tourne en mode CHOSE ?

Si vous regardez attentivement la première (très) petit image avec le résultat de la trace vous allez trouver que vous avez compte deux fois (exécutions de) la requête.

[stof]

La seconde requête répond elle au besoin que vous avez exprimé.

Oui, honte à moi; la seconde est bien celle qui tourne; l'autre c'était effectivement pour essayer de l'écrire autrement mais c'est pas du tout pareil.

Pourriez-vous préciser la version de votre base Oracle qui tourne en mode CHOSE ?

10.2

Si vous regardez attentivement la première (très) petit image avec le résultat de la trace vous allez trouver que vous avez compte deux fois (exécutions de) la requête.

C'est peut-être quand je l'ai exécuté d'une part et mon DBA d'autre part?

[mnitu]

1) Pour Oracle 10g mettez le paramètre optimizer_goal = ALL_ROWS. CHOOSE c’était à l’époque où les ours blanc peupler la France.

2) Je ne sais pas ce que vous avez fait avec votre DBA mais je vous indique que vos 78’’ sont cumulées pour deux exécutions de la requête et non pas une.

3) Why are there so many Oracle related myths ? The Inconvenient Truth

[stof]

1) Pour Oracle 10g mettez le paramètre optimizer_goal = ALL_ROWS. CHOOSE c’était à l’époque où les ours blanc peupler la France.

Et donc la mode Printemps/Ete 2012, c'est RULE, tu penses que ça serait miraculeux? C'est quand-même le mode de base d'Oracle le CHOOSE...
En tous cas je ferai des tests avec et sans dans mon requêteur (CAST)

Code :

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

1
2
alter session set optimizer_mode = choose
alter session set optimizer_mode = rule

Après faut voir où c'est paramétré pour Tibco mais c'est une autre histoire.

PS : je ne vois pas "optimizer_goal" dans la table v$parameter

2) Je ne sais pas ce que vous avez fait avec votre DBA mais je vous indique que vos 78’’ sont cumulées pour deux exécutions de la requête et non pas une.

Bah j'ai exécuté ma requête tout seul et j'ai bien vu que ça a pris 78" (comment sur une seule demande de select j'aurais pu en avoir2?!)

3)

Je lirai ça (même si tous ces trucs dépassent mes compétences) car là c'est bloqué au boulot.

[mnitu]

Et donc la mode Printemps/Ete 2012, c'est RULE, tu penses que ça serait miraculeux? C'est quand-même le mode de base d'Oracle le CHOOSE...
...

Non RULE c'est la carrément de la préhistoire! (et de plus j'vais dit ALL_ROWS)

OPTIMIZER_MODE Initialization Parameter

Chechez la valeur CHOOSE dedans ...

[stof]

optimizer_goal (que je ne trouve pas dans v$parameter) est apparemment obsolète depuis la version 10g (et là)

[Waldar]

Maintenant qu'on a compilé un peu plus d'informations, que donnent les performances de cette requête dans votre bloc PL/SQL (pas de execute immediate) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT /*+ FIRST_ROWS_1 */ EEECHAMP1, CCCCHAMP1
  INTO V_EEECHAMP1, V_CCCCHAMP1
  FROM ( SELECT t5.EEECHAMP1, t3.CCCCHAMP1
           FROM AAA t1
           JOIN BBB t2 ON t2.AAAIDT = t1.AAAIDT
           JOIN CCC t3 ON t3.BBBIDT = t2.BBBIDT
           JOIN DDD t4 ON t4.DDDIDT = t3.DDDIDT
           JOIN EEE t5 ON t5.EEEIDT = t4.EEEIDT
          WHERE t1.AAACRIT1 = 2
            AND t1.DAT     <= I_DAT
            AND t2.AAACRIT2 = I_AAACRIT2
            AND t3.CCCCRIT1 = I_CCCCRIT1
       ORDER BY t1.DAT DESC)
 WHERE ROWNUM = 1;

Compte tenu que la variable I_DAT est déjà une date, nul besoin de la reconvertir en date.

[mnitu]

optimizer_goal (que je ne trouve pas dans v$parameter) est apparemment obsolète depuis la version 10g (et là)

Vous avez bien compris, c’est ce paramètre pour lequel la valeur CHOOSE à été défini à l’époque d’Oracle 7. Ce paramètre a été remplacé par optimizer_mode pour lequel la valeur CHOOSE n’est plus documentée.

Dans la requête proposé par Waldar commencez par ne pas utiliser le hint SQL /*+ FIRST_ROWS_1 */

[stof]

Ce paramètre a été remplacé par optimizer_mode pour lequel la valeur CHOOSE n’est plus documentée.

Donc la conclusion? Puisque je ne peux pas choisir un "ALL_ROWS" pour optimizer_goal et que RULE "c'est la carrément de la préhistoire", c'est que je suis dans le bon mode alors (?)

Dans la requête proposé par Waldar commencez par ne pas utiliser le hint SQL /*+ FIRST_ROWS_1 */

Bah en fait c'est la seule différence par rapport à ce qui était en place au début (puisque là il y avait la requête en "variables bind" sans effet supplémentaire) donc je suis passé directement en version hint SQL.
Note : le to_date c'était pour donner un exemple mais je passe bien directement une date I_DAT

Le souci, c'est qu'hier soir j'ai aussi recalculé les index donc difficile de juger des effets de cette modif seule.

Aujourd'hui j'ai un jeu de données qui fait que j'ai eu 3 blocages (pas loin de 10s).
Hier les exemples que j'ai eus avaient plus de lignes potentielles :
1311 lignes pour l'exemple qui prend 22"
1669 lignes pour l'exemple qui prend 25"
Aujourd'hui les triplets qui ont bloqué avaient : 2305, 10920 et 8571 et pourtant j'ai noté des temps assez proches de 10s.
J'aurais donc tendance à dire qu'il y a du mieux.
En tous cas la différence 1ère exécution / 2e exécution demeure au fil des intégrations.


Sauf autres idées de votre part (qui aboutisse à 0 timeout), je vais peut-être tester comment ça tourne quelques jours avec cette combinaison des 2 actions (reconstruction d'index + hint SQL) car il me faut plus de recul pour en tirer une conclusion sur la possible amélioration.

[mnitu]

Donc la conclusion? Puisque je ne peux pas choisir un "ALL_ROWS" pour optimizer_goal et que RULE "c'est la carrément de la préhistoire", c'est que je suis dans le bon mode alors (?)


...

La conclusion : optimizer_mode = ALL_ROWS !

[stof]

La conclusion : optimizer_mode = ALL_ROWS !

Bizarre ils en parlent pas dans l'aide de CAST, mais ça a l'air d'exister.
Je demande un vide du cache et je repasse les 3 requêtes du jour qui ont bloqué pour remesurer les temps.
PS : par contre ça peut très bien résoudre ce problème en particulier et en poser d'autres donc je n'y connais rien mais ça me paraît potentiellement dangereux comme modif.

Setting the Optimizer Mode to Rule for Oracle servers

If you want to change the Optimizer Mode for ALL connections to your server to Choose (from Rule the default setting) you can add the following option to the GloballySharedSettings.ini file:
[global]
ForceOptimizerChoose=1
Which will send the following query to the server: alter session set optimizer_mode = choose

To revert to the default Rule mode, add the following:

[global]
ForceOptimizerRule=1
Which will send the following query to the server: alter session set optimizer_mode = rule

[Waldar]

Mais votre outil CAST, de quand date-t-il ?
Y a-t-il un site de l'éditeur ?
Je n'en ai jamais entendu parler.

[Pomalaix]

Mais votre outil CAST, de quand date-t-il ?
Y a-t-il un site de l'éditeur ?
Je n'en ai jamais entendu parler.

Pareil, jamais entendu parler de ce truc là...

Pour Stof, vous devrier vous référer à la doc Oracle, qui est la source d'information la plus complète et la plus fiable.
Votre outil, qui parle encore de RULE et de CHOOSE, est resté bloqué à l'ère glaciaire comme Mnitu vous le disait au début.

[skuatamad]

Concernant le hint, il me semble qu'il devrait plutôt être dans la sous requête :

Code :

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

1
2
3
4
SELECT EEECHAMP1, CCCCHAMP1
  INTO V_EEECHAMP1, V_CCCCHAMP1
  FROM ( SELECT /*+ FIRST_ROWS_1 */ t5.EEECHAMP1, t3.CCCCHAMP1
  .......

[stof]

Mais votre outil CAST, de quand date-t-il ?
Y a-t-il un site de l'éditeur ?
Je n'en ai jamais entendu parler.

Ouais vous avez raison... ah on n'est pas aidé avec les outils qu'on a :
http://www.castsoftware.com

Remarque de skuatamad : le hint doit-il être dans la sous-requête ou ça ne change rien?

J'ai un peu de mal à comprendre mais pourquoi FIRST_ROWS_1 : si je regarde dans la liste des choix c'est FIRST_ROWS (tout court) qui est proposé, non?
Et si je lis la description, je comprend qu'on passe provisoirement en mode "cost based", ce que fait de manière générale le mode ALL_ROWS.
Par conséquent, si j'ai testé le hint SQL plus tôt, je n'obtinedrai rien de plus à tester en mode ALL_ROWS (?)