Discussion :

[lecharcutierdelinux]

Bonjour à tous, j'ai plusieurs question

1/J'ai sur ma base de prod un log switch toutes les 5/10 minutes, est ce que cela affecte les performances de ma base, devrais grossir leur taille? 20 Mo actuellement:

-rw-rw---- 1 oraprd dba 20972544 Jun 14 10:58 PRDarch1_199077.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:05 PRDarch1_199078.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:12 PRDarch1_199079.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:17 PRDarch1_199080.dbf
-rw-rw---- 1 oraprd dba 20970496 Jun 14 11:22 PRDarch1_199081.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:30 PRDarch1_199082.dbf
-rw-rw---- 1 oraprd dba 20971520 Jun 14 11:31 PRDarch1_199083.dbf
-rw-rw---- 1 oraprd dba 20969472 Jun 14 11:33 PRDarch1_199084.dbf
-rw-rw---- 1 oraprd dba 20971520 Jun 14 11:35 PRDarch1_199085.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:40 PRDarch1_199086.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:46 PRDarch1_199087.dbf
-rw-rw---- 1 oraprd dba 20972544 Jun 14 11:54 PRDarch1_199088.dbf


2/Je n'ai pas tout saisi au niveau du rebuild d'un index? Ce dernier est reconstruit mais est-ce qu'il est effacé puis re-rempli?

3/Quelle est la meilleure méthode de reorganiser une table, la plus pratique? Export/import, en créer une copie "as select"? ou compacté "shrink"/coalasce? Question d'experience qu'en pensez vous?

4/Je ne comprends pas l'utilisation du package dbms.stats car une fois que j'execute une fonction de ce package, où puis-je consulter le résultat? Par exemple si je lance:
#exec dbms_stats.gather_schema_stats('toto')

5/La commande analyse table me renvoit "analyse: command not found" sous sqlplus?

6/Enfin, il est expliqué qu'un optimiseur "gather_stats_job" planifié depuis la version 10 sous forme de job. Quand je vais dans OEM, je le vois bien dans "travaux" mais sous toad je ne le trouve pas dans la section jobs de l'utilisateur SYS ou SYSTEM. Qqun a une idée du type d'objet que c'est?

D'avance merci.

[LeoAnderson]

1. Un switch toutes les 5 minutes, c'est un peu trop. On cherche à en avoir 2-3 par heure d'activité normale.
Attention toutefois aux problèmes que cela engendre sur la capacité de restauration, notamment sur des bases répliquées !

2. Un nouvel index est recréé avec un équilibrage "idéal"

3. Le shrink se faisant à chaud, c'est le moins impactant; je préfère donc.

4. cela renseigne un certain nombre de colonnes de DBA_TABLES et consoeurs.... attention aux paramètres d'appels

5. z ! analyZe !

6. ce n'est pas un "JOB" mais une "SCHEDULED" !

[orafrance]

1/ un switch toutes les 15-30 minutes c'est moins impactant mais plus le délai est long plus tu perds des données en cas de crash... 5-10 minutes ça parait pas catastrophique

2/ le rebuild reconstruit l'index on peux considérer que c'est comme une recréation mais en réalité c'est mieux que ça puisque le rebuild se fait ONLINE

3/ le COALESCE ne réorganise pas la table, il ne fait que fusionner les extents libres contigus. Au lieu d'avoir 3 extents vide qui se suivent tu n'en auras plus qu'un. Le mieux c'est la commande MOVE en passant pas un tablespace de travail

4/ dbms_stats calcule les statistiques sur les segments, il remplie la colonne NUM_ROWS par exemple dans DBA_TABLES.

5/ c'est ANALYZE avec un Z mais depuis la 9i c'est obsoléte pour calculer les stats. On ne s'en sert plus que pour trouver les lignes migrées.

6/ ton TOAD ne doit pas être parfaitement compatible 10g

Si tu veux d'avantages d'info, je t'invite à te tourner vers les nombreux tutoriels disponibles et la recherche du forum parce que tous ces points ont déjà été traités

[lecharcutierdelinux]

Alors là chapeau messieurs, vous avez vraiment réponses à tout.

J'ai une dernière question mais j'ai honte de la poser. Depuis toujours je sais ce qu'est un index, son principe et son utilité.

Pour moi un index est une table dont on n'a pris que qqes champs pour en accélérer la lecteur. La question que je me pose c'est pourquoi contrairement aux tables, on ne peut pas voir son contenu ? Dans toad, n'importe quel sur lequel je clique me précise de quelle table il dépend mais je ne vois pas son contenu. Ma question est peut être complètement stupide mais ça me trote dans la tête.

