[TUNING] Jointure après agrégat

**FSiebert** · 18/06/2012, 10h50

Bonjour à tous,

J'aurai besoin de quelques pistes pour améliorer une requête.

Je vous explique rapidement la situation : nous traitons un certain nombre d'événements, ces événements passent par différents statuts au cours du traitement et on conserve un historique des ces statuts.
Pour du reporting, on voudrait récupérer connaître le nombre de passage par chacun des status pour chaque jour MAIS un événement pouvant être traité sur plusieurs jours on souhaite que les status de chaque événement soient comptés sur le dernier jour de traitement (avec la requête ça sera peut-être plus clair).

Je ne suis pas totalement maître de la structure de la table (statuts directement en VARCHAR2, etc.) et je ne peux pas trop casser l'existant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE "SPV_LOG_BOOK"
  (
    "ID" NUMBER NOT NULL ENABLE,
    "DATE_TIME" TIMESTAMP (6) NOT NULL ENABLE,
    "EVENT_ID"  NUMBER NOT NULL ENABLE,
    "STATUS"    VARCHAR2(40 CHAR) NOT NULL ENABLE,
    CONSTRAINT "PK_SPV_LOG_BOOK" PRIMARY KEY ("ID") ENABLE
  );
CREATE UNIQUE INDEX "PK_SPV_LOG_BOOK" ON "SPV_LOG_BOOK" ("ID");
CREATE INDEX "INDEX_SPVLB_DATETIME" ON "SPV_LOG_BOOK" ("DATE_TIME" DESC);
CREATE INDEX "INDEX_SPVLB_EVENTID" ON "SPV_LOG_BOOK" ("EVENT_ID" DESC);

La requête basique avec son plan d'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
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT h.DATE_TIME, s.STATUS, COUNT(*)
FROM SPV_LOG_BOOK s
INNER JOIN (
  SELECT EVENT_ID, TRUNC(MAX(DATE_TIME)) As DATE_TIME
  FROM SPV_LOG_BOOK
  GROUP BY EVENT_ID
) h ON h.EVENT_ID = s.EVENT_ID
GROUP BY h.DATE_TIME, s.STATUS;
 
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
|   0 | SELECT STATEMENT      |              |   247K|    17M|       |  4975   (1)| 00:01:00 |                                                                                                                                                                                                               
|   1 |  HASH GROUP BY        |              |   247K|    17M|       |  4975   (1)| 00:01:00 |                                                                                                                                                                                                               
|*  2 |   HASH JOIN           |              |   247K|    17M|  1984K|  4966   (1)| 00:01:00 |                                                                                                                                                                                                               
|   3 |    VIEW               |              | 65523 |  1215K|       |  2402   (1)| 00:00:29 |                                                                                                                                                                                                               
|   4 |     HASH GROUP BY     |              | 65523 |  1151K|  6816K|  2402   (1)| 00:00:29 |                                                                                                                                                                                                               
|   5 |      TABLE ACCESS FULL| SPV_LOG_BOOK |   247K|  4350K|       |  1657   (1)| 00:00:20 |                                                                                                                                                                                                               
|   6 |    TABLE ACCESS FULL  | SPV_LOG_BOOK |   247K|    13M|       |  1658   (1)| 00:00:20 |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   2 - access("H"."EVENT_ID"="S"."EVENT_ID" AND                                                                                                                                                                                                                                                              
              SYS_OP_DESCEND("H"."EVENT_ID")=SYS_OP_DESCEND("S"."EVENT_ID"))

Un petit jeu de données pour les personnes motivées :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450953,to_timestamp('17/06/12 07:27:13,680000000','DD/MM/RR HH24:MI:SS,FF'),474393,'O');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450954,to_timestamp('18/06/12 07:27:15,639000000','DD/MM/RR HH24:MI:SS,FF'),474393,'R');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450955,to_timestamp('18/06/12 07:27:15,643000000','DD/MM/RR HH24:MI:SS,FF'),474393,'F');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450959,to_timestamp('18/06/12 07:35:52,704000000','DD/MM/RR HH24:MI:SS,FF'),474395,'O');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450960,to_timestamp('18/06/12 07:35:53,090000000','DD/MM/RR HH24:MI:SS,FF'),474395,'R');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450961,to_timestamp('18/06/12 07:35:53,093000000','DD/MM/RR HH24:MI:SS,FF'),474395,'F');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450952,to_timestamp('18/06/12 07:19:22,882000000','DD/MM/RR HH24:MI:SS,FF'),474392,'O');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450956,to_timestamp('18/06/12 07:31:49,548000000','DD/MM/RR HH24:MI:SS,FF'),474394,'O');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450957,to_timestamp('18/06/12 07:31:50,676000000','DD/MM/RR HH24:MI:SS,FF'),474394,'R');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450958,to_timestamp('18/06/12 07:31:50,681000000','DD/MM/RR HH24:MI:SS,FF'),474394,'F');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450962,to_timestamp('18/06/12 07:35:53,515000000','DD/MM/RR HH24:MI:SS,FF'),474396,'O');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450963,to_timestamp('18/06/12 07:35:54,236000000','DD/MM/RR HH24:MI:SS,FF'),474396,'R');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450964,to_timestamp('18/06/12 07:35:54,240000000','DD/MM/RR HH24:MI:SS,FF'),474396,'F');
Insert into SPV_LOG_BOOK (ID,DATE_TIME,EVENT_ID,STATUS) values (1450965,to_timestamp('18/06/12 07:54:25,353000000','DD/MM/RR HH24:MI:SS,FF'),474397,'O');

