Discussion :

[cch02]

Bonjour,

J'ai un gros soucis de performance avec une requête SQL sous Oracle 9.2.0.8.
Mon plus gros problème: Je ne peux pas modifier la requête SQL car elle est déclenchée par un programme fourni par un Editeur. Le tout, non modifiable.

Je cherche donc tout autre moyen d'améliorer les performances.
Pour vous donner une idée:
- Lorsque cette table contient environ 50000 lignes, la requête prend 2min.
- Lorsque cette table contient environ 100000 lignes, la requête prend 3h00.

Voici la requête:

Code :

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

1
2
3
4
5
6
7
UPDATE REQ_APPROVALLOG a1
SET (APPROVAL_START_TIME, APPROVAL_DURATION) =
   (SELECT MAX(a2.APPROVAL_END_TIME), (a1.APPROVAL_END_TIME - MAX(a2.APPROVAL_END_TIME)) * 86400
    FROM REQ_APPROVALLOG a2
    WHERE     a1.REQUEST_ID = a2.REQUEST_ID
		AND a1.DATA_SOURCE = a2.DATA_SOURCE
          AND a1.APPROVAL_END_TIME > a2.APPROVAL_END_TIME);

Voici la description de la table:
Desc.txt

Voici un échantillon de données:
Data.txt

Merci d'avance de votre aide.

[Waldar]

Dommage que dans vos données il n'y ait :

• pas le résultat attendu
• que des request_id différents
• pas les ordres d'inserts, ça prend du temps de les taper à la main (pour la table c'est très bien)

[mnitu]

Sans toucher à la requête ça va être compliqué. Essayez d'utiliser une vue matérialisée pour pré-calculer les zones start_time et duration et ce qu'il faut pour qu'Oracle ré écrit la requête avec la vue matérialisée.

[McM]

Sans toucher la requête, faut voir au niveau de la base ce qui fait ramer : Le disque, le undo, la table, le serveur, etc...

[cch02]

Sans toucher la requête, faut voir au niveau de la base ce qui fait ramer : Le disque, le undo, la table, le serveur, etc...

J'ai un trace:

TKPROF: Release 9.2.0.6.0 - Production on Tue May 4 09:35:10 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: trace_bi_pic_20100504.trc
Sort options: exeela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

-- Update APPROVAL_START_TIME, APPROVAL_DURATION in seconds for all REQ_APPROVALLOG Groups except the --first Approver group

UPDATE REQ_APPROVALLOG a1
SET (APPROVAL_START_TIME, APPROVAL_DURATION) =
(SELECT MAX(a2.APPROVAL_END_TIME), (a1.APPROVAL_END_TIME - MAX(a2.APPROVAL_END_TIME)) * 86400
FROM REQ_APPROVALLOG a2
WHERE a1.REQUEST_ID = a2.REQUEST_ID
AND a1.DATA_SOURCE = a2.DATA_SOURCE
AND a1.APPROVAL_END_TIME > a2.APPROVAL_END_TIME)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3650.57 11464.30 36870551 775598366 61054 26954
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3650.57 11464.30 36870551 775598366 61054 26954

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46 (BI_PICASSO)

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE
26954 TABLE ACCESS FULL REQ_APPROVALLOG
26944 SORT AGGREGATE
170 TABLE ACCESS FULL REQ_APPROVALLOG


Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'REQ_APPROVALLOG'
26954 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'REQ_APPROVALLOG'
26944 SORT (AGGREGATE)
170 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'REQ_APPROVALLOG'

[McM]

C'est Clair, il manque un index !

Code :

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

1
2
3
4
5
(SELECT MAX(a2.APPROVAL_END_TIME), (a1.APPROVAL_END_TIME - MAX(a2.APPROVAL_END_TIME)) * 86400
FROM REQ_APPROVALLOG a2
WHERE a1.REQUEST_ID = a2.REQUEST_ID
AND a1.DATA_SOURCE = a2.DATA_SOURCE
AND a1.APPROVAL_END_TIME > a2.APPROVAL_END_TIME)

=> Index sur REQUEST_ID, DATA_SOURCE

[cch02]

Dommage que dans vos données il n'y ait :

• pas le résultat attendu
• que des request_id différents
• pas les ordres d'inserts, ça prend du temps de les taper à la main (pour la table c'est très bien)

On voit tout de suite que l'on à affaire à des professionnels.
Je vais vous fournir tout cela très vite !
Pour le résultat attendu, celui n'est pas remis en cause. La requête donne le résultat escompté. C'est à dire mettre à jour le champ approval_start_time avec le plus ancienne valeur de approval_end_time connue pour un même couple request_id/data_source. puis pour chaque ligne de request_id/data_source calculer le temps d'approval en secondes.

