Discussion :

[elkamaro]

Bonjour à tous,

je viens a vous pour de l'aide.
Mon client me demande d'effectuer la purge d'une base de donnée Oracle10g d'à peu prés 100go pour gagner de l'espace.

Je dois supprimer toutes les données antérieur à 2010.
J'ai déjà ciblé quelles tables sont concernées et leurs ordres.
Je n'ai pu obtenir que 1heure/jour d'indisponibilité de la base pour cette purge

Lorsque je fais un delete normal sur les tables, il me faut plus de 3 semaines pour tout purgé (avec la base en ligne, mais bon les performance de la base sont très dégradées)

1/
j'ai pensé à ceci :
1- create table a as select * from b
2- drop table b
3- rename a to b
mais les index et constraints sont atteints, donc faut que je les recréer et ca prend aussi énormément de temps.

2/delete bulk
j'ai déjà essayer avec un bulk delete avec rowid comme proposé ici : http://www.dbatools.net/experience/o...lk_delete.html
Mais sur une table de plus 300 000 000 de lignes et toutes ces contraintes, ca prend aussi énormément de temps


Avec vous eu des expériences de ce genre et quelles sont vos solutions, tout aide ou conseil est le bien venu car je suis vraiment coincé.


En vous remerciant.

[Mohamed.Houri]

Je suppose que l'on vous a demandé de supprimer des données afin d'alleger le volume et donc d'améliorer la performance. Est-ce bien ça?

Dans ce cas, quelques soit la méthode choisie pour faire votre suppression de données, soyez averti, selon le volume de données supprimé par rapport au volume de données restant et selon le critère de suppression, il se peut que la performance de votre base de données va être pire après le delete qu'elle ne l'était avant cette purge.

Il va vous falloir une ré-organisation des objets(coalesce des indexes, shrink des tables, etc..) et cela ne va pas se faire sans effet sur le clustering factor de certains indexes et donc sans un effet sur les explain plan.

Je vous conseille de bien analyser tout cela et faites un test equivalent en acceptance avant de passer en PROD.

Il y a une discussion intéressante dans l'article suivant

http://jonathanlewis.wordpress.com/2...quiz-night-13/

[olivanto]

dans ce cas, n'est il pas plus simple de créer une nouvelle base avec uniquement les données à garder, puis de droper l'ancienne... ?

[elkamaro]

et bien je vous remercie de vos réponses

et bien j'ai deja proposer ces choses la au client mais celui ci reste convaincu que c'est faisable sans changer de bases et avec un simple delete.

Il s'agit d'une base assez critique qui nous laisse que peu de marge de manoeuvre pour cette purge

[Waldar]

et bien j'ai deja proposer ces choses la au client mais celui ci reste convaincu que c'est faisable sans changer de bases et avec un simple delete.

Votre client a tort.

[olivanto]

oui, mais c'est lui qui paye...

t'as qu'à purger au coup par coup (du style, une année à chaque "purge") ; c'est long, mais au moins t'as rien à faire de compliqué !

[elkamaro]

Bonjour

Est ce que quelqu'un pourrait quand même me conseiller une solution a utiliser
1/ soit une solution qu on lance à plusieurs fois une heure par jour
2/ ou une solution qu on peut lancer base ouverte

nous avons déjà écarter l'idée de créer une nouvelle base en réimportant uniquement les données nécessaire car cette solution prend énormément de temps

En gros j'ai juste à purger une dizaine de tables avec un critére de date.
Les plus grosses tables font 10 à 14 Go et contient biensur des index et ck et fk

Merci pour votre aide

[Mohamed.Houri]

Bonjour

En gros j'ai juste à purger une dizaine de tables avec un critére de date.
Les plus grosses tables font 10 à 14 Go et contient biensur des index et ck et fk

Merci pour votre aide

Faites attention!!! Comme je l'ai senti, votre purge se base sur un critère de date du genre

Code :

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

1
2
 
where dat >= une_date_du_passe

Aujourd'hui votre principal souci est comment purger les données le plus efficacement possible sans perturber le fonctionnement de votre application. Mais demain (après la purge) votre souci deviendra peut-être un problème de performance global de l'application dû, justement, à l'effet que cette purge produira sur le High Water Mark des tables et sur les indexes remplis uniquement sur leur droite. Surtout si le volume à supprimer est énorme.

A bon entendeur salut !

[olivanto]

