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

**Waldar** · 18/06/2012, 15h45

Je n'ai pas l'impression que l'index descendant sur event_id soit performant ici.

En le recréant en index classique, sur le jeu de test les plans d'exécution sont déjà meilleurs et les deux requête utilisent l'index.
Avec l'index descendant que je ne vois que des scans complets.

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

Oui, je peux ajouter des index

Cependant on écrit bien plus fréquemment dans cette table (à chaque changement de statut d'un événement (entre 5 et 10 statuts différents pour le traitement d'un événenement) pour 200000 événements traités par jour) qu'on ne va exécuter cette requête (pour l'instant elle ne servira que pour du reporting donc quelques exécutions par jour seulement mais si j'arrive à la rendre performante elle pourra servir pour de la supervision : détection en cas de pic de statut d'erreur, etc. donc quelques fois par heure). Dans tous les cas elle ne sera pas exécuté des milliers de fois par heure.

Voilà les statistiques plus détaillées pour les deux requêtes (d'ailleurs je veux bien quelques explications rapides sur ces différentes informations

) :

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
---------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                  
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |                                                                                                                                                                                  
---------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                  
|   1 |  HASH GROUP BY        |              |      1 |    867K|    184 |00:00:02.25 |   20239 |   877K|   877K|   25M (0)|                                                                                                                                                                                  
|*  2 |   HASH JOIN           |              |      1 |    867K|    894K|00:00:05.87 |   20239 |    33M|  5277K|   44M (0)|                                                                                                                                                                                  
|   3 |    VIEW               |              |      1 |    867K|    697K|00:00:02.63 |   15426 |       |       |          |                                                                                                                                                                                  
|   4 |     HASH GROUP BY     |              |      1 |    867K|    697K|00:00:01.23 |   15426 |    32M|  5393K|   38M (0)|                                                                                                                                                                                  
|   5 |      TABLE ACCESS FULL| SPV_LOG_BOOK |      1 |    867K|    894K|00:00:00.89 |   15426 |       |       |          |                                                                                                                                                                                  
|   6 |    TABLE ACCESS FULL  | SPV_LOG_BOOK |      1 |    867K|    894K|00:00:00.89 |    4813 |       |       |          |                                                                                                                                                                                  
---------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                  
 
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"))

Code :

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

1
2
3
4
5
6
7
8
-----------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                          
| Id  | Operation            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |                                                                                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                          
|   1 |  HASH GROUP BY       |              |      1 |    867K|    184 |00:00:02.50 |   15418 |     73 |   877K|   877K|   21M (0)|                                                                                                                                                                          
|   2 |   VIEW               |              |      1 |    867K|    894K|00:00:04.05 |   15418 |     73 |       |       |          |                                                                                                                                                                          
|   3 |    WINDOW SORT       |              |      1 |    867K|    894K|00:00:02.26 |   15418 |     73 |    42M|  2303K|   37M (0)|                                                                                                                                                                          
|   4 |     TABLE ACCESS FULL| SPV_LOG_BOOK |      1 |    867K|    894K|00:00:01.79 |   15418 |     73 |       |       |          |                                                                                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------

Merci pour l'article skuatamad, j'étudierai ça.

@Waldar L'index descendant était déjà là, mais effectivement je devrais pouvoir le remplacer, je vais regarder ça. Merci pour la remarque !

PS : Le sujet a été déplacé, merci à CinePhil.

**Mohamed.Houri** · 18/06/2012, 15h57

Envoyé par Waldar

Je n'ai pas l'impression que l'index descendant sur event_id soit performant ici.

En le recréant en index classique, sur le jeu de test les plans d'exécution sont déjà meilleurs et les deux requête utilisent l'index.
Avec l'index descendant que je ne vois que des scans complets.

Excellente remarque

**mnitu** · 18/06/2012, 20h02

L'index descendant est un index basé sur une fonction. En 11g il n'a aucun souci à l'utiliser donc j'aimerais savoir s quelle est votre version d'Oracle ?

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
34
35
36
37
38
39
 
