Discussion :

[T@kniX]

Bonjour,

Mon cas me paraît assez classique mais je ne trouve pas d'informations pertinentes (soit je ne sais pas chercher soit il n'y a pas vraiment de solution...).

Désolé pour la longueur de message mais j'essaye d'expliquer au mieux mon problème ainsi que les différentes pistes que j'ai pu trouver.
Merci à ceux auront le courage de me lire.

Je ne suis pas un spécialiste d'Oracle, je me suis pas mal documenté donc je vais parler de partitionnement, vue matérialisée, etc. mais je ne suis pas certain d'avoir tout bien compris, n'hésitez pas à me reprendre et apporter des précisions.

Pour faire court : j'ai des logs stockés en base de données ; les volumétries estimées : 3 millions d'enregistrements par jour, on conserve 15 jours de logs soit 45 millions.

La structure n'a rien d'exotique :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE "SPV_LOGS"
  (
    "LOG_ID" NUMBER NOT NULL ENABLE,
    "LOG_DATE" TIMESTAMP (6),
    "EVENT_ID"  NUMBER,
    "LOGGER"    VARCHAR2(50 CHAR),
    "IN_DATA"   VARCHAR2(500 CHAR),
    "LOG_LEVEL" VARCHAR2(5 CHAR),
    "MESSAGE" CLOB,
    CONSTRAINT "PK_SPV_LOGS" PRIMARY KEY ("LOG_ID") ENABLE
  );
CREATE UNIQUE INDEX "PK_SPV_LOGS" ON "SPV_LOGS" ("LOG_ID");

Je veux afficher les logs sur une IHM JEE (style tableau paginé avec des filtres de recherche, etc.) avec évidemment les derniers logs sur la première page.

Le problème est que le tri nécessite toujours un full scan un moment ou un autre et sur 45 millions de lignes...

J'ai essayé de réfléchir à des techniques pour optimiser ça mais je ne suis pas certain qu'il y a une solution... il faudra de toute façon toujours parcourir la table un moment ou un autre à mon avis.

Je pense que de toute façon il faudra un filtre en amont : par exemple on demande de définir le jour à afficher (passage de 45 millions à 3 millions).
Dans un tel cas, est-ce qu'il pourrait être intéressant de partitionner la table ? Avec une partition par jour ? Si oui, comment paramétrer pour avoir un espèce de rolling sur les partitions ? Est-ce qu'il est nécessaire d'avoir un tablespace par partition ? Quels sont les impacts d'utiliser un seul tablespace ?

Ensuite, en admettant que cette solution soit déjà une bonne première étape, trier les 3 millions d'enregistrements du jour reste encore un peu long. Est-ce que d'autres astuces pourraient être mises en place ?

J'ai pensé à une vue matérialisée qui aurait en plus une colonne contenant la valeur du rownum après tri.

Code :

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

1
2
3
4
SELECT ROWNUM, tmp.* FROM (
  SELECT * FROM SPV_LOGS
  ORDER BY LOG_ID DESC
) tmp;