Quelles peuvent être les bonnes idées pour améliorer cette requête ?

Merci par avance pour vos suggestions.

Cordialement,
FSiebert

**skuatamad** · 18/06/2012, 13h16

Déjà, tu n'es pas obligé de scanner 2 fois la table, mais il y a quand même une sous-requête, je ne sais pas si ça améliore beaucoup :

Code :

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

1
2
3
4
5
6
select dt, status, count(*)
  from (select event_id, status, 
               max(trunc(date_time)) over (partition by event_id) as dt
          from spv_log_book
       )
 group by dt, status

Peux tu créer des index ? Un index sur le couple (event_id, date_time) pourrait être util.

**FSiebert** · 18/06/2012, 14h28

Merci pour votre réponse skuatamad, j'ai du mal à penser aux fonctions de fenêtrage, ça ne me vient pas encore

Cette idée est pas mal, elle permet effectivement d'éviter un deuxième FTS

Mais malheureusement, la requête n'est pas plus rapide.

Les deux plans d'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
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   867K|    44M|       |  5742   (2)| 00:01:09 |
|   1 |  HASH GROUP BY        |              |   867K|    44M|       |  5742   (2)| 00:01:09 |
|*  2 |   HASH JOIN           |              |   867K|    44M|    25M|  5712   (2)| 00:01:09 |
|   3 |    VIEW               |              |   867K|    15M|       |  1264   (4)| 00:00:16 |
|   4 |     HASH GROUP BY     |              |   867K|    21M|       |  1264   (4)| 00:00:16 |
|   5 |      TABLE ACCESS FULL| SPV_LOG_BOOK |   867K|    21M|       |  1233   (1)| 00:00:15 |
|   6 |    TABLE ACCESS FULL  | SPV_LOG_BOOK |   867K|    28M|       |  1235   (1)| 00:00:15 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("H"."EVENT_ID"="S"."EVENT_ID" AND
              SYS_OP_DESCEND("H"."EVENT_ID")=SYS_OP_DESCEND("S"."EVENT_ID"))
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      18690  consistent gets
          0  physical reads
       5400  redo size
       6301  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        184  rows processed

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
---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   867K|    25M|       | 11625   (1)| 00:02:20 |
|   1 |  HASH GROUP BY       |              |   867K|    25M|       | 11625   (1)| 00:02:20 |
|   2 |   VIEW               |              |   867K|    25M|       | 11594   (1)| 00:02:20 |
|   3 |    WINDOW SORT       |              |   867K|    39M|    99M| 11594   (1)| 00:02:20 |
|   4 |     TABLE ACCESS FULL| SPV_LOG_BOOK |   867K|    39M|       |  1235   (1)| 00:00:15 |
---------------------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13899  consistent gets
          0  physical reads
       5444  redo size
       6480  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        184  rows processed

Une idée de pourquoi la seconde requête est plus lente à s'exécuter ?

Edit : J'ai oublié une partie de la question

Effectivement je pourrais éventuellement ajouter un index sur (EVENT_ID, DATE_TIME) qui permettrait de remplacer un FTS par un Full Scan de l'index pour la sous-requête (sur la requête sans la fonction de fenêtrage). Après il ne faut pas non plus trop impacter les -- nombreuses -- insertions dans cette table.
Par contre cet index n'améliorerait rien si j'utilise la fonction de fenêtrage n'est-ce-pas ?

**punkoff** · 18/06/2012, 14h49

tention :

Code :

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

1
2
 
max(trunc(date_time))

c'est pas pareil que :

Code :

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

1
2
 
trunc(max(date_time))

Surtout si vous avez un index : (event_id, date_time)

Dans un cas il regardera si c'est util de l'utiliser mais pas dans l'autre.

**FSiebert** · 18/06/2012, 15h04

Effectivement punkoff, je n'avais pas fait attention que skuatamad avait inversé dans sa requête (pour pouvoir utiliser la fonction de fenêtrage je présume).

Fonctionnellement, le résultat est identique mais en effet le TRUNC peut empêcher d'utiliser l'index (sauf à faire un index basé sur une fonction).

**skuatamad** · 18/06/2012, 15h21

Peux tu utiliser le hint gather_plan_statistics, puis juste après générer le plan avec dbms_xplan.display_cursor (pour avoir plus d'info sur l'estimation)

Code :

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

1
2
SELECT /*+ gather_plan_statistics */ dt, STATUS, count(*) from .....
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

As tu la possibilité de créer des index ?
Cette requête sera-t-elle régulièrement exécutée ?

Il est généralement préférable d'utiliser les fonctions de fenétrages sur des ensembles déjà filtrés plutôt que sur toute une grosse table :
Analytic Agony
Aucune fonctionnalité n'est miraculeuse

Effectivement, comme l'a fait remarquer punkoff, le trunc dans ma requête n'est peut etre pas placé au bonne endroit (mais tu peux entourer le max analytique d'un trunc).
Sinon un index sur (event_id, trunc(date_time), status) pour travailler intégralement la sous-requête sur l'index.

[edit]Faudrait migrer le message sur le forum oracle pour que d'autres puissent aussi intervenir.

[TUNING] Jointure après agrégat

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager