Aide sur Optimisation requête

**shadeoner** · 29/08/2008, 14h56

Bonjour à tous,

je suis actuellement en train d'essayer d'optimiser une requête qui est plutôt lourde :

Code :

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

1
2
3
4
5
6
SELECT AN.id_presto, BLG.id_logiciel, BLG.aan_id, TRUNC(BLG.blg_date, 'MM') as blg_date, count(*) as total
    FROM T_BRIDGELOG_BLG BLG, ANNONCE AN
    WHERE BLG.id_annonce = AN.id_annonce
      AND BLG.id_logiciel > 0
      AND TRUNC(blg_date, 'MM') in ( TRUNC(sysdate, 'MM'), TRUNC( ADD_MONTHS(SYSDATE, - 1 ) ) )
      GROUP BY id_presto, id_logiciel, aan_id, TRUNC(BLG.blg_date, 'MM')

SELECT STATEMENT, GOAL = ALL_ROWS Cost=26541 Cardinality=12383 Bytes=1164002
HASH GROUP BY Cost=26541 Cardinality=12383 Bytes=1164002
NESTED LOOPS Cost=26270 Cardinality=12383 Bytes=1164002
TABLE ACCESS FULL Object owner=PRESTO Object name=T_BRIDGELOG_BLG Cost=1490 Cardinality=12383 Bytes=619150
TABLE ACCESS BY INDEX ROWID Object owner=PRESTO Object name=ANNONCE Cost=2 Cardinality=1 Bytes=44
INDEX UNIQUE SCAN Object owner=PRESTO Object name=PK_ANNONCE Cost=1 Cardinality=1

table ANNONCE environs 5 millions de lignes
table BLG = 830 000 lignes

Index en pk sur id_annonce
Index sur id_presto
Index sur trunc(blg_date)

Le temps d'execution est d'environs 40 sec.

1/ Comment puis-je l'optimiser ?

2/ Comment se lit le plan d'execution, où puis-je trouver une doc là dessus ?

Merci d'avance pour votre aide

**mnitu** · 29/08/2008, 15h44

Envoyé par shadeoner

...

2/ Comment se lit le plan d'execution, où puis-je trouver une doc là dessus ?

Merci d'avance pour votre aide

Using explain plan

**shadeoner** · 29/08/2008, 16h46

Bonjour,

merci pour ce lien, j'aurais préféré en français car c'est vraiment particulier et très technique.

Sinon j'ai rien trouvé sur le "HASH GROUP BY"

**mnitu** · 29/08/2008, 17h15

Laise tomber le HASH GROUP BY. Peux tu donner quelques détails, volumétrie, temps d’exécution constaté, description des tables des indexes, etc.

Sinon à ce que je comprends ton plan te dit que

il fait un full scan de la table T_BRIDGELOG_BLG
pour chaque ligne il récupère id_presto de la table ANNONCE via l’index unique
ensuite il construit les groups

PS. Est-ce que la condition n'est pas

Code :

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

1
2
 
AND TRUNC(blg_date, 'MM') IN ( TRUNC(sysdate, 'MM'), TRUNC( ADD_MONTHS(SYSDATE, - 1 ), 'MM' ) )

**shadeoner** · 29/08/2008, 17h21

Merci pour ton aide, je mets les infos dans l'en-tête.

**McM** · 30/08/2008, 12h15

Malgré ton index sur trunc(blg_date, 'MM'), Oracle ne le prend pas..
Est ce que les stats sont à jour.
Est ce que si tu mets un = au lieu du IN (pour tester avec 1 seule date de param), oracle prend l'index ?
Si Oui, alors tu peux surement séparer la requete en 2 (OR ou UNION ALL)

**jmguiche** · 30/08/2008, 12h46

Pourquoi faire compliqué quand on peut faire simple ?

Plutot qu'une fonction sur ta date et un index fonction, tu peux utiliser un index simple sur la date et un between dans ta requette (voire même un simple

Code :

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

blg_date>= TRUNC( ADD_MONTHS(SYSDATE, - 1 ) )

s'il n'y a rien dans le futur dans la base).

Je pense qu'il est preferable d'éviter les index fonction quand c'est possible, le cout de maintenance est élevé.
Tu as en effet tres probablement un probleme de stat.

**shadeoner** · 30/08/2008, 18h51

Merci pour votre aide, en fait j'ai fais cet index car il n'utilisait pas l'index "normal" sur la date. Merci de vos conseils je vais tester tout ça dès lundi au boulot

**scheu** · 01/09/2008, 16h59

Avant même de regarder les indexes, le premier réflexe est toujours de regarder si les stats sont à jour ...

**shadeoner** · 02/09/2008, 10h20

Le problème est que je ne suis pas DBA, je ne sais pas trop comment on regarde si les stats sont à jours, je lance donc un compute statistics sans vraiment savoir si y'a besoin de le faire.

Les stats étaient donc déjà à jour.

Autre info, je remarque que l'index sur la date ne vaut rien puisque finalement, ma requête ramène 95% des lignes de la table BLG, j'ai donc utilisé la date sur la table BLG car j'ai moins de ligne à scanner et la date entre la table ANNONCE et BLG valent "presque" la même chose, en tout cas les deux me vont.

Malheureusement ma requête est toujours aussi lente.

Sachant que ma requête fera forcément une jointure sur les 95% des lignes de BLG (soit environs 800 000), est-ce qu'il est encore possible d'optimiser ? Ne vaut-il pas mieux passer par une vue matérialisée ?

**mnitu** · 02/09/2008, 11h13

Envoyé par shadeoner