L'autre truc qui me dérange dans oracle c'est la gestion de l'espace libre. Le coup des extends + datafiles et tablespace est compréhensible. Mais la gestion des segments est bcp plus obscure. Il est par exemple impossible (ou difficile) de trouver une vue indiquant directement la taille d'une table. Il faut passer par des procédures. Et à l'interieur d'un segment, lorsqu'on supprime des lignes, les blocs se vident ce qui fait qu'une table de 2Mo d'extent, peut être en réalité plein qu'à 90% et à l'intieur de ces 90% on trouve aussi des blocs vide dû à des suppressions... C'est un peu compliqué au final pour connaître la taille d'un segment.

[orafrance]

Pour moi un index est une table dont on n'a pris que qqes champs pour en accélérer la lecteur. La question que je me pose c'est pourquoi contrairement aux tables, on ne peut pas voir son contenu ? Dans toad, n'importe quel sur lequel je clique me précise de quelle table il dépend mais je ne vois pas son contenu. Ma question est peut être complètement stupide mais ça me trote dans la tête.

Un index c'est une liste de référence. C'est à dire : pour tel colonne, on trouvera telle valeur dans la liste de bloc x, y et z. Tu te doutes bien que la lecture de ces informations n'apportent absolument rien et serait même illisible. Pour répondre à la question, j'imagine qu'il y a une table qui permet de voir le contenu d'un index mais celle-ci est dans le schéma SYS et c'est une table interne qui n'est probablement même pas documentée

L'autre truc qui me dérange dans oracle c'est la gestion de l'espace libre. Le coup des extends + datafiles et tablespace est compréhensible. Mais la gestion des segments est bcp plus obscure. Il est par exemple impossible (ou difficile) de trouver une vue indiquant directement la taille d'une table. Il faut passer par des procédures. Et à l'interieur d'un segment, lorsqu'on supprime des lignes, les blocs se vident ce qui fait qu'une table de 2Mo d'extent, peut être en réalité plein qu'à 90% et à l'intieur de ces 90% on trouve aussi des blocs vide dû à des suppressions... C'est un peu compliqué au final pour connaître la taille d'un segment.

Tu as la colonne BYTES de DBA_SEGMENTS qui donne la taille... rien d'alambiqué là-dedans
Ce qu'il faut comprendre c'est qu'Oracle hérite de concept pensés alors que la puissance de calcul des machines était très limité et que les accés disques étaient très couteux. Ainsi c'était trop couteux de désallouer l'espace dés qu'on supprimait des lignes, en plus PCTFREE et PCTUSED anticipe l'usage des blocs pendant la vie de la base (à quoi ça servirait de libérer tout un extent alors que dans 98% des cas on va réutiliser cet espace).

Finalement, rien ne sert de connaitre la taille d'un segment (plus précis que DBA_SEGMENT.BYTES j'entends ) puisque le plus important dans la manière de récupérer l'espace c'est la High Water Mark c'est à dire où est le dernier block du segment qui a été écrit durant la vie du segment. Et ça, seul TRUNCATE ou DROP peut le faire redescendre toujours à cause de cette histoire de désallocation inutile et couteuse (sans parler de la fragmentation que ça générerait).

Je ne suis pas sûr d'avoir répondu à la question mais j'ai essayé de faire au mieux

[lecharcutierdelinux]

Merci (une fois de +) orafrance,

Si tu as un bloc rempli à 60% de lignes (pctfree à 40). Cela signifie bien que 40% de l'espace est reservé aux futurs update.

Mais que se passe t'il si les lignes qui utilisent les 60% sont supprimées? Est-ce que le segment sera capable de réutilisé le bloc? Dans ce cas là il ne peut y avoir de fragmentation dans une base? (à part le pctfree mais qui peut être utile). En fait, à quel niveau de nombreux delete peuvent ils créent une base gruillère?

Logiquement, étant donné que les segments sont composé d'extents qui eux même sont composés de bloc bien géré par pctfree et pctused c'est impossible? En cas de drop du segment (les extents et les blocs restent-ils sur le disque? Les données sont elles toujours dans les blocs?...)

[orafrance]

le bloc est considéré vide seulement si PCTFREE pourcent du bloc est vide

ce que je disais c'est qu'il y aurait des problèmes de fragmentation si on passait notre temps à désallouer l'espace disque... ce qu'on ne fait pas donc c'est bon

[lecharcutierdelinux]

OK,
dernière question:

-Si tu drop une table, les extents et blocs qui lui était reservé sont-ils réutilisables pour un autre segment?

-Si tu vides une table (truncate), les blocs seront réutilisés lors des prochaines insertions? grâce à la freelist?

-Si tu supprimes une ligne sur deux, là il y a de la fragmentation au sein du bloc mais est-ce que pour les futurs insertions, ces espaces supprimés seront re-remplies ou est-ce que oracle aura tendance a utiliser un nouveau bloc?



