Tablespace index full : problème pour faire une requête?

Rebonjour aux forumeurs :P

Désolé si je spam le forum avec mes problèmes mais j'ai besoin de vos conseils éclairés.
J'ai un TBS Index full à 98% et je me demande si ça peut ralentir les requêtes : Select count(*) from ma_table.

Je n'ai rien lu sur le web ou dans mes bouquins d'administration là-dessus. Cependant, si je réfléchis un peu, je ne vois pas pourquoi ça bloquerait : prenons l'exemple d'un parking de 1000 places, s'il est plein à 99.9% il va continuer à fonctionner normalement, les conducteurs trouveront leur voiture aussi vite que s'il était quasi vide car ils ont l'id de leur place; le seul problème est pour les nouveaux conducteurs qui vont peut-être mettre plus de temps à trouver une place libre.

Tout ça pour dire que si ma requête select count(*) est lente, peut-on accuser le TBS d'index avant de voir d'autres pistes?

Un gros merci pour vos réponses.

Non, aucune raison que la place disponible d'un TBS influe sur les perfs.

La raison d'une perte de perf sur un select count(*) est le HWM qui reste haut après de nombreux Delete.

Ou alors vérifier aussi le nombre d'extents de la table / index.

En tout cas, en premier lieu voir l'explain plan, et en mode trace le nombre de gets.

Quelle est le plan d'exécution de ton count ? Celui-ci utilise-t-il l'index ?

Quel est la taille de ta table ?
As-tu tester la même requête en demandant de la parallélisation sur celle-ci à Oracle ?
Exemple de requête de comptage, en demandant un parallèle 6 à Oracle.

Code:

---

select /*+ PARALLEL(p, 6) */ count(*) from ma_table p;

---

Si ce tablespace est plein, il est possible que tu ai d'autre éléments en saturation...

Cordialement,
Patrick Kolodziejczyk.

Voici quelques infos en plus que j'avais mises dans un autre post.


Nb de rows : 4 825 974

Il y a une clé primaire dans cette table sur trois colonnes et un index sur les mêmes colonnes.
PFO_LOT, PFO_LOT_LIGNE, IL_CDR_ID

Index leaf blocks : 33 449
Table blocks : 52 809
Le clustering factor a l'air très mauvais mais on n'accède pas à la table, juste à l'index donc c'est pas gênant.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

set autotrace traceonly explain select count(*) from pfo_date;   COUNT(*) ---------- 4825974 1 row selected.   Execution Plan ---------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=34640 Card=1) 1 SORT AGGREGATE (Card=1) 2 1 INDEX FULL SCAN AFPL.PK_PFO_DATE (Cost=34640 Card=4 M)

---

Test la requête suivante :

Code:

---

select /*+ PARALLEL(p, 6) */ count(*) from pfo_date p;

---

Et donne les temps d'exécution via SET TIMING ON; avant la requête. Pour savoir, si le parallélisme aide ou non.

Cordialement,
Patrick Kolodziejczyk.

C'est quoi "lent" ?
Le plan d'exécution a l'air d'être correct.

Alors là je suis sur le cul!
Seulement 7 secondes...

Code:

---

1 2 3 4 5 6 7 8

set timing on select /*+ PARALLEL(p, 6) */ count(*) from pfo_date p;   COUNT(*) ---------- 4826259 1 row selected. Elapsed: 00:00:07.01

---

Pour être sur que l'environnement n'a pas changé depuis ce midi, je relance la même requête sans parallélisme et là ça prend neuf minutes...

Code:

---

1 2 3 4 5 6 7 8

set timing on select count(*) from pfo_date;   COUNT(*) ---------- 4826259 1 row selected. Elapsed: 00:08:43.21

---

Un gros gros merci pour votre aide :P

C'est une syntaxe que j'ai vue il y a peu sur une problématique d'optimisation de performance. :mrgreen:

Je pense que tu peux marqué la discussion comme résolu.

Cordialement,
Patrick Kolodziejczyk.