mni@DIANA> SELECT h.DATE_TIME, s.STATUS, COUNT(*)
  2  FROM SPV_LOG_BOOK s
  3  INNER JOIN (
  4    SELECT EVENT_ID, TRUNC(MAX(DATE_TIME)) AS DATE_TIME
  5    FROM SPV_LOG_BOOK
  6    GROUP BY EVENT_ID
  7  ) h ON h.EVENT_ID = s.EVENT_ID
  8  GROUP BY h.DATE_TIME, s.STATUS;
 
DATE_TIM STATUS                                     COUNT(*)
-------- ---------------------------------------- ----------
18/06/12 O                                                 6
18/06/12 R                                                 4
18/06/12 F                                                 4
 
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2099353034
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |    33 |  1782 |     1 (100)| 00:00:01 |
|   1 |  HASH GROUP BY                  |                     |    33 |  1782 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                     |    33 |  1782 |     0   (0)| 00:00:01 |
|   3 |    VIEW                         |                     |    14 |   266 |     0   (0)| 00:00:01 |
|   4 |     HASH GROUP BY               |                     |    14 |   364 |     0   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| SPV_LOG_BOOK        |    14 |   364 |     0   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | INDEX_SPVLB_EVENTID |    14 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID  | SPV_LOG_BOOK        |     2 |    70 |     0   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN            | INDEX_SPVLB_EVENTID |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access(SYS_OP_DESCEND("H"."EVENT_ID")=SYS_OP_DESCEND("EVENT_ID"))
       filter("H"."EVENT_ID"=SYS_OP_UNDESCEND(SYS_OP_DESCEND("EVENT_ID")))

**FSiebert** · 19/06/2012, 16h11

Bonjour à tous,