Désolé d'insister sur ce point là mais je viens de me rendre compte que j'avais une dizaine de table à + de 25 millions d'enregistrements. DOnc si je fais le ménage j'aimerais vraiment que les blocs libérés soit réutilisables et que la table arrête de grosssir en taille.
D'ailleurs je pense que la base de données a du mal à cause de ces tables mais je ne vois pas ce que je peux faire d'autre à part la réorganiser.

[orafrance]

drop = truncate = HWM en début de segment = le segment est vide ou inexistant. Evidemment quand il n'existe plus tu réutilises les blocs

pour la réutilisation du bloc ça dépend encore une fois de PCTFREE

[lecharcutierdelinux]

Merci orafrance,

Je suis un peu surpris de ta réponse car cela signifie qu'il n'y'a presque jamais de trou dans la base de données? Pourtant après un export/import de ma base de QUA cette dernière a presque été divisé en 2.

Quelle en était la cause à ton avis?

[orafrance]

probablement des PCTFREE trop gros avec beaucoup de DELETE mais qui vidaient pas assez le bloc... ça devrait justement t'insiter à changer les clauses storage et réorganiser ta base

[lecharcutierdelinux]

Ok mais juste une remarque, si je truncate la table, elle sera vide mais je ne suis pas sur que la colonne BYTE de dba_segment indique 0! C'est ça justement le prob sur Oracle.

[pifor]

A propos de PCTFREE et PCTUSED, noter que Oracle 10g créé par défaut un tablespace permanent en automatic segment space management qui annulle les paramètres PCTFREE et PCTUSED d'un CREATE TABLE ou ALTER TABLE:

The segment_management_clause is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle Database should track the used and free space in the segments in the tablespace using free lists or bitmaps. This clause is not valid for a temporary tablespace.

AUTO Specify AUTO if you want the database to manage the free space of segments in the tablespace using a bitmap. If you specify AUTO, then the database ignores any specification for PCTUSED, FREELIST, and FREELIST GROUPS in subsequent storage specifications for objects in this tablespace. This setting is called automatic segment-space management and is the default.

MANUAL Specify MANUAL if you want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that you do not use this setting and that you create tablespaces with automatic segment-space management.

To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT column of the DBA_TABLESPACES or USER_TABLESPACES data dictionary view.

[pifor]

Ok mais juste une remarque, si je truncate la table, elle sera vide mais je ne suis pas sur que la colonne BYTE de dba_segment indique 0! C'est ça justement le prob sur Oracle.

Oui mais grâce au job des statistiques automatiques, Oracle va s'en rendre compte et va mettre à jour la colonne DBA_TABLES.NUM_ROWS.

[lecharcutierdelinux]

Les choses sont plus claires maintenant,

Pour procéder a des tests je souhaiterais créer une table qui se rempli toute seule? Est-ce possible en sql de générer une insertions en auto-increment pour 5 champs? Jusqu'a 10.000 par exemple? Une fois l'arret du script, je truncate pour voir.

[louping]

J'arrive en retard, dsl

2/Je n'ai pas tout saisi au niveau du rebuild d'un index? Ce dernier est reconstruit mais est-ce qu'il est effacé puis re-rempli?

Un index temporaire est généré dans le même espace, puis renommer à la fin du rebuild, donc il te faut la taille du + gros index de libre

3/Quelle est la meilleure méthode de reorganiser une table, la plus pratique? Export/import, en créer une copie "as select"? ou compacté "shrink"/coalasce? Question d'experience qu'en pensez vous?

La meilleur reste l'import/export même en AUTO ALLOCATE, avec shrink/coalasce chaque jours. Avec copie "as select" tu vas boucher les trous des autres tables et cela pour chacunes des tables...

Le reste a été repondu...
A+

[orafrance]

Ok mais juste une remarque, si je truncate la table, elle sera vide mais je ne suis pas sur que la colonne BYTE de dba_segment indique 0! C'est ça justement le prob sur Oracle.

le BYTES devrait être égale à la taille de 3 extents au minimum en LMT

[orafrance]

Les choses sont plus claires maintenant,

Pour procéder a des tests je souhaiterais créer une table qui se rempli toute seule? Est-ce possible en sql de générer une insertions en auto-increment pour 5 champs? Jusqu'a 10.000 par exemple? Une fois l'arret du script, je truncate pour voir.

le plus simple c'est encore de faire un :

Code :

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

CREATE TABLE tatable AS SELECT * FROM dba_objects WHERE 0=1;

et ensuite :

Code :

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

INSERT INTO tatable SELECT * FROM dba_objects WHERE ROWNUM <= 10000