Citation:

---

Envoyé par mnitu

...Le plan d'exécution a l'air d'être correct.

---

Je ne suis pas du même avis.
Pour des millions de lignes, j'attendrais plutôt un INDEX FAST FULL SCAN, et non un simple INDEX FULL SCAN (lectures multiblocs d'un côté, monoblocs de l'autre).

En outre, le ratio de 70 entre le temps d'exécution en parallèle 6 et en série me paraît démesuré.
Pour moi, on n'a pas là le simple effet du parallélisme, mais en plus le contournement d'une anomalie (laquelle ?) dans cette exécution en série.

Citation:

---

Envoyé par Pomalaix

Je ne suis pas du même avis.

---

Et vous avez raison! C'est probablement lié au

Citation:

---

Le clustering factor a l'air très mauvais ...

---

il serait intéressant de voir le plan d'exécution de la requête utilisant le parallélisme.

Ayant le même comportement sur une table chez moi, je me permet de donner les deux plans d'exécution correspondant :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

select /*+ PARALLEL(MA_TABLE,6) */ count(*) from MA_TABLE; ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 401K| | | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | PX COORDINATOR | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 1898M| 401K| Q1,00 | PCWC | | | 6 | TABLE ACCESS FULL| MA_TABLE | 1898M| 401K| Q1,00 | PCWP | | ----------------------------------------------------------------------------------------

---

Code:

---

1 2 3 4 5 6 7 8

select count(*) from MA_TABLE; -------------------------------------------------------- | Id | Operation | Name | Rows | Cost | -------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 509K| | 1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FULL SCAN| PK_MA_TABLE | 1898M| 509K| --------------------------------------------------------

---

Cordialement,
Patrick Kolodziejczyk.

Sans parallélisme forcez via le hint FULL et donnez-nous le temps d'exécution de ces trois requêtes.
Les différences peuvent probablement s'expliquer par les lectures mono-bloc via l'index versus multi-bloc quand c'est balayage complet de la table.

Citation:

---

Envoyé par mnitu

il serait intéressant de voir le plan d'exécution de la requête utilisant le parallélisme.

---

Voici le plan d'exécution avec l'option parallélisme.

Code:

---

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

set autotrace on select /*+ PARALLEL(p, 6) */ count(*) from pfo_date p;     COUNT(*) ---------- 4828743 1 row selected.   Execution Plan ---------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=12009 Card=1) 1 SORT AGGREGATE (Card=1) 2 1 PX COORDINATOR 3 2 PX SEND QC (RANDOM) SYS.:TQ10000 (Card=1) 4 3 SORT AGGREGATE (Card=1) 5 4 PX BLOCK ITERATOR (Cost=12009 Card=4 M) 6 5 TABLE ACCESS FULL AFPL.PFO_DATE (Cost=12009 Card=4 M)   Statistics ---------------------------------------------------------- 0 user rollbacks 0 global enqueue releases 0 physical read requests optimized 0 physical write total multi block requests 0 hot buffers moved to head of LRU 0 commit wait performed 0 global undo segment hints helped 0 global undo segment hints were stale 0 IMU commits 0 IMU Flushes 1 rows processed

---

Est-ce tu as pensé à analyser l'index ?

- tu consulte la table statistique (index_stats) correspondant au nom
de ton index
- tu éxécute cette commande :

Code:

---

1 2	analyze index nom_index validate structure ;

---

Tu regarde les résultats de l'analyse dans la même table index_stats, si tu peux
trouver qqchose de suspect ??

Sinon, tu peux générer ton plan d'éxécution en mettant toutes les statisques du plan, par

Code:

---

1 2 3 4

alter session set statistics_level='ALL' ;   select count(*) from nom_table ;

---

ensuite tu regarde ton plan sur une autre session sous sys

Code:

---

1 2	select * from table(dbms_xplan.display_cursor(sql_id=>'xxxxxxx',format=>'ALLSTATS'))

---

Avec ce plan tu auras un plan plus claire !