Le problème est que je ne suis pas DBA, je ne sais pas trop comment on regarde si les stats sont à jours, je lance donc un compute statistics sans vraiment savoir si y'a besoin de le faire.

Pour voir si les statistiques sont à jour regarde last_analized (et autres colonnes) dans all_tables.

Les stats étaient donc déjà à jour.

Autre info, je remarque que l'index sur la date ne vaut rien puisque finalement, ma requête ramène 95% des lignes de la table BLG, j'ai donc utilisé la date sur la table BLG car j'ai moins de ligne à scanner et la date entre la table ANNONCE et BLG valent "presque" la même chose, en tout cas les deux me vont.

Malheureusement ma requête est toujours aussi lente.

Sachant que ma requête fera forcément une jointure sur les 95% des lignes de BLG (soit environs 800 000), est-ce qu'il est encore possible d'optimiser ? Ne vaut-il pas mieux passer par une vue matérialisée ?

Je ne pense pas !
Tu dit que la requête ramène environ 800 000 lignes et que les statistiques sont à jour mais l’optimiseur pense qu’il ramènera seulement 12383 lignes ce qui représente une différence d’un ordre de grandeur. Mauvais plan d’exécution signifie souvent différence entre les cardinalités estimés et celles réelles.
Tu peux utiliser une vue matérialisée tant que tu n’oublies pas que tu devrais payer quelque chose pour ça.

**shadeoner** · 02/09/2008, 11h24

Merci pour cette super info, (le all_table)

J'ai regardé la table a été analisée le 30/08 et comptait à cette époque 840 000 lignes, donc ça m'a l'air plutôt bien.

Pour la materialized view j'aimerais que ce soit la dernière chose à faire, j'essaye donc avant, par tout les moyens d'optimiser cette f**** requête.

Là je bloque je vois pas comment faire...

**mnitu** · 02/09/2008, 12h02

1) Peux tu faire

Code :

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

1
2
 
SQL> set autotrace traceonly explain

ensuite exécuter ta requête et poster le plan ?

**shadeoner** · 02/09/2008, 12h12

Alors pour info j'ai mis à jour ma requête par rapport à vos commentaires :

Elle devient donc :

Code :

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

1
2
3
4
5
6
7
 
SELECT AN.id_presto, BLG.id_logiciel, BLG.aan_id, TRUNC(BLG.blg_date, 'MM') AS blg_date, count(*) AS total
    FROM T_BRIDGELOG_BLG BLG, ANNONCE AN
    WHERE BLG.id_annonce = AN.id_annonce
      AND BLG.id_logiciel > 0
      AND blg_date >= TRUNC( ADD_MONTHS(SYSDATE, - 1 ), 'MM' )
      GROUP BY id_presto, id_logiciel, aan_id, TRUNC(BLG.blg_date, 'MM')

et j'ai aussi viré l'index sur trunc(blg_date)

Voici le plan que j'ai :

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%C
PU)|

--------------------------------------------------------------------------------
----

| 0 | SELECT STATEMENT | | 216K| 19M| | 122K
(1)|

| 1 | HASH GROUP BY | | 216K| 19M| 43M| 122K
(1)|

| 2 | HASH JOIN | | 216K| 19M| 13M| 117K
(1)|

| 3 | TABLE ACCESS FULL| T_BRIDGELOG_BLG | 216K| 10M| | 1481
(4)|

| 4 | TABLE ACCESS FULL| ANNONCE | 5602K| 235M| | 100K
(1)|

--------------------------------------------------------------------------------
----

Note
-----
- 'PLAN_TABLE' is old version

Ce que je comprends... c'est qu'il n'utilise pas l'index de id_annonce entre les deux tables

**scheu** · 02/09/2008, 14h24

Est-ce que les stats des indexes de la table sont calculées (vue all_indexes, colonne last_analyzed) ?

**shadeoner** · 02/09/2008, 14h34

Oui elles le sont, je l'ai précisé au dessus.

**mnitu** · 02/09/2008, 15h04

Envoyé par shadeoner

...

Ce que je comprends... c'est qu'il n'utilise pas l'index de id_annonce entre les deux tables

Ce n'est pas grave. Un index n'est pas toujours bon un full table scan n’est pas toujours mauvais. Par contre je note que maintenant les cardinalités sont correctes et que le plan d’exécution a changé d’un manière importante : HASH JOIN à la place de NESTED LOOP.
Peux tu indiquer les temps des exécutions actuelles ? Peux tu préciser ta version de base Oracle ?

**shadeoner** · 02/09/2008, 15h26

Oui mais ne devrait-il pas utiliser l'index de la table ANNONCE pour ne récupérer que celles qui sont jointe avec la table BLG (soit environs 400 000), car si je comprends bien il fait un full scan d'annonce (soit 5 millions de lignes)

Pour répondre à ta question environs 60 sec.

Pour ma culture : qu'est-ce qu'un NESTED LOOP et qu'est-ce qu'un HASH GROUP ?

Merci

**mnitu** · 02/09/2008, 16h16

Envoyé par shadeoner

...
Pour ma culture : qu'est-ce qu'un NESTED LOOP et qu'est-ce qu'un HASH GROUP ?

Merci

How the Query Optimizer Chooses Execution Plans for Joins

Cette requête s'exécute dans un batch ou dans une application ? Ce quoi le but de son optimisation ?

**shadeoner** · 02/09/2008, 16h16

j'ai crée une vue matérialisée, c'est la soluce facile... ça va très vite pour ma requête...
Mais je cherche toujours à optimiser ma requête pour comprendre d'où vient le noeud

Aide sur Optimisation requête

SQL Oracle

Discussions similaires

Partager

Partager