Sans toucher à la requête ça va être compliqué. Essayez d'utiliser une vue matérialisée pour pré-calculer les zones start_time et duration et ce qu'il faut pour qu'Oracle ré écrit la requête avec la vue matérialisée.

Qu'entendez-vous par 'vue matérialisée' ?
CREATE VIEW .... ?

Le soucis, c'est que je ne peux pas intervenir dans le programme qui génère les requêtes. C'est une solution packagée où rien est modifiable. Cette requête n'est qu'un infime partie de ce qui est exécuté mais c'est la requête de très loin la plus longue.
Même si je créais une vue puis une requête de mise à jour à partir de la vue vers ma table req_approvallog, la requête d'update s'exécuterait toujours.

PS: Je ne suis pas admin Oracle et mes connaissances sont assez limitées.
Je maîtrise par contre assez bien le langage SQL.

[cch02]

C'est Clair, il manque un index !

Code :

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

1
2
3
4
5
(SELECT MAX(a2.APPROVAL_END_TIME), (a1.APPROVAL_END_TIME - MAX(a2.APPROVAL_END_TIME)) * 86400
FROM REQ_APPROVALLOG a2
WHERE a1.REQUEST_ID = a2.REQUEST_ID
AND a1.DATA_SOURCE = a2.DATA_SOURCE
AND a1.APPROVAL_END_TIME > a2.APPROVAL_END_TIME)

=> Index sur REQUEST_ID, DATA_SOURCE

oui, c'est ce que je me suis dit aussi quand j'ai vu le trace tout à l'heure.
(je suis en plein debug)

J'ai ajouté un index sur une base de test (identique à ma prod en volume)
sur le champ request_id uniquement.
En fait, le champ data_source est toujours = 'request' aujourd'hui
car nous n'avons pas encore d'autre source de données.

Et ça ne va pas plus vite. Pour info; le serveur de test et de prod ont la même puissance car en cas de panne de la prod, c'est lui qui prendrait le relais. Les disques sont dans un SAN.

[mnitu]

...
Qu'entendez-vous par 'vue matérialisée' ?
CREATE VIEW .... ?

Le soucis, c'est que je ne peux pas intervenir dans le programme qui génère les requêtes. C'est une solution packagée où rien est modifiable. Cette requête n'est qu'un infime partie de ce qui est exécuté mais c'est la requête de très loin la plus longue.
Même si je créais une vue puis une requête de mise à jour à partir de la vue vers ma table req_approvallog, la requête d'update s'exécuterait toujours.

PS: Je ne suis pas admin Oracle et mes connaissances sont assez limitées.
Je maîtrise par contre assez bien le langage SQL.

Une vue matérialisée c'est une vue qui contient des données comme une table. De plus Oracle dans certaines conditions peut récrire votre requête pour utiliser une vue matérialisée, parce qu'elle contient déjà le resultat de ce que vous est en train de calculer.

[McM]

Malgré l'index, la requête donne le même temps ?
Les stats sont elles à jour ?

Donne nous l'explain de l'update avec et sans l'index.

Code :

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

1
2
3
4
5
6
7
UPDATE REQ_APPROVALLOG a1
SET (APPROVAL_START_TIME, APPROVAL_DURATION) =
(SELECT MAX(a2.APPROVAL_END_TIME), (a1.APPROVAL_END_TIME - MAX(a2.APPROVAL_END_TIME)) * 86400
FROM REQ_APPROVALLOG a2
WHERE a1.REQUEST_ID = a2.REQUEST_ID
AND a1.DATA_SOURCE = a2.DATA_SOURCE
AND a1.APPROVAL_END_TIME > a2.APPROVAL_END_TIME)

sans l'index normalement c'est ce que tu avais mis avec tkprof

0 UPDATE
26954 TABLE ACCESS FULL REQ_APPROVALLOG
26944 SORT AGGREGATE
170 TABLE ACCESS FULL REQ_APPROVALLOG

[cch02]

Malgré l'index, la requête donne le même temps ?
Les stats sont elles à jour ?

Donne nous l'explain de l'update avec et sans l'index.

Code :

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

1
2
3
4
5
6
7
UPDATE REQ_APPROVALLOG a1
SET (APPROVAL_START_TIME, APPROVAL_DURATION) =
(SELECT MAX(a2.APPROVAL_END_TIME), (a1.APPROVAL_END_TIME - MAX(a2.APPROVAL_END_TIME)) * 86400
FROM REQ_APPROVALLOG a2
WHERE a1.REQUEST_ID = a2.REQUEST_ID
AND a1.DATA_SOURCE = a2.DATA_SOURCE
AND a1.APPROVAL_END_TIME > a2.APPROVAL_END_TIME)

