Problème de performance avec un to_date dans where clause

[Gaadek]

Bonjour à tous,

J'ai un soucis de performance avec une de mes requêtes SQL.

Cette requête exécute une recherche dans une table d'audit contenant une date de validité afin de construire une vue.

Les données sources sont stockées dans une table:

Code :

1
2
3
4
5
id       val01        end_ts
1        1            01-JAN-2011
2        3            03-MAR-2011
3        5            15-AUG-2050
4        3            15-AUG-2050

Dans l'exemple ci-dessus, end_ts représente la date de validité.
Les données courantes sont celles ayant end_ts = 15-AUG-2050 (une date future qui est la même pour tous les records courants - me demandez pas pourquoi cette date...), soit les records 3 & 4.

La vue ne doit afficher que les données courantes et leur appliquer une fonction d'aggrégation (une somme).

Afin de ne récupérer que les données courantes, je dois donc filtrer les données, ce que je fais comme cela:

Code :

1
2
3
SELECT sum(val01) 
FROM ma_table 
WHERE end_ts = to_date('15-AUG-2050', 'DD-MMM-YYYY');

Le soucis, c'est que l'utilisation de la fonction to_date réduit de manière trop importante les performances de ma requête.
Quand j'exécute la requête sans where clause, quelques secondes suffisent.
Par contre, avec where clause, plusieurs dizaines de minutes sont nécessaires (j'ai environ 400 000 records dans ma_table)

Je commence donc à envisager de ne plus faire de vue, mais une seconde table, rafraichie avec une procédure SQL (qui me permettrait de stocker le résultat de to_date('15-AUG-2050', 'DD-MMM-YYYY') dans une variable, puis d'utiliser cette variable dans la where clause)

Cependant, me gène de ne plus avoir de vue, je serais obligé de forcer un rafraichissement (job schedulé), et surtout, je me demande s'il n'y a pas de possibilité pour faire autrement!

Merci par avance pour votre aide

[pacmann]

Salut !

Plusieurs dizaines de minutes pour 400K lignes, ça parait monstrueux...

Par contre, to_date en soi ne ralentit pas de requêtes (essaie par exemple de faire la même sans clause where).

Y a-t-il beaucoup de lignes courante par rapport au total des lignes ? Serait-il peut être souhaitable de poser un index sur la date ? Pourrais-tu donner le plan d'exécution de ta requête ?

[skuatamad]

Franchement la même que Pacmann, il faut au moins un plan (set autot trace dans sqlplus) voir un tkprof

Sinon c'était juste pour préciser que :

Je commence donc à envisager de ne plus faire de vue, mais une seconde table, rafraichie avec une procédure SQL (qui me permettrait de stocker le résultat de to_date('15-AUG-2050', 'DD-MMM-YYYY') dans une variable, puis d'utiliser cette variable dans la where clause)

Cependant, me gène de ne plus avoir de vue, je serais obligé de forcer un rafraichissement (job schedulé), et surtout, je me demande s'il n'y a pas de possibilité pour faire autrement!

Ca s'appelle une vue matérialisée (mais c'est plus pour ta culture... il n'y a aucune raison pour que la requête présentée sur 400K lignes prennent 10 minutes...)

[Gaadek]

Bonjour,

Merci pour vos réponses!

Alors, je vais d'abord répondre à Pacman:

Par contre, to_date en soi ne ralentit pas de requêtes (essaie par exemple de faire la même sans clause where).

Justement, c'est exactement le problème: j'ai identifié le to_date comme facteur limitant dans ma requête, cf mon post original:

Quand j'exécute la requête sans where clause, quelques secondes suffisent.

Pour répondre à skuatamad:
Merci, je sais ce qu'est une vue matérialisée (ou snapshot) . Ceci dit, je ne suis pas certains qu'on puisse utiliser une variable.

Je m'explique: une vue matérialisée exécute une requête SQL a une fréquence donnée (tous les jours,...) Bref, je vais pas expliquer plus dans les détails.

En gros, ma vue matérialisée va exécuter le même script que ma vue Oracle, sauf que je vais le faire à un horaire prédéfini. Ma vue matérialisée sera beaucoup plus rapide en accès, par contre, il va me falloir toujours autant de temps pour la mettre à jour...

Par contre, si je créé une table (non matérialisée), rafraichie par un script PL/SQL, ça me semble plus efficace, mais pas très propre sur la méthode:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
declare
  max_ts date := to_date('15-AUG-2050', 'DD-MON-YYYY');
begin
  begin
    DROP TABLE mon_autre_table;
  exception
    when OTHERS then
      NULL;
  end;
 
  CREATE TABLE mon_autre_table AS 
    SELECT pt, sum(val01) 
    FROM ma_table 
    WHERE end_ts = max_ts
    GROUP BY pt
  ;
end;

Quoiqu'il en soit, je vais poster le plan d'exécution dès demain!

[startout]

Bonjour,

Et la création d'un index sur la fonction to_date(end_ts) vous l'avez peut-être déjà fait ?

[Gaadek]

Envoyé par startout

Bonjour,

Et la création d'un index sur la fonction to_date(end_ts) vous l'avez peut-être déjà fait ?

Bonjour,

Je ne comprends pas votre remarque.

La table d'origine contient la variable "end_ts", je ne vois pas comment je paux mettre un index sur "to_date(end_ts)"

Si vous pouviez m'aider sur ce point!

[ojo77]

Bonjour ?

Quels sont les plans d'exécution ?
Si vous avez un index sur le champs end_ts quelle est sa taille et son type dans user_segments ? De quand datent les statistiques sur les différents objets impactés ?

[Yanika_bzh]

A partir du moment ou vous transformez la colonne indexée dans votre clause where, vous perdrez l'utilisation des index (convert, upper, ...)
Vous devez utiliser la valeur native pour que l'index soit utilisé

Bon courage

[Gaadek]

Bonsoir à tous,

Voici la requête utilisée pour l'explain plan:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  a.id,
        c.var_01
FROM    ma_table_a a,
        ma_table_b b,
        ma_table_c c
WHERE   a.id = 1
    AND a.id = b.id
    AND a.id = c.id
    AND a.sub_id = b.sub_id
    AND b.collected_data = 'Y'
    AND c.end_ts = to_date (3000000, 'J')
--    AND c.end_ts > sysdate
;

Donc, premier explain plan avec le to_date dans la where clause.
Temps d'exécution de la requête: 1 min et 52 sec.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT STATEMENT CHOOSE
    cost: 14 155 bytes: 3 894 cardinality: 66
  6 - Hash JOIN
      cost: 14 155 bytes: 3894 cardinality: 66
    4 - Nested loops
        cost: 13 914 bytes: 126 132 cardinality: 2 742
      2 - TABLE access BY INDEX rowid ma_table_a
         cost: 1 bytes: 19 cardinality: 1
        1 - INDEX UNIQUE scan ma_table_a_uk_idx
            cardinality: 1
      3 - INDEX range scan non-UNIQUE ma_table_b_nfk_idx
          cost 13 913 bytes 74 034 carinality: 2 742
    5 - TABLE access full ma_table_c
        cost: 237 bytes 1 088 620 cardinality 83 740

Second explain plan sans le to_date dans la where clause.
Temps d'exécution de la requête: 454 msecs.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT STATEMENT CHOOSE
    cost: 15 713 bytes: 3 677 508 cardinality: 72 108
  6 - Hash JOIN
      cost: 15 713 bytes: 3 677 508 cardinality: 72 108
    1 - TABLE access full ma_table_b
        cost: 237 bytes: 1088308 cardinality: 83716
    5 - Nested loops
        cost: 13 914 bytes: 96 058 110 cardinality: 2 527 845
      3 - TABLE access BY INDEX rowid ma_table_a
         cost: 1 bytes: 19 cardinality: 1
        2 - INDEX UNIQUE scan ma_table_a_uk_idx
            cardinality: 1
      4 - INDEX range scan non-UNIQUE ma_table_c_nfk_idx
          cost: 13 913 bytes 48 029 055 cardinality: 2 527 845

Dernier explain plan sans le to_date dans la where clause, mais avec un sysdate.
Temps d'exécution de la requête: 563 msecs.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT STATEMENT CHOOSE
    cost: 15 958 bytes: 4 248 177 cardinality: 72 003
  6 - Hash JOIN
      cost: 15 958 bytes: 4 248 177 cardinality: 72 003
    1 - TABLE access full ma_table_b
        cost: 237 bytes: 1 088 308 cardinality: 83 716
    5 - Nested loops
        cost: 13 914 bytes: 116 110 808 cardinality: 2 524 148
      3 - TABLE access BY INDEX rowid ma_table_a
         cost: 1 bytes: 19 cardinality: 1
        2 - INDEX UNIQUE scan ma_table_a_uk_idx
            cardinality: 1
      4 - INDEX range scan non-UNIQUE ma_table_c_nfk_idx
          cost: 13 913 bytes 68 151 996 cardinality: 2 524 148

[pacmann]

Salut,

Juste par curiosité, tu as lancé les requêtes et les explain plan avec quel outil ?

Question bonus : tu parlais de "sum" au départ... pourquoi n'as tu pas cherché les plans d'exécution des requêtes "sum" ?
(Et au passage tu parlais de plusieurs dizaines de minutes... mais finalement ça fait moins de 2 minutes ?)

@yanika et startout : ce n'est pas sur la colonne qu'on applique une fonction ici, c'est sur une constante... donc aucun problème par rapport au potentiel index.

[ojo77]

La requête qui pose problème est la suivante

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT  a.id,
        c.var_01
FROM    ma_table_a a,
        ma_table_b b,
        ma_table_c c
WHERE   a.id = 1
    AND a.id = b.id
    AND a.id = c.id
    AND a.sub_id = b.sub_id
    AND b.collected_data = 'Y'
    AND c.end_ts = to_date (3000000, 'J')
--    AND c.end_ts > sysdate
;

Visiblement elle induit un FTS sur c. On peut donc en déduire qu'il n'y a pas d'index saur c.end_ts ou que celui ci n'est pas suffisamment sélectif pour la valeur to_date(3000000,'J').
La cardianlité estimée est de 83 740 lignes pour cette contrainte : est-ce réellement le cas ?

En effet jusqu'en 11gR2 l'optimiseur n'est pas au courant affinités entre colonnes et il va donc estimer la sélectivité de n colonne comme le produit des sélectivités individuelles des colonnes. C'est une multiples raisons qui peut pousser l'optimiseur à prendre un plan d'exécution qui n'est pas optimal.

On en revient donc au satistiques et aux index.

Existe-t-il un index sur c.end_ts ? ou mieux sur c.id et c.end_ts ?
Quelles sont les statistiques de la table, des index et des colonnes ?
Quand et comment ont-elles été calculées ?
Et surtout reflètent-elles suffisamment la réalité ?

Que se passe-t-il si la requête est réécrite comme suit

Code :

1
2
3
4
5
6
7
8
9
SELECT  a.id,
        c.var_01
FROM    (SELECT id, sub_id ma_table_a WHERE id=1) a,
        INNER JOIN ma_table_b b ON (a.id=b.id AND a.sub_id=b.sub_id AND b.id=1),
        INNER JOIN ma_table_c c ON (a.id=c.id AND c.id=1)
WHERE   b.collected_data = 'Y'
    AND c.end_ts = to_date (3000000, 'J')
--    AND c.end_ts > sysdate
;

[mnitu]

Les données provient des tables a et c seulement. Les filtre sont posées sur la table a la table b et la table c. Le filtre sur la table a est très fort, valeur unique de la clé primaire (a.id = 1). Le filtre sur la table b est très peu sélective (col = ‘Y’). Le filtre sur la table c (date = 15-08-3051) utilise une date bidon pour remplacer un null et a pour conséquence d’introduire une non-uniformité des données très forte. Sans histogrammes sur cette colonne l’optimiseur part tranquillement dans les choux.

La requête qui n’utilise pas de date ne peut se baser que sur le filtre de la table b et le plan attaque cette table en full. La requête qui utilise sysdate implique le même plan due à la forte non-uniformité des données de la collone c.end_ts autrement dit il est plus intéressant de commencer avec la table b que avec la table c

Bref, je n’ai pas vu de version de base Oracle mais je pense que c’est une base Oracle 9. Il est très probable que recalculer les statistiques avec génération des histogrammes là où besoin est devrait améliorer les choses.

[Gaadek]

Envoyé par pacmann

Salut,

Juste par curiosité, tu as lancé les requêtes et les explain plan avec quel outil ?

Salut, Les explain plan ont été exécuté avec TOAD.

Envoyé par pacmann

Question bonus : tu parlais de "sum" au départ... pourquoi n'as tu pas cherché les plans d'exécution des requêtes "sum" ?
(Et au passage tu parlais de plusieurs dizaines de minutes... mais finalement ça fait moins de 2 minutes ?)

J'ai simplifié la requête. Ce qui m'intéresse dans le cas présent, c'est l'impact de l'utilisation du to_date dans la where clause.

Envoyé par ojo77

On en revient donc au satistiques et aux index.

Existe-t-il un index sur c.end_ts ? ou mieux sur c.id et c.end_ts ?
Quelles sont les statistiques de la table, des index et des colonnes ?
Quand et comment ont-elles été calculées ?
Et surtout reflètent-elles suffisamment la réalité ?

Salut, il existe bien un index sur end_ts, mais non utilisé dans mon cas car c'est un index concaténé (sur plusieurs variables qui ne sont pas toutes utilisées). Mais cela me donne une piste, ça peut valoir le coup d'ajouter une ou deux variables dans la requête pour forcer l'utilisation de l'index.

Les stats de la table sont mises à jour tous les jours. Je ne sais pas quelles sont les informations dont vous avez besoin, mais je peux déjà vous donner ça:
AVG_ROW_LEN 76
AVG_SPACE 1455
AVG_SPACE_FREELIST_BLOCKS 0
BACKED_UP N
BLOCKS 697 312
BUFFER_POOL DEFAULT
CACHE N
CHAIN_CNT 4 985
CLUSTER_NAME
CLUSTER_OWNER
COMPRESSION DISABLED
DEGREE 1
DEPENDENCIES DISABLED
DURATION
EMPTY_BLOCKS 91
FREELISTS
FREELIST_GROUPS
GLOBAL_STATS YES
INITIAL_EXTENT 80 Kb
INI_TRANS 1
INSTANCES 1
IOT_NAME
IOT_TYPE
LAST_ANALYZED 05/16/11 20:55:54
LOGGING YES
MAX_EXTENTS 2 147 483 645
MAX_TRANS 255
MIN_EXTENTS 1
MONITORING NO
NESTED NO
NEXT_EXTENT
NUM_FREELIST_BLOCKS 0
NUM_ROWS 137 122 804
OBJECT_ID_TYPE
OWNER RXC
PARTITIONED NO
PCT_FREE 10
PCT_INCREASE
PCT_USED
ROW_MOVEMENT DISABLED
SAMPLE_SIZE 34 280 701
SECONDARY N
SKIP_CORRUPT DISABLED
TABLESPACE_NAME RXC_RESP_TSPA
TABLE_LOCK ENABLED
TABLE_NAME RESPONSES
TABLE_TYPE
TABLE_TYPE_OWNER
TEMPORARY N
USER_STATS NO

Envoyé par mnitu

Le filtre sur la table c (date = 15-08-3051) utilise une date bidon pour remplacer un null et a pour conséquence d’introduire une non-uniformité des données très forte. Sans histogrammes sur cette colonne l’optimiseur part tranquillement dans les choux.

Salut, malheureusement, la date n'est pas bidon. Je travaille sur le système Oracle Clinical, qui utilise une base Oracle. Oracle Clinical a besoin de conserver toute donnée saisie dans le système. Pour cela, il utilise une date de fin de validité (end_ts) qui est fixée à une date future (15AUG3051) pour indiquer au système que c'est la valeur courante. Grace à ce fonctionnement, on est capable de sortir toutes les données saisies dans une fourchette de temps donnée. Je vais pas m'étendre dur le sujet, il faut considérer qu'il y a une véritable utilité à ce fonctionnement (domaine de la recherche clinique, donc fort besoin en audit etc)

Envoyé par mnitu

Bref, je n’ai pas vu de version de base Oracle mais je pense que c’est une base Oracle 9. Il est très probable que recalculer les statistiques avec génération des histogrammes là où besoin est devrait améliorer les choses.

Effectivement, je confirme, c'est une base Oracle 9i. On devrait bientôt passer en 11, mais en attendant, faut bien s'occuper

[ojo77]

ok, on la refait doucement

Pour obtenir les statistiques sur les tables :

Code :

1
2
3
SELECT * 
FROM dba_tables 
WHERE table_name IN ('MA_TABLE_A','MA_TABLE_B','MA_TABLE_C' );

Pour obtenir les statistiques sur les index

Code :

1
2
3
4
SELECT * 
FROM dba_indexes 
WHERE table_name IN ('MA_TABLE_A','MA_TABLE_B','MA_TABLE_C' )
ORDER BY table_name ;

Pour obtenir les obtenir les statistiques sur les colonnes impactées

Code :

1
2
3
4
5
SELECT * 
FROM dba_tab_columns
WHERE table_name IN ('MA_TABLE_A','MA_TABLE_B','MA_TABLE_C' )
AND column_name IN ('ID','END_TS','VAR_01','SUB_ID','COLLECTED_DATA')
ORDER BY table_name ;

Pour obtenir les obtenir les histogrammes sur les colonnes impactées

Code :

1
2
3
4
5
SELECT * 
FROM dba_tab_histograms
WHERE table_name IN ('MA_TABLE_A','MA_TABLE_B','MA_TABLE_C' )
AND column_name IN ('ID','END_TS','VAR_01','SUB_ID','COLLECTED_DATA')
ORDER BY table_name ;

[mnitu]

Envoyé par mnitu

...Le filtre sur la table c (date = 15-08-3051) utilise une date bidon pour remplacer un null et a pour conséquence d’introduire une non-uniformité des données très forte. Sans histogrammes sur cette colonne l’optimiseur part tranquillement dans les choux.
...

Je viens de reformuler:
Le filtre sur la table c (date = 15-08-3051) utilise une date dans le futur pour remplacer un null et a pour conséquence d’introduire une non-uniformité des données très forte. Sans histogrammes sur cette colonne l’optimiseur part tranquillement dans les choux.

Voilà, maintenant relisez.