Discussion :

[user_oracle]

Bonjour à tous,

Comment savoir si une table à besoin d'être réorganiser ou pas ? :
Si oui, existe-t-il une procédure automatique qui permettrait de réorganiser ces tables :

MERCI

[orafrance]

Il faut éviter les lignes trop chainées.

En gros, les tables qui apparaissent ici sont rebuildable

Code :

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

1
2
select distinct owner_name,table_name from system.CHAINED_ROWS
order by 2

[user_oracle]

Lorsque j'exécute la requête, il me dit que la table n'existe pas. Par contre, je n'ai pas trouvé grand chose sur CHAINED ROWS ?

[orafrance]

Oups

Effectivement, c'est une table utile mais qui n'est pas installé par défaut, il faut lancer le script %ORACLE_HOME%/rdbms/admin/utlchain.sql et calculer les statistiques sur les tables que tu veut surveiller :

Code :

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

1
2
ANALYZE TABLE <nom de table>
LIST CHAINED ROWS INTO chained_rows;

Pour créer la table, il faut exécuté $ORACLE_HOME/rdbms/admin/utlchain.sql

[rouardg]

Ce que dit Orafrance est juste, mais il y a plus simple si tu génères tes statistiques.

Si celles-ci sont à jour, utilise requête suivante :

Code :

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

select * from user_tables where chain_cnt != 0 ;

Cela te donnera la liste des tables ayant des lignes chaînées.

[Marc Musette]

attention, une reorg n'est utile qu'en cas de row migrée (une vrai chained row est immuable)

--> l'intérêt de vérifier la colonne AVG_ROW_LEN de user_tables

[orafrance]

c'est exact, c'est pourquoi je parle de l'analyze CHAINED ROWS qui donnent bien la liste des rowid migrés

[rouardg]

Excuse moi Marc, mais je ne comprends rien à ce que tu dis. Qu'entends-tu par immuable, et que surveilles-tu avec AVG_ROW_LEN ???

Pour moi, dès que le nombre de lignes chaînées dépasse 1 % du nombre total de ligne, je corrige ce pb de lignes chaînées.

Pour ce faire, j'ai 2 solutions.
Solution 1 :
1 ) je créé une table temporaire à partir de ma table d'origine,
2 ) je fais un truncate sur la table d'origine,
3 ) je remplis la table d'origine à partir de la sauvegarde. Je peux même ajouter un order by, afin d'améliorer le clustering factor d'un index.

Solution 2 :
Ou alors, si je ne peux pas faire de truncate sur la table, je la déplace dans son tablespace (à ce moment-là, tu verras que les lignes chaînées se retrouvent toujours à la fin de la table) et je reconstruis les index.

[orafrance]

à noter une chose intéressante : le MOVE invalide les indexes de la table qui passent à UNUSED... pensez donc à les reconstruire ou les recréer comme le souligne rouardg

Edit : et non pas le TRUNCATE comme j'avais dit

[rouardg]

à noter une chose intéressante : le TRUNCATE invalide les indexes de la table qui passent à UNUSED... pensez donc à les reconstruire ou les recréer

Non, non orafrance, tu te trompes !!!

Le TRUNCATE n'a jamais invalidé les index, c'est le déplacement de la table dans le tablespace (ALTER TABLE xxx MOVE TABLESPACE yyy) qui invalide les index.

Donc comme je le disais, c'est en solution 2 que l'on reconstruit les index.

[orafrance]

tu as raison

C'est bien le MOVE qui déplace les données et donc les adresses de bloc de l'index ne sont plus bonnes pour un ROWID donné

Désolé... j'édite mon post pour ne pas laisser cette horreur

[user_oracle]

Je vous remercie pour toutes ces informations très précieuses. Une dernière question, est ce qqun a déjà créé une procédure qui permettrait de faire tout ceci en automatique sans l'intervention humaine ?

[Marc Musette]

il ya deux types de chained rows :

1- les "vraies" = la taille de la rows est > à la taille d'un block --> rien n'y fait, la row sera toujours répartie sur plusieurs blocks

2- les migrated rows

soit un bloc de 8k, une row de 2k
--> la row tient ds le bloc
le bloc est ensuite utilisé pour des insert de nouvelles rows

on fait un update de la row initiale et ohohoh plus de place ds le bloc --> la row est alors migrée da un nouveau bloc et seul subsite un pointeur dans le bloc initial (ennuyant pour les recherches avec index car l'index pointe vers le bloc initial et le bloc nitial pointe vers le nouveau)

--> ces rows peuvent être reconstruites pour ne se situer que dans un bloc puisque qu'elles ont toujours une taille inférieure à celle d'un bloc

-> l'intérêt de vérifier la longueur de la row (effectivement la moyenne ne donne qu'une idée et mieux calculer la véritable taille)

[user_oracle]

Merci pour ton explication Marc sur les chained rows, c'est plus clair maintenant.

[user_oracle]

Bonjour Messieurs,

Je vous remercie pour toutes ces informations. Maintenant, je vais essayer avec votre aide de faire une procédure qui englobera les étapes suivantes pour l'intégrer à un job quotidien :

1. positionnement de l'option MONITORING à YES au niveau table avec DBMS_STATS.alter_schema_tab_monitoring('user') afin d'utiliser les procédures suivantes DBMS_STATS.gather_database_stats et gather_schema_stats,

2.supprimer les stats déjà existante avec DBMS_STATS.delete_schema_stats,

3.regénération des stats avec DBMS_STATS.gather_schema_stats('user', % d'analyse sur la table),

4.remonté les tables qui contiennent des lignes chaînées avec la requête suivante

Code :

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

SELECT chain_cnt, table_name, last_analyzed FROM user_tables WHERE chain_cnt != 0;

5.SI la requête retourne des noms de tables, insérer ces lignes dans une variable
ALORS faire un MOVE des tables vers un autre tablespace qui sera créer préalablement en automatique ensuite refaire un MOVE vers le tablespace d'origine sans oublier de reconstruire les indexes avec un rebuild
SINON pas de réorganisation nécessaires.

Je voudrai savoir comment je peux récupérer cette variable (nom_table qui ont des lignes chaïnées) afin de l'utiliser dans l'étape 5.
Je voudrai par la suite remonter ce résultat vers ma messagerie avec le package UTL_SMTP.
J'espère ne rien avoir oublier.
MERCI pour votre aide.

[orafrance]

1°) l'étape 2 est inutile

2°) le sql dynamique est ton ami, la recherche sur le forum aussi

[Marc Musette]

mais tu ne tiens pas compte de la longueur de la row ... je ne comprends pas pourquoi tu vas t'amuser à bouger des vraies chained rows ... elles seront toujours chaînées

[orafrance]

Exact, il faut bien sûr traiter le probléme des lignes migrées et non simplement chainée

En plus, c'est le genre d'action assez lourde à faire et on réorganise les tables assez rarement quand même... en général c'est quand on voit les perfs se dégrader dangereusement

[user_oracle]

Le pb est que j'ai bcp de mise à jour quotidienne sur mes tables (Update/Delete/insert), j'ai besoin de réorganiser mes tables afin d'optimiser le résultats des requêtes.
Le pb des lignes migrées n'est pas corriger avec la réorganisation de la table :

[Marc Musette]

selon moi il est très difficile de réorganiser AUTOMATIQUEMENT les tables

je réinsiste sur le fait qu'une vraie chained row ne peut être résolu : si une ligne prend 8k et le bloc size =4k il faudra toujours deux blocs pour contenir la row (reorg ou pas reorg)

--> c'est donc une perte de temps ; seules les rows migrées peuvent être corrigées