Discussion :

[lady701]

salut tout le monde

je dois optimiser une requête, j'ai effectué un explain plan et j'avoue je n'arrive pas à le décortiquer.
Avez vous des directives à me donner pour sa lecture?
actuellement la requête tourne en 1min38 et je dois l'optimier à moins de 30s

Ecoulé : 00 :01 :38.11

Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=18782 Card
=6450 Bytes=1161000)

1 0 SORT (ORDER BY) (Cost=18782 Card=6450 Bytes=1161000)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TD_ETABLISSEMENT
S' (Cost=18603 Card=6450 Bytes=1161000)

3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP MINUS
5 4 BITMAP MINUS
6 5 BITMAP INDEX (SINGLE VALUE) OF 'IDX_TDETA_DRA'
7 5 BITMAP INDEX (SINGLE VALUE) OF 'IDX_TDETA_ETAT_E
TAB'

8 4 BITMAP INDEX (SINGLE VALUE) OF 'IDX_TDETA_ETAT_ETA
B'





Statistiques
----------------------------------------------------------
86 recursive calls
0 db block gets
56891 consistent gets
47153 physical reads
0 redo size
13279 bytes sent via SQL*Net to client
718 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
91 rows processed


MERCI de votre précieuse aide

[13thFloor]

Ca va être chaud pour deviner la requête.
Voyons, ça doit commencer par un select...il y a un order by... on est en 9.2 ou peut être 10.1 ou encore 10.2, voire 11g...
Bref, je cale, trop peu d'informations.

Sinon, le FIRST_ROWS est parfois très consommateur de cpu en utilisant les index.
Que donne la requête (invisible) en mode ALL_ROWS ?

Que vaut la PGA_AGGREGATE_TARGET ?

[Ahmed AANGOUR]

Si t'es en 10g ou 11g et que t'as la licence pour le diagnostic et tuning pack, tu peux essayer de voir ce que te recommande le sql tuning advisor.

[yong321]

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=18782 Card
=6450 Bytes=1161000)

1 0 SORT (ORDER BY) (Cost=18782 Card=6450 Bytes=1161000)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TD_ETABLISSEMENT
S' (Cost=18603 Card=6450 Bytes=1161000)

3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP MINUS
5 4 BITMAP MINUS
6 5 BITMAP INDEX (SINGLE VALUE) OF 'IDX_TDETA_DRA'
7 5 BITMAP INDEX (SINGLE VALUE) OF 'IDX_TDETA_ETAT_E
TAB'

8 4 BITMAP INDEX (SINGLE VALUE) OF 'IDX_TDETA_ETAT_ETA
B'

Il est préférable d'avoir Oracle version. Mais ce plan semble avoir déjà un coût élevé sur les lignes 3-8. Essayez
ALTER SESSION SET "_b_tree_bitmap_plans" = false;

Yong Huang
(Excusez-moi pour mon mauvais français)

[pachot]

Bonjour,

Est-ce que FIRST_ROWS a un sens ? pas our les stats que tu donnes puisque tu donnes le temps de fetch de tous les enregistrements (et pas seulement du premier fetch)

Est-ce que la colonne qui correpsond à l'index IDX_TDETA_ETAT_ETAB contient des valeurs nulles ? Si la réponse est non, alors il faudrait la déclarer en not null afin que l'optimiseur évite de lire 2 fois cet index.

Bon, puisqu'on doit deviner la requête, c'est quelque chose comme ca ?
select ... from TDETA where ETAB <> valeur1 and DRA = valeur2

et ETAB n'est pas déclaré en NOT NULL donc il faut aller voir 2 fois l'index bitmap: 1 fois pour enlevel les valeurs 'valeur1' et une deuxième fois pour enlever les valeurs null.

Cordialement,
Franck.

[pachot]

Yong Huang
(Excusez-moi pour mon mauvais français)

Bonjour,
Tu es le Yong Huang de http://yong321.freeshell.org/oranotes/ ?
C'est un honneur de te voir sur ce forum

Cordialement,
Franck Pachot

[yong321]

Bonjour,
Tu es le Yong Huang de http://yong321.freeshell.org/oranotes/ ?
C'est un honneur de te voir sur ce forum

Cordialement,
Franck Pachot