Pour répondre à la question, nous sommes en 10G (10.2.0.5) mais dans tous les cas l'index n'est jamais utilisé (qu'il soit ascendant ou descendant).
J'ai reconstruit la table (MOVE) et les index avant de recalculer les statistiques pour repartir sur des bases saines vu qu'on avait pas mal joué avec la table ces derniers temps (test de purge, etc.).

Je vous remet des plans d'exécutions (sur un autre environnement donc à ne pas comparer avec les plans précédents).

La requête de base :

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
SELECT /*+ gather_plan_statistics */ h.DATE_TIME As DAY, s.STATUS, COUNT(*) AS VALUE
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         | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|                                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                              
|   0 | SELECT STATEMENT      |              |      1 |        |       |       |  1465 (100)|          |    625 |00:00:01.01 |    3004 |   3675 |   1239 |       |       |          |         |                                                                                                              
|   1 |  HASH GROUP BY        |              |      1 |     21 |   483 |       |  1465   (2)| 00:00:18 |    625 |00:00:01.01 |    3004 |   3675 |   1239 |   832K|   832K|  651K (0)|         |                                                                                                              
|*  2 |   HASH JOIN           |              |      1 |    251K|  5643K|       |  1457   (2)| 00:00:18 |    251K|00:00:01.00 |    3004 |   3675 |   1239 |  2689K|  1484K|  890K (5)|    8192 |                                                                                                              
|   3 |    VIEW               |              |      1 |  43872 |   471K|       |  1041   (2)| 00:00:13 |  48719 |00:00:00.53 |    1502 |    490 |    490 |       |       |          |         |                                                                                                              
|   4 |     HASH GROUP BY     |              |      1 |  43872 |   685K|  5928K|  1041   (2)| 00:00:13 |  48719 |00:00:00.38 |    1502 |    490 |    490 |  7410K|  2727K| 2016K (1)|    4096 |                                                                                                              
|   5 |      TABLE ACCESS FULL| SPV_LOG_BOOK |      1 |    251K|  3926K|       |   413   (1)| 00:00:05 |    251K|00:00:00.25 |    1502 |      0 |      0 |       |       |          |         |                                                                                                              
|   6 |    TABLE ACCESS FULL  | SPV_LOG_BOOK |      1 |    251K|  2944K|       |   414   (1)| 00:00:05 |    251K|00:00:00.25 |    1502 |      0 |      0 |       |       |          |         |                                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                              
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   2 - access("H"."EVENT_ID"="S"."EVENT_ID")

En forçant l'utilisation des index :

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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                     
| Id  | Operation                       | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                     
|   0 | SELECT STATEMENT                |                     |      1 |        |       |   179K(100)|          |    625 |00:00:01.58 |     179K|   3367 |    931 |       |       |          |         |                                                                                                     
|   1 |  HASH GROUP BY                  |                     |      1 |     21 |   483 |   179K  (1)| 00:35:57 |    625 |00:00:01.58 |     179K|   3367 |    931 |   832K|   832K|  651K (0)|         |                                                                                                     
|*  2 |   HASH JOIN                     |                     |      1 |    251K|  5643K|   179K  (1)| 00:35:57 |    251K|00:00:01.57 |     179K|   3367 |    931 |  2689K|  1484K|  890K (5)|    8192 |                                                                                                     
|   3 |    VIEW                         |                     |      1 |  43872 |   471K| 89861   (1)| 00:17:59 |  48719 |00:00:00.74 |   89833 |    182 |    182 |       |       |          |         |                                                                                                     
|   4 |     HASH GROUP BY               |                     |      1 |  43872 |   685K| 89861   (1)| 00:17:59 |  48719 |00:00:00.59 |   89833 |    182 |    182 |  2896K|  1364K| 1976K (1)|    4096 |                                                                                                     
|   5 |      TABLE ACCESS BY INDEX ROWID| SPV_LOG_BOOK        |      1 |    251K|  3926K| 89861   (1)| 00:17:59 |    251K|00:00:01.01 |   89833 |      0 |      0 |       |       |          |         |                                                                                                     
|   6 |       INDEX FULL SCAN           | INDEX_SPVLB_EVENTID |      1 |    251K|       |   564   (1)| 00:00:07 |    251K|00:00:00.25 |     562 |      0 |      0 |       |       |          |         |                                                                                                     
|   7 |    TABLE ACCESS BY INDEX ROWID  | SPV_LOG_BOOK        |      1 |    251K|  2944K| 89861   (1)| 00:17:59 |    251K|00:00:01.51 |   89833 |      0 |      0 |       |       |          |         |                                                                                                     
|   8 |     INDEX FULL SCAN             | INDEX_SPVLB_EVENTID |      1 |    251K|       |   564   (1)| 00:00:07 |    251K|00:00:00.25 |     562 |      0 |      0 |       |       |          |         |                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                     
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   2 - access("H"."EVENT_ID"="S"."EVENT_ID")

L'optimiseur ne semble pas trop largué sur les cardinalités.

Je vous exporterai bien les données pour que vous puissiez tester mais le fichier d'export fait 42 Mo...
Concernant la sélectivité, j'ai 48721 EVENT_ID distincts sur 251270 lignes, trop peu sélectif pour que ça vaille le coup/coût ?
Ça me surprend que le coût estimé pour parcourir l'index soit plus élevé que celui pour parcourir la table...

Merci à tous pour votre patience.

**mnitu** · 19/06/2012, 16h30

1.1 secondes ce n’est pas si mal que ça. Que voulez vous obtenir suite à l'optimisation ?

**Waldar** · 19/06/2012, 17h58

En tout cas on voit qu'il a raison de ne pas l'utiliser.

Est-ce que votre table est compressée ? À coup de full scan il peut être intéressant de réduire vos I/O contre un peu de CPU, et compte-tenu que vous avez très peu de colonnes (et donc beaucoup de lignes par bloc), la compression pourrait être importante (facteur 3 à 6, estimation doigt mouillé).

Est-ce que vous faites des mises à jour dans cette table ou uniquement des insertions ? Je crois que c'est la seconde hypothèse mais il vaut mieux que vous le confirmiez.

Si c'est le cas, créez une table jumelle et essayez vos requêtes dessus :

Code :

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

1
2
3
4
5
6
CREATE TABLE SPV_LOG_BOOK_2
COMPRESS
PCTFREE 0
NOLOGGING
AS
SELECT * FROM SPV_LOG_BOOK;

**FSiebert** · 04/07/2012, 09h14

Bonjour à tous,

Un petit message pour dire que je ne suis pas parti comme un voleur sans reconnaissance

Actuellement je travaille sur d'autres priorités donc ce sujet est quelque peu en suspens. Je vous remercie encore pour toutes vos réponses et je ne manquerai pas de revenir vers vous prochainement.

Bonne continuation.

[TUNING] Jointure après agrégat

SQL Oracle

Discussions similaires

Partager

Partager