sans l'index normalement c'est ce que tu avais mis avec tkprof

Oui, je confirme que je n'avais pas encore ajouté d'index lors du tkprof.
et oui après ajout de l'index sur la colonne request_id, les temps d'execution n'ont pas changé.

j'avais mis à jour les stats avant de relancer le requête de cette manière :
exec dbms_stats.gather_schema_stats(ownname=> 'bi_picasso',ESTIMATE_PERCENT => NULL, DEGREE => 4, CASCADE => TRUE, GRANULARITY=>'ALL', method_opt=>'FOR ALL INDEXED COLUMNS');

J'ai un explain via tora:
explain.jpg

[mnitu]

Moi, je trouve que le plan d'exécution initial n'était pas mal: il faut mettre à jour tout la table avec un sort aggregate sur toute la table ().

[Waldar]

De toutes façons c'est la requête le problème ; comme le dit mnitu il n'y a pas de where (donc full update), et je suspecte qu'il y a moyen de la réécrire de façon beaucoup plus efficace.

[cch02]

Moi, je trouve que le plan d'exécution initial n'était pas mal: il faut mettre à jour tout la table avec un sort aggregate sur toute la table ().

De toutes façons c'est la requête le problème ; comme le dit mnitu il n'y a pas de where (donc full update), et je suspecte qu'il y a moyen de la réécrire de façon beaucoup plus efficace.

Personne n'est convaincu du contraire. Le problème, c'est la requête et je pense que je serais moi-même en mesure d'en écrire une plus performante.
(Vu les perfs actuelles, c'est pas dur non plus de trouver mieux ^_^)

MAIS comme je l'ai dit dès le départ, ce n'est pas possible de la modifier.
Bon, et bien je sens que je vais devoir trouver une solution de contournement:
Truncate de cette table ce qui me donnera une optimisation infinie !
Et create d'une table perso avec une requête maison optimisée aux petits oignons pour arriver au même résultat!

Je vais faire ajouter un index en prod sur request_id (ça mange pas de pain)
Pensez-vous que un index sur le champ date changerait quoi que ce soit (je pense que non mais je préfère avoir l'avis d'experts)

Et comme le requête tourne tous les jours, on verra rapidement si il y a une quelconque amélioration

Encore merci pour votre aide, je vous tiens au courant de mes résultats dès que ça fonctionne

[Waldar]

Un index sur la triplette (request_id, data_source, approval_end_time), oui ce serait utile.

[McM]

De toutes façons c'est la requête le problème ; comme le dit mnitu il n'y a pas de where (donc full update), et je suspecte qu'il y a moyen de la réécrire de façon beaucoup plus efficace.

C'est pas l'update qui pose problème (on met tout à jour, donc Ok pour le FULL), par contre le select qui ramène les nouvelles données, lui, est pas optimisé.

[cch02]

Un index sur la triplette (request_id, data_source, approval_end_time), oui ce serait utile.

C'est ce que j'ai demandé à la prod.
Merci du conseil

C'est pas l'update qui pose problème (on met tout à jour, donc Ok pour le FULL), par contre le select qui ramène les nouvelles données, lui, est pas optimisé.

Non non, Waldar a eu la bonne intuition
Il est inutile de tout updater à chaque fois

Et puis bon, c'est sûr aussi que le select est un peu méchant pour les I/O et les buffers !

Donc, cette requête est moisie et l'éditeur va avoir droit à son ticket sur ce sujet ^_^

[Ahmed AANGOUR]

A partir de la 10g il aurait été possible d'utiliser un SQL profile pour aider l'optimizer à trouver un bon plan.

La seule solution que je vois en 9i si on ne peut pas toucher à la requête est de forcer l'utilisation du bon plan via un STROED OUTLINE.

[cch02]

Un index sur la triplette (request_id, data_source, approval_end_time), oui ce serait utile.

ATTENTION, roulement de tambours :
9 secondes !!!

En fait, c'est mon calcul de stats qui n'avait pas du passer parce que j'en ai refait un autre ce matin après l'ajout des nouveaux index mais que sur la table req_approvallog, pas sur tout le schema.

Et là, hop, 1400x plus rapide qu'avant.

Je doit quand même avouer que je ne m'attendais pas à une optimisation aussi radicale...

MERCI BEAUCOUP

[Ahmed AANGOUR]

Effectivement dans le plan d'execution que t'as attaché on voit que les cardinalités estimées étaient de 1 pour toutes les opérations

Peux tu envoyer le nouveau plan ?