Discussion :

[elharet]

Bonjour;

Je me permets de solliciter votre aide car je ne trouve pas de solutions à mon problème.
En faite j'ai une requête SQL pénalisante, que j'ai optimisé via SQL TUNING Advisor, mais la première exécution est toujours très lente, environ 10 minutes, les exécutions suivantes sont de 4 secondes, ceci est normal car les données sont toujours dans le cache Buffer.
Je reviens vers vous si vous avez des idées afin d'optimiser le temps de réponse de cette requête (vues matérialisées,...?).
Merci d'avance pour votre aide.

Cordialement;

[al1_24]

Sans connaitre la requête, les index existants ni le plan d'exécution actuel, il est difficile de donner des conseils...

[13thFloor]

Hello,
est-ce bien confirmé que systématiquement la 1ère exécution recherche les données sur disque ?
En la lançant avec "set autotrace statistics on" tu confirmes que les disk reads disparaissent au bénéfice des buffer gets ?
Et le plan d'exécution ne change pas ?
S'il change il faudra peut être envisager un effet du cardinality feedback géré par le paramètre _optimizer_use_feedback.

Et comme signale al1_24, avoir le plan d'exécution ne sera pas superflu.

[mnitu]

...
En faite j'ai une requête SQL pénalisante, que j'ai optimisé via SQL TUNING Advisor, mais la première exécution est toujours très lente, environ 10 minutes, les exécutions suivantes sont de 4 secondes, ceci est normal car les données sont toujours dans le cache Buffer.
...

10 minutes vers 4 secondes ça ne s'explique pas par le fait que les données sont déjà en mémoire ou pas. Comme mentionné je pense plus à l'effet de cardinality feedback: première exécution prends un mauvais plan mais comme elle est surveillé l'optimisateur reoptimise la requête pour l'exécution suivante en changeant de plan. Commencez déjà par vérifier ce scénario. Ensuite il faut analyser la requête pour comprendre la raison de ce déraillement et utiliser une méthode appropriée si possible sans toucher aux paramètres internes d'Oracle.
Remarque: SQL Tunning Advisor c'est un outil automatique qui fait de son mieux mais ce n'est pas le panacée universel, lisez The Oracle Advisors from a Different Perspective pour un exemple.

[elharet]

Bonjour;

Je vous remercie pour vos réponses.
J'ai mis autotrace en ON, et j'ai réexecuté la requête, le plan d'exécution est le même, mais le temps de réponse est différent, et les statistics sont différents.
La première exécution:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Statistics
----------------------------------------------------------
        462  recursive calls
          0  db block gets
    1886533  consistent gets
      44355  physical reads
          0  redo size
       1613  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

La deuxième exécution:

Code :

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

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

Merci encore une fois pour votre aide.

[13thFloor]

Curieuse cette affaire !
Le result cache ne serait-il pas en cause ?
1) quelle est la version oracle ?
2) peux t-on avoir le plan d'exécution et surtout les indications en fin de plan : sample size, filter...

[mnitu]

Code :

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

462  recursive calls

semble dû au parsing hard de la requête, normal pour une première exécution. Et bien sûr leur "d'échauffement" de la base il est normal d'avoir plus des physical reads mais cela n'explique pas la difference 10 min vers 4 sec. "Refroidissez" la base et ré exécutez la requête en activant une trace sql étendue puis analysez les évènements d'attente et le parsing de la requête dans le fichier brut de trace ainsi généré, de cette manière vous allez comprendre où passent vos 10 minutes.

Mais cela va vous aider seulement si vraiment le plan c'est le même et si vous exécutez vraiment la même requête.