Aide sur Optimisation requête

Bonjour à tous,

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

Code:

---

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')

---

Citation:

---

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

Citation:

---

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

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"

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:

---

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

---

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

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)

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:

---

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.

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 :)

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

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 ?

Citation:

---

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.

Citation:

---

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.

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

1) Peux tu faire

Code:

---

1 2	SQL> set autotrace traceonly explain

---

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

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

Elle devient donc :

Code:

---

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 :

Citation:

---

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

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

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

Citation:

---

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 ?

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

Citation:

---

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 ?

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 :)