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]

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.

[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.

[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