Oui. C'est mon website. Je vous remercie pour vos aimables paroles. J'apprends le français. Et je veux combiner cette étude avec mon travail quotidien. Je peux lire. Mais je dois utiliser translate.google.com pour écrire en français. Je suis sûr que certains de mes écrits ici sont risibles (I'm sure some of my writings here are laughable).

Yong Huang

[lady701]

merci pour vos diverses réponses

la version Oracle=9.2.0

pour la requête:

SELECT /*+ FIRST_ROWS PARALLEL (td_etablissements,4) */ code, libpos, date_creation,
DECODE(delai_derniere_prise_offre_dol, NULL , 'Aucune Pr. offres DOL', 0 , '0 à 1', delai_derniere_prise_offre_dol) AS delai_crea_drn_offr_dol_m ,
DECODE(delai_derniere_relation, NULL , 'Aucune relation', 0 , '0 à 1', delai_derniere_relation) AS delai_derniere_relation ,
effectif, ville, secteur_affec, DECODE(ETAT_ETAB_I, NULL , lib_etat_etab, lib_etat_etab || ' - ' || lib_etat_etab_i) AS etat_etab,
numero_etab, plans_actions_encours, naf, nb_due_12mois AS nb_embauches_12m, nb_offres_12mois AS nb_postes_12m,
nb_prises_offres_encours AS nb_pr_off_cours_susp, nb_relations_mois_cours, nb_relations_12mois, nb_rel_visite_12mois,
lib_niv_accords_encours AS niv_accords_encours,
CASE WHEN (part_marche_12mois >100) THEN '100 %' WHEN (part_marche_12mois <= 100) THEN part_marche_12mois || ' %' ELSE '0 %' END AS part_de_marche,
siret, lib_type_etab as type, unite_rattachement || ' - ' || lib_unite_rattachement AS unite_rattachement
FROM td_etablissements WHERE ETAT_ETAB != 'A' AND CODE_TP = 'B' AND DRA = '44116'
AND ville LIKE '%NOZ%' ORDER BY VILLE;

[pachot]

Donc la question était:
est-ce que ETAT_ETAB peut avoir des valeurs nulles ?
Si la réponse est non il faudrait le définir comme NOT NULL dans la table.
(explication ici)

sinon pour ville LIKE '%NOZ%' est-ce qu'il n'y a pas moyen d'éviter ce genre de recherche ? du genre attendre que l'utilisateur conaisse au moins les premières lettres (donc un like 'XXX%' au lieu de like '%XXX%') afin de pouvoir utiliser un index sur VILLE ?

[lady701]

vraiment merci pour votre rapidité

je vais consulter la table et je vous dirai

[lady701]

j'ai essayé de vérifier mais je ne suis pas arrivé pour la vérification des valeurs nulles

par contre, l'explication sur le NOT NULL m'a été un peu compliqué = jai compris que si le champ n'est pas déclaré en NOT NULL, oracle n'utilise pas l'index ......

1000 Merci PACHOT

[Waldar]

j'ai essayé de vérifier mais je ne suis pas arrivé pour la vérification des valeurs nulles

Il suffit de faire :

Code :

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

1
2
3
SELECT count(*)
  FROM td_etablissements
 WHERE etat_etab is null;

Si ça renvoie 0, c'est que vous n'avez pas de valeur nulles !

[lady701]

Merci de votre réponse Waldar

[lady701]

rien qu'en passant le first_rows en all_rows, le gain de temps de réponse est important je suis passé à 12s

[pachot]

rien qu'en passant le first_rows en all_rows, le gain de temps de réponse est important je suis passé à 12s

Cool, on peut voir le plan d'exécution par curiosité ?

[Ahmed AANGOUR]

rien qu'en passant le first_rows en all_rows, le gain de temps de réponse est important je suis passé à 12s

Simple curiosité: pourquoi avoir mis le hint FIRST_ROWS ?

[lady701]

ouiiii bien sûr ,

Ecoulé : 00 :00 :32.99

Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=2386 Card=64
39 Bytes=1159020)

1 0 SORT* (ORDER BY) (Cost=2386 Card=6439 Bytes=1159020) :Q147434
9001

2 1 TABLE ACCESS* (FULL) OF 'TD_ETABLISSEMENTS' (Cost=2324 C :Q147434
ard=6439 Bytes=1159020) 9000



1 PARALLEL_TO_SERIAL SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A
1.C4 C4,A1.C5 C5,A1.C6 C6,A1.C7 C7,A

2 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ NLSSORT(A1
."ENSEIGNE") C0,A1."CODE_POSTAL" C1,



Statistiques
----------------------------------------------------------
20 recursive calls
3 db block gets
61253 consistent gets
60772 physical reads
864 redo size
13163 bytes sent via SQL*Net to client
717 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
91 rows processed

[lady701]

Simple curiosité: pourquoi avoir mis le hint FIRST_ROWS ?

[pachot]

Ah oui, un full scan en parallèle.

par contre, l'explication sur le NOT NULL m'a été un peu compliqué = jai compris que si le champ n'est pas déclaré en NOT NULL, oracle n'utilise pas l'index

Non, c'est pas vraiement ca.

Ici tu as des index bitmaps et ils indexent aussi les valeurs nulles.
Par contre le prédicat ETAT_ETAB != 'A' est equivallent à
ETAT_ETAB != 'A' and ETAT_ETAB is not null

Et donc, oracle va voir 2 fois l'index bitmap: une fois pour enlever les ETAT_ETAB = 'A' et une autre fois pour elever les ETAT_ETAB is null

Celà n'est justifié que si ETAT_ETAB peut être null dans la table et que tu ne veux pas ces valeurs dans le resultat. Sinon, il faudrait soit rajouter NOT NULL dans la table soit rajouter (or ETAT_ETAB is null) dans la requête.

[yong321]

rien qu'en passant le first_rows en all_rows, le gain de temps de réponse est important je suis passé à 12s

Lady701,

Le temps écoulé est trompeuse, parce qu'elle pourrait être affectée par la mémoire cache de données (data in Oracle buffer cache) et de nombreux autres facteurs. Toujours utiliser consistent gets ou buffer gets en tant que métrique. Est-il nettement inférieur à celui 56891 maintenant?

Et bien sûr nous montrer le nouveau plan d'exécution (comme pachot dit).

Yong Huang