Son seul but est de visiblement de purger les données, pas autre chose ; on peut longtemps discuter du HWM ou du clustering....mais le fait est que s'il est en mesure d'identifier les objets qui peuvent poser problèmes après la purge, il suffira de les recréer ; si ce problème des performances se résume à qq tables et quelques indexes boiteux, ce n'est quand même pas trop compliqué de recréer les tables et de dropper / recréer les indexs ; à moins d'en avoir des centaines...bien sûr.

non ?

[elkamaro]

Aujourd'hui votre principal souci est comment purger les données le plus efficacement possible sans perturber le fonctionnement de votre application. Mais demain (après la purge) votre souci deviendra peut-être un problème de performance global de l'application dû, justement, à l'effet que cette purge produira sur le High Water Mark des tables et sur les indexes remplis uniquement sur leur droite. Surtout si le volume à supprimer est énorme.

A bon entendeur salut !

Merci Mohamed pour cette précision,
en prenant en compte tes recommandation on va proposer porter ces information au client afin qu on se protege et qu'il prenne ces prédisposition.

[pachot]

Bonjour,

Le but initial étant de gagner de l'espace, faire des delete n'est pas la solution immédiate. Sauf à faire une réorg par la suite (qui peut être faire online).

Pour accélérer le create table as select, il est possible de créer les index en nologging et en parallel (penser à remettre le degré à sa valeur par défaut faire un backup par la suite) et recréer les contraintes en novalidate si tu es sûr que l'intégrité des données est toujours bonne.

Cordialement,
Franck.

[orafrance]

Pour moi, il n'y a pas 36 solutions, il faut recréer les tables du genre :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE matable_purgee AS
SELECT * FROM matable;
 
TRUNCATE TABLE matable;
 
INSERT INTO matable 
SELECT * FROM matable_purgee ;

Idéalement tu renommes les tables pour s'éviter un nouvel INSERT mais c'est loin d'être trivial puisqu'il faut recréer les contraintes, indexes, etc.

En tout cas, espérer s'en sortir avec un simple DELETE c'est très aléaloire puisque la HWM ne descendra pas tu risques donc d'avoir des temps désastreux sur un simple COUNT, tu ne réussiras pas à réduire la taille des datafiles et tu ne profiteras pas d'une réorganisation.

Tu peux en profiter pour créer des tables partitionnées ce qui en plus de régler le problème actuel permettra de se simplifier la tache à l'avenir.

Bon courage

PS : c'est pas parce que le client paye qu'il a raison, explique lui le cout du DELETE pour un gain nul et le cout d'une réorg, ça devrait le convaincre

[olivanto]

tu peux en profiter pour créer des tables partitionnées ce qui en plus de régler le problème actuel permettra de se simplifier la tache à l'avenir.

Je ne suis un grand spécialiste, mais ; à moins que ses tables soient déja partitionnées (auquel cas on doit peut être pouvoir tout simplement supprimer le(s) partition(s) inutiles ), c'est un peu tard.
Entre DBMS_redefinition, et le code que tu proposes, c'est du pareil au même non ?

[islamov2000]

j'ai tombé une fois sur le meme probleme.
pour cela;j'ai

• augmenté le nombre le rollback segment(undo pour Oracle >=9i)
• déactivé les containtes apres une étude
• déactivé le trigger apres une étude
• re-dimentionné les tablespaces apres défragmentation
• réintialisé le tablespace TEMP

pour aller vite sur une base de données à haute disponibilité.

[islamov2000]

j'ai tombé une fois sur le meme probleme.
pour cela;j'ai

• augmenté le nombre le rollback segment(undo pour Oracle >=9i)
• déactivé les containtes apres une étude
• déactivé le trigger apres une étude
• re-dimentionné les tablespaces apres défragmentation
• réintialisé le tablespace TEMP

pour aller vite sur une base de données à haute disponibilité.

as tu pris en consideration mon message

[orafrance]

Je ne suis un grand spécialiste, mais ; à moins que ses tables soient déja partitionnées (auquel cas on doit peut être pouvoir tout simplement supprimer le(s) partition(s) inutiles ), c'est un peu tard.
Entre DBMS_redefinition, et le code que tu proposes, c'est du pareil au même non ?

Oui, ça oblige à une réorg aussi mais t'auras plus à le refaire ensuite, après t'auras juste à gérer tes partitions, donc non ça régle pas le problème présent mais t'anticipe pour les années à venir, ce qui peut largement justifier le surcout de la réorg

[olivanto]

ok... la question se pose aussi pour ma base, mais pas tout'suite ! D'où mon "incrustation" dans le débat..

[elkamaro]

Oui, ça oblige à une réorg aussi mais t'auras plus à le refaire ensuite, après t'auras juste à gérer tes partitions, donc non ça régle pas le problème présent mais t'anticipe pour les années à venir, ce qui peut largement justifier le surcout de la réorg

Bien vu OraFrance, car le client dans ses spéc voulait que l'opération soit reproduite tous les 6mois ou 1 ans..

[elkamaro]

Bonjour,

Le but initial étant de gagner de l'espace, faire des delete n'est pas la solution immédiate. Sauf à faire une réorg par la suite (qui peut être faire online).

Pour accélérer le create table as select, il est possible de créer les index en nologging et en parallel (penser à remettre le degré à sa valeur par défaut faire un backup par la suite) et recréer les contraintes en novalidate si tu es sûr que l'intégrité des données est toujours bonne.

Cordialement,
Franck.

Bonjour Pachot, j ai aussi tester ta méthode en faisant CTAS juste des données que je voulais ... je suis quand même bloqué sur certaine table donc les index sont gros de l'ordre de 3Go, ca depasse l'heure d'indisponibilté que j'ai pu obtenir

[Mohamed.Houri]

Bonjour à tous,

je viens a vous pour de l'aide.
Mon client me demande d'effectuer la purge d'une base de donnée Oracle10g d'à peu prés 100go pour gagner de l'espace.

Je dois supprimer toutes les données antérieur à 2010.
J'ai déjà ciblé quelles tables sont concernées et leurs ordres.
Je n'ai pu obtenir que 1heure/jour d'indisponibilité de la base pour cette purge

Lorsque je fais un delete normal sur les tables, il me faut plus de 3 semaines pour tout purgé (avec la base en ligne, mais bon les performance de la base sont très dégradées)

Voilà plusieurs semaines que vous avez posé cette question de purge et il me semble que vous êtes toujours au point mort.

Certains, très justement, vous ont proposé de transformer vos tables en table partitionnés. Mais je suis désolé de vous dire que ce genre de chose doit être pensé au moment de la modélisation de la base de données et non maintenant. Si votre client prévoyait de faire une purge tous les 6 mois, pourquoi alors avoir attendu jusqu’à la mise en production pour commencer à envisager une solution de purge très efficace ?

Admettons que vous voulez vraiment passer maintenant d’un schéma de tables normales (heap table) à un schéma de table partitionnées ! Vu votre objectif de purge des données sur la base d’un critère de date et sur un rythme régulier d’une purge par semestre, c’est ce qui se fait de mieux par rapport à cet objectif (dommage qu’il ait été pensé maintenant).

Je suppose que vous allez faire un partitionnement par date (range partition) et que vous allez considérer le critère de date qui sert à la purge comme clé de partitionnement. Très bien. Dans ce cas il vous faudra simplement supprimer les partitions que vous n’utilisez plus avec un simple:

Code :

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

1
2
 
alter table ma_nouvelle_tab_part drop partition p2010 update global indexes ;

Je ne passerai pas sans vous signaler que, comme vous avez certainement des tables liées entre elles par des clés étrangères, vous devriez d’abord ‘’disabler’’ ces contraintes d’intégrité avant de supprimer les partitions et de les ‘’ré-enabler’’ à la fin sans validation bien évidemment.

Par contre, en passant à ce nouveau schéma, vous pourriez (au conditionnel) bien aussi complètement détériorer la performance de votre application. La raison dans ce cas n’aura rien à voir avec la fragmentation des indexes basés sur le critère de date (‘’sweeper indexes’’ ou ‘smashed indexes’’ ou ‘’right hand indexes’’) mais plutôt avec le comportement des anciennes requêtes par rapport au partitionnement. Toute requête ne faisant pas appel à la clé de partitionnement fera un ''PARTITION RANGE ALL'' ce qui n’est pas du tout le but du partitionnement à savoir:

1. Facilité d’administration (comme la purge par exemple)
2. Amélioration de la performance grâce à l’élimination de partitions

Comment allez-vous transformer vos indexes ? En indexes simples ? En indexes localement partitionnés ? En indexes globalement partitionnés ? En indexes préfixés (non préfixés) ?

Ne foncez pas sur une solution si vous ne maitrisez pas tous les aspects qui entourent cette solution