Purger une base, quelle solution ?

[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

[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

[Waldar]

Envoyé par elkamaro

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é !

[Mohamed.Houri]

Envoyé par elkamaro

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 :

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 ?

[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 :

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]

Envoyé par orafrance

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 ?

[boussafi]

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é.

[orafrance]

Envoyé par olivanto

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]

Envoyé par 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é !

Bonjour et merci pour vos réponses

Alors c'est ce que nous avons déjà essayer de faire, et dans ce cas il faut que la base reste en ligne.
en fait nous même découpé la purge en plusieurs lots (dont certains de 6mois) mais ça gênait quand même. Mais la je pense que c'est à cause du script ... en effet, on faisait un delete massif (genre un delete de 400 000 lignes) ... le tbs undo n'a pas tenu ... et qd on fait des commit tous 1000 enregistrement ca prend trop de temps ...

[elkamaro]

Envoyé par Mohamed.Houri

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.

[elkamaro]

Envoyé par 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.

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

[elkamaro]

Envoyé par orafrance

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..

[skuatamad]

Tu peux peut être regarder du côté de DBMS_REDEFINITION pour redéfinir ta table en table partitionnée tout en étant online.
Ca simplifierait les futures tâches de maintenance grâce aux partitions par contre ça nécessite une grosse capacité de stockage pour la 1ere execution.

C'est juste une idée qui ne convient peut être pas, mais je pense que ça vaut le coup d'y jeter un oeil.

[edit] J'avais pas bien lu tout le fil, l'idée était déjà abordée.