On pourrait ainsi faire directement des requêtes "WHERE ROWNUM BETWEEN...".
Dans ce cas, comment paramétrer le refresh ? Sachant qu'il ne faut peut-être pas le faire au commit, ça serait dommage que les logs impacts les performances globales de l'application.
Est-il possible de faire un système pour rafraîchir seulement le jour courant (aucun log n'est ajouté à un jour passé) ?

Merci d'avance pour vos réponses.

Si vous avez d'autres suggestions n'hésitez pas, je trouve ce genre de problématique très intéressant et j'aimerai en apprendre davantage.

Cordialement,
T@kniX

[Scriuiw]

Le partionning, vu ton nombre de données me parait juste démesurer

Penses plus index. Par exemple ajout d'une date de trace indexée qui te permettrait d'effectuer tes recherches par jour ou période.

A voir selon les filtres de ton IHM pour ajouter d'autres colonnes indexés (Type de traitement, statut, Nom, ...)

[T@kniX]

Merci pour ta réponse

Effectivement, je suis en train de regarder ce que ça donne d'indexer un TRUNC(LOG_DATE, 'D') pour pouvoir rechercher rapidement par jour
Par contre, je ne voudrais pas que les index impactent trop les insertions (il ne faut pas que les logs impactent les performances globales de l'application et vu les volumétries j'ai un peu peur de mettre trop d'index).
J'ai effectivement prévu un index sur EVENT_ID (on va fréquemment recherché les logs attachés à un événement), probablement un sur la partie DAY de la date.
Pour le reste je ne sais pas trop : le level (comme log4j : error, warn, etc.) me paraît trop peu discriminant, le logger sera peu utilisé comme filtre (plutôt dans l'autre sens : on a un log, on a regarde la classe origine).
Donc en fait je pense qu'indexer l'eventId, la partie day de la date (et la PK) est un compromis pas mal, qu'en pensez-vous ?

Concernant le partitionnement, je pensais justement que c'était fait pour des gros volumes. Pourquoi ça te paraît démesuré ?

[Scriuiw]

45 millions de lignes n'est pas un volume très conséquent
si tu en avais 10x plus par mois, le partitionnement serait un bon compromis (enfin je pense, place aux experts qui en ont eue l'utilité)

Concernant l'index date, je pensais d'abord à un index sur la date sans TRUNC, après si tu as une utilité particulière à avoir accès aux traces de chaque Lundi, Mardi, etc... alors tu peux créer un index fonction

[Mohamed.Houri]

Partitionner votre table par LOG_DATE (un jour par partition; faire quand même attention au log_date timestamp) me semble bien convenir à votre problématique de performance pourvu que les selects sur votre table fassent toujours référence à LOG_DATE dans leurs clause where permettant de faire du "partition pruning"

Si en plus vous créerai un index local approprié vous pourriez également gagner en performance.

Quant au tri, je vous conseille de prendre beaucoup de précaution lors de l’utilisation du rownum ; en effet trop souvent le rownum vous fait passer du mode ALL_ROWS au mode FIRST_ROWS_N qui n’est pas sans conséquence sur la performance


Si votre table est déjà en production voici un lien vous donnant un exemple de comment faire passer une table non partitionnée en une table partitionnée en utilisant dbms_redefinition

[skuatamad]

Quant au tri, je vous conseille de prendre beaucoup de précaution lors de l’utilisation du rownum ; en effet trop souvent le rownum vous fait passer du mode ALL_ROWS au mode FIRST_ROWS_N qui n’est pas sans conséquence sur la performance

Ben c'est un peu le but dans ce genre de requête, je dirais même utiliser le hint first_rows(n) dans la requête si besoin

On pourrait ainsi faire directement des requêtes "WHERE ROWNUM BETWEEN...".

Il ne faut pas utiliser BETWEEN, mais 2 étapes d'abord le filtre < puis le filtre >=, ce que tu veux voir dans le plan c'est le STOPKEY.
Ci dessous un exemple, note particulièrement la différence de consistent gets (lecture logique) ainsi que la colonne ROWS grâce au STOPKEY :

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
SQL> create table t (
  2    id, c,
  3    CONSTRAINT PK PRIMARY KEY (ID) ENABLE
  4    ) as
  5  select level, dbms_random.string('u',10)
  6    from dual
  7  connect by level <= 100000
  8  /
 
Table created.
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> set autot trace
SQL> 
SQL> select *
  2    from (select t.*, rownum as rn
  3            from (select t.* from t order by id desc) t
  4           where rownum < 101
  5         )
  6   where rn >= 50
  7  /
 
51 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1486502395
 
---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |   100 |   198K|     3   (0)| 00:00:01 |
|*  1 |  VIEW                          |      |   100 |   198K|     3   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |      |       |       |            |          |
|   3 |    VIEW                        |      |   100 |   196K|     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    |   100K|  1562K|     3   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN DESCENDING| PK   |   100 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN">=50)
   2 - filter(ROWNUM<101)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       2327  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         51  rows processed
 
SQL> 
SQL> select t.*
  2    from (select t.*, rownum as rn from t order by id desc) t
  3   where rn between 50 and 100
  4  /
 
51 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 501813784
 
--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |   100K|   193M|   500   (1)| 00:00:07 |
|*  1 |  VIEW                         |      |   100K|   193M|   500   (1)| 00:00:07 |
|   2 |   COUNT                       |      |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T    |   100K|  1562K|   500   (1)| 00:00:07 |
|   4 |     INDEX FULL SCAN DESCENDING| PK   |   100K|       |   210   (1)| 00:00:03 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN"<=100 AND "RN">=50)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        507  consistent gets
          0  physical reads
          0  redo size
       2327  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         51  rows processed
 
SQL>

Après comment sont gérés les purges, il pourraient être intéressant de les associer à une réorganisation de la table afin de limiter le HWM.
Donc peut être utiliser après la purge le package DBMS_REDEFINITION ou ALTER TABLE MOVE ou SHRINK...

Je laisse les DBA intervenir sur les différentes possibilités de réorganisation.

[Mohamed.Houri]

Ben c'est un peu le but dans ce genre de requête, je dirais même utiliser le hint first_rows(n) dans la requête si besoin

Ce que je veux dire, c'est qu'il faut toujours préférer le mode ALL_ROWS au mode FIRST_ROWS_N (je ne parle même pas du mode FIRST_ROWS). Le CBO a été intensivement testé sous le mode ALL_ROWS alors qu'il n'a pas été aussi intensivement testé sous le mode FIRST_ROWS_N. Avec ce dernier mode, plusieurs bugs(ou choix contestés du CBO) ont été découverts. Vous allez me dire oui mais moi je n'ai pas touché au mode du CBO; certes, mais il suffit d'introduire la clause WHERE ROWNUM et le CBO (vous pouvez le voir via le 10053) particulièrement lorsqu'il y a un ORDER BY va évaluer le cout de la requête sous le mode FIRST_ROWS_N

Et comme le hasard fait parfois bien les choses, vous avez vous même via votre exemple, montré cet aspect du FIRST_ROWS_N et du ORDER BY. Observez bien votre 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

Execution Plan
----------------------------------------------------------
Plan hash value: 1486502395

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |   100 |   198K|     3   (0)| 00:00:01 |
|*  1 |  VIEW                          |      |   100 |   198K|     3   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |      |       |       |            |          |
|   3 |    VIEW                        |      |   100 |   196K|     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    |   100K|  1562K|     3   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN DESCENDING| PK   |   100 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=50)
   2 - filter(ROWNUM<101)

Votre requête contient un order by mais le CBO a préféré utilisé un INDEX FULL SCAN pour éviter l'opération ORDER BY. Cela, il le fait quelque soit le cout de l'accès à cet index. J'ai essayé d'expliquer cela ici. Si vous avez du temps et de la volonté, essayez de faire un 10053 "trace file" de votre requête et regardez les étapes par lesquelles est passé le CBO.

[mnitu]

...
Le CBO a été intensivement testé sous le mode ALL_ROWS alors qu'il n'a pas été aussi intensivement testé sous le mode FIRST_ROWS_N. ... Vous allez me dire oui mais moi je n'ai pas touché au mode du CBO; certes, mais il suffit d'introduire la clause WHERE ROWNUM et le CBO (vous pouvez le voir via le 10053) particulièrement lorsqu'il y a un ORDER BY va évaluer le cout de la requête sous le mode FIRST_ROWS_N
...

Je suis très curieux d’apprendre votre démonstration qui soutien vos affirmations.
Par ailleurs votre lien parle du mode FIRST_ROWS vers ALL_ROWS et non pas du mode FIRST_ROWS_N vers ALL_ROWS ce qui n'est pas du tout la même chose (comme vous le savez assez bien).

[Waldar]

Partitionner votre table par LOG_DATE (un jour par partition; faire quand même attention au log_date timestamp) me semble bien convenir à votre problématique de performance pourvu que les selects sur votre table fassent toujours référence à LOG_DATE dans leurs clause where permettant de faire du "partition pruning"

Vous pouvez même envisager une table organisée en index partitionnée, les données seront ainsi déjà triées sur la clef primaire.

[skuatamad]

Mohamed,

Je ne conteste pas que le mode ALL_ROWS soit très souvent préférable au mode FIRST_ROWS_N, ni que l'utilisation de rownum oriente le CBO. Cependant dans un contexte de requête TOP N je vois mal comment éviter l'ORDER BY et le rownum.
Le but de mon post était de montrer, sur un exemple certes simpliste, qu'en fonction de l'utilisation faite de rownum, le plan était différent.

Concernant votre lien il ne correspond pas à la situation actuelle car les requêtes qui y sont proposées ne sont pas du type TOP N.
Et oui dans un contexte TOP N, j'ai tendance à souhaiter que l'optimiseur switch vers le mode FIRTS_ROWS_N. En tout cas il me semble que le mode FIRST_ROWS_N a été créé plus particulièrement pour ce type de requêtes.

Cependant je prendrai évidemment le temps d'osculter au 10053 les différents types de requêtes autour du sujet, même si je ne suis pas très à l'aise dans son interprétation.

il faut toujours préférer le mode ALL_ROWS au mode FIRST_ROWS_N

Cette affirmation ne peut qu'être en contradiction avec un des célèbres mantras de Tom Kyte

never say never, never say always, I always say

Par ailleurs je suis assez intéressé par la proposition de Waldar sur les tables organisées en index, car, même si je n'en ai pas d'expérience, c'est justement à cette option que je pensais cet aprem.

[pachot]

Bonjour,

Vous voulez lire les premiers enregistrements dans l'ordre décroissant de leur LOG_ID ?
- Il ne faut pas trier mais utiliser l'index (qui est trié)
- il faut préciser à Oracle que vous n'allez pas tout lire -> c'est le FIRST_ROWS()
- il faut de toute façon pas tout lire, donc limiter avec ROWNUM < ... qui am'ne le FIRST_ROWS() de toute façon

L'index sera super optimal puisque les enregistrements arrivent en ordre croissant de LOG_ID. c'est une bon candidat pour une IOT d'ailleurs.

Très bon candidat pour partitionnement aussi afin de purger les anciennes données de manière efficace.

Par contre, pour partitionner par LOG_DATE, il faudrait que LOG_DATE soit dans la clé.
sinon partitionner sur LOG_ID mais il faut savoir quelles dates correspondent.

Une clé du genre (LOG_DATE,LOG_ID) permettrait celà.

Mais pas besoin d'avoir plusieurs tablespaces.

Cordialement,
Franck.