Discussion :

[cquilgars]

Bonjour,

Soit un base 9i
Soit une table MaTable partitionnée et sous-partitionnée

Les évènements survenus sur cette table sont les suivants :
1) Chargement de 700 000 lignes
2) Soit un bilan de l'occupation disque de la table ci-dessous
Vous remarquerez les sous-partitions chargées à cette occasion
Pour les autres, cela correspond au min extends attribué avec un block sizé à 256Ko. Jusque là pas de surprise...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t.partition_name as "partition",t.subpartition_name as "sous partition",round(sum(bytes))/1024 as "Ko"
FROM dba_segments s,dba_tab_subpartitions t
WHERE segment_name ='MaTable'
and s.partition_name=t.subpartition_name
group by t.table_name,t.partition_name,t.subpartition_name

partition			sous partition	Ko
P_Matable_20070105	SYS_SUBP11900	256
P_Matable_20070105	SYS_SUBP11901	256
P_Matable_20070105	SYS_SUBP11902	256
P_Matable_20070106	SYS_SUBP11929	256
P_Matable_20070106	SYS_SUBP11930	256
P_Matable_20070106	SYS_SUBP11931	256
.../...
P_MaTable_20070110	SYS_SUBP12047	768
P_MaTable_20070111	SYS_SUBP12074	256
P_MaTable_20070111	SYS_SUBP12075	256
P_MaTable_20070111	SYS_SUBP12076	2265
P_MaTable_20070113	SYS_SUBP12134	256
.../...

2) truncate table MaTable drop storage
3) Execution de la même requête
L'occupation est tjs identique alors que la table est tronquée avec libération du stockage Je m'attendais à une libération des block par la clause "drop storage" !!!
4) alter table Matable truncate subpartition SYS_SUBP12076 drop storage
5) Execution de la même requête
La situation reste la même...

Pouvez-vous éclairer ma lanterne ? A part droper la table et la créer, je ne vois comment libérer cette allocation de blocks ...

Merci par avance de vos remarques

[remi4444]

est-ce que tu es sûr que ça corresponds pas à l'initial extend qui serait différent d'une partition à l'autre ?

[cquilgars]

Bonjour Rémi,

Tout d'abord, merci pour ton intérêt...

J'en suis sur pour preuve des réponses aux requêtes

Code :

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

 select distinct initial_extent from dba_subpartitions where table_name ='MaTable'

Tout le monde est à 256 Ko

Deplus, un drop et un create de la table me permettent de mettre tout ce petit monde à 256Ko...

[McM]

Il me semble que les TRUNCATE ne libèrent pas les extends.
L'option DROP STORAGE .. je connaissais REUSE STORAGE qui remettait le HWM à 0.
Quelqu'un a des infos sur ces options ?

[McM]

Erreur de ma part : Je corrige :

http://www.psoug.org/reference/truncate.html

Truncates are DDL and, in a sense, cheat. A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table
(if you have MINEXTENTS set to anything other than 1, then that number of extents is retained rather than just the initial). By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake

Empty Table And Move Highwater Mark
TRUNCATE table <table_name> DROP STORAGE;
By default drops storage even if DROP STORAGE is not specified

Empty Table And Do Not Move Highwater Mark
TRUNCATE TABLE <table_name> REUSE STORAGE;

Bref j'avais tout faux : par defaut le truncate RAZ le HWM

[cquilgars]

Bref j'avais tout faux : par defaut le truncate RAZ le HWM

Bonjour,

Veux-tu dire que la clause drop storage doit normalement libèrer "virtuellement" les extends en positionnant à zéro le HWM ?

[McM]

Non, le TRUNCATE (hors option REUSE STORAGE) supprime tous les enreg et remet le HWM à 0 (il me semble que le HWM c'est le point d'arret pour les accès lectures/ecritures)

La suppression des extents n'est pas automatique, elle dépend du MINEXTENTS de la table.

Qu'as tu ?

Code :

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

1
2
3
SELECT DISTINCT initial_extent, minextents
FROM dba_subpartitions 
WHERE table_name ='MaTable'

[McM]

Je fais un test : Comme toi, malgré le minextents à 1
Une table de 19 extents, truncatée, ça reste à 19 au niveau segment, mais 1 seul extent

Code :

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

1
2
3
4
5
6
7
8
9
SELECT extents
FROM dba_segments
WHERE segment_name ='MATABLE'
=> 19
 
SELECT COUNT(*)
FROM dba_extents
WHERE segment_name = 'MATABLE'
=> 1

[McM]

Visiblement, le dba_segments n'est pas raffraichi lors du truncate

Code :

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

1
2
3
4
5
6
BEGIN
FOR i IN 1..10000
LOOP
	INSERT INTO MATABLE (qte) VALUES (i);
END LOOP;
END;

Code :

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

1
2
3
4
5
6
7
8
SELECT extents
FROM dba_segments
WHERE segment_name ='MATABLE'
=> 3
SELECT COUNT(*)
FROM dba_extents
WHERE segment_name = 'MATABLE'
=> 3

Code :

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

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
33
34
35
36
37
38
39
TRUNCATE TABLE MATABLE DROP STORAGE
SELECT extents
FROM dba_segments
WHERE segment_name ='MATABLE'
=> 3
SELECT COUNT(*)
FROM dba_extents
WHERE segment_name = 'MATABLE'
=> 1
 
BEGIN
FOR i IN 1..1000
LOOP
	INSERT INTO MATABLE (qte) VALUES (i);
END LOOP;
END;
SELECT extents
FROM dba_segments
WHERE segment_name ='MATABLE'
=> 3
SELECT COUNT(*)
FROM dba_extents
WHERE segment_name = 'MATABLE'
=> 1
 
BEGIN
FOR i IN 1..6000
LOOP
	INSERT INTO MATABLE (qte) VALUES (i);
END LOOP;
END;
SELECT extents
FROM dba_segments
WHERE segment_name ='MATABLE'
=> 2
SELECT COUNT(*)
FROM dba_extents
WHERE segment_name = 'MATABLE'
=> 2

Le dba_segment est donc raffraichi quand il doit recréer un extent.

[cquilgars]

McM... Un seul mot merci... Je n'étais pas au point de soupconner le comportement de rafraichissement de la vue DBA_SEGMENTS...

Tu sais... Dès qu'il y a DBA_*... Je fais confiance !!!

Une dernière question... Me conseilles-tu d'utiliser dba_extents pour déterminer (estimer est plus approprié) mon occupation de table ? (Je précise que tous mes extents sont de même taille pctincrease=0 dans ce but)

Sinon à ta question

Code :

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

1
2
3
SELECT DISTINCT initial_extent, min_extent
FROM dba_subpartitions 
WHERE table_name ='MaTable'

J'obtiens initial_extent=256Ko et min_extent=1

[cquilgars]

La requête que je propose est la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select latable,round(avg(taille))
from (
  select s.table_name as latable,
           s.partition_name ,
           count(*) as nb,
           sum  (e.BYTES)/1024/1024 as taille
   from dba_extents e,dba_tab_subpartitions s 
   where e.PARTITION_NAME=s.SUBPARTITION_NAME 
   group by s.table_name,s.partition_name)
where taille>10
group by latable

Elle permettrait d'obtenir la taille occupée à l'extent près (sachant que les miens sont sous-dimensionnés volontairement...)

[Marc Musette]

Visiblement, le dba_segments n'est pas raffraichi lors du truncate

tu crois vraiment que les vues dba ne sont pas remises à jour instantanément ... ce serait dangereux et je ne voudrais pas payer si cher pour un rdbms aussi ringard

ce que tu dis est FAUX ; reteste le et tu verras qu'après un truncate, dba_segments est bien mis à jour

comme le dit la doc, un truncate libère les extents (à concurrence du min extent) et reset la hwm .

pour connaître la taille d'un segment , utilise la procédure DBA_SPACE.SPACE_USAGE . (va voir la doc des supplied package pl/sql)

c'est la seule façon de connaître combien de blocs sont libres sur l'ensemble des blocs déjà alloués.

pour ce qui est de connaître la taille à l'extent près ... via DBA_EXTENTS, tu ne peux que connaîtrele nombre ALLOUE d'extents et pas véritablement utilisé

[cquilgars]

Bonjour à tous,

tu crois vraiment que les vues dba ne sont pas remises à jour instantanément ... ce serait dangereux et je ne voudrais pas payer si cher pour un rdbms aussi ringard

Je ne cherche pas à initier un troll. Es-tu bien certain ?
Je t'invite à faire le test de McM qui prouve notre observation...

Pour plus de certitude, je viens de refaire des tests sur ma base de données...
1) Soit une table avec 45 partitions
2) Les partitions sont sous-partitionnées en 3
3) Mon paramétrage et mon contexte

• min_extent=1
• initial_extent=256Ko
• pct_increase=0
• Taille des extents donc uniforme à 256Ko
• La taille de l'extent est sous-dimensionnée : 600Mo par partitions en situation nominale... Je ne suis donc pas à 256Ko près en marge d'erreur
• Je tiens à préciser que la table MaTable subira uniquement des INSERT, Pas DELETE, PAS UPDATE !!! Ainsi il n'y aura pas de extent vide mais pourtant alloué (a part celui encours de chargement...)

4) Avant chargement les requêtes donnent bien la même réponse attendue...
Requete N°1

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT s.table_name AS latable,
           s.partition_name,
           s.subpartition_name,
           count(*) AS nb,
           sum  (e.BYTES)/1024 AS taille
FROM dba_extents e,dba_tab_subpartitions s 
WHERE e.PARTITION_NAME=s.SUBPARTITION_NAME 
and s.table_name='MaTable'
GROUP BY s.table_name,s.partition_name,s.subpartition_name

Requête N°2

Code :

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

1
2
3
4
5
6
7
8
 
SELECT t.partition_name as "partition",
           t.subpartition_name as "sous partition",
           round(sum(bytes))/1024 as "Ko"
FROM dba_segments s,dba_tab_subpartitions t
WHERE segment_name ='MaTable'
and s.partition_name=t.subpartition_name
group by t.table_name,t.partition_name,t.subpartition_name

A savoir tout ce petit monde à 256Ko !!!

4) Je charge environ 100 000 lignes dans une des sous-partitions
5) Les mêmes requêtes me donne la sous-partition en question à 20Mo environ
6) Réalisation de l'opération

Code :

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

truncate table MaTable drop storage

7)

• La requête N°1 me donne 256Ko pour la sous-partition précédemment à 20Mo: Super ...
• La requête N°2 est tjs à 20Mo !!!

8) Alors... Verdict... Est-ce que Oracle est bien trop cher payé pour le service rendu ???

Tel un Teckel ne lâchant pas son os et compte-tenu de toutes ces infos, est-ce que cette requête vous semble appropriée pour mon cas (à l'approximation près)

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
  SELECT s.table_name AS latable,
           s.partition_name ,
           count(*) AS nb,
           sum  (e.BYTES)/1024/1024 AS taille
   FROM dba_extents e,dba_tab_subpartitions s 
   WHERE e.PARTITION_NAME=s.SUBPARTITION_NAME 
   AND table_name='MaTable'
   GROUP BY s.table_name,s.partition_name)

Encore merci de votre intérêt...
Il y a tjs quelque chose à apprendre dans une telle discussion... Même après 9 ans d'expérience sur Oracle acquis sur le tas (comme la majorité d'entre nous)

[McM]

Merci cquilgars, je suis en train de raffraichir notre base de dev donc je peux pas faire des tests pour l'instants. J'avais bien fait les tests et plutôt 2 fois qu'une vu les résultats.

Tu as oublié le dernier test :
Recharge 1000 lignes (ou juste ce qu'il faut pour augmenter d'un extent) et les 2 requetes seront alors identiques !

[pifor]

min_extent=1
initial_extent=256Ko
pct_increase=0
Taille des extents donc uniforme à 256Ko

Il serait aussi intéressant de savoir comment ont été créés les tablespaces.
Que donne la requête suivantes pour les tablespaces concernés:

Code :

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

1
2
3
 
select tablespace_name, extent_management, allocation_type, 
segment_space_management from dba_tablespaces;

[cquilgars]

Il serait aussi intéressant de savoir comment ont été créés les tablespaces.
Que donne la requête suivantes pour les tablespaces concernés:

Code :

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

1
2
3
 
select tablespace_name, extent_management, allocation_type, 
segment_space_management from dba_tablespaces;

Bonjour Pifor,

Je crois que ce nom me dit quelque chose !!!

La réponse à ta question est (et je m'excuse de ne pas avoir précisé cette info) la suivante : ils sont tous en LOCAL UNIFORM MANUAL

[rouardg]

Bonjour,

Pour votre information, il existe un bug Oracle en Oracle 9i relatif aux tablespaces en SEGMENT SPACE MANAGEMENT AUTO.

Donc si votre gestion des segments est en automatique, alors les informations remontées par DBA_SEGMENTS sont fausses après un DEALLOCATE UNUSUED ou un TRUNCATE. Seule DBA_EXTENTS donne les bonnes infos.

Vous pouvez aussi consulter Metalink à ce sujet, vous trouverez de nombreux articles.

[cquilgars]

Tu as oublié le dernier test :
Recharge 1000 lignes (ou juste ce qu'il faut pour augmenter d'un extent) et les 2 requetes seront alors identiques !

Pour info, je viens de refaire le test (négligé de ma part) avec 3 000 lignes injectées et je confirme le résultat identique des deux requêtes. Merci McM pour le tirage d'oreille...

Pour votre information, il existe un bug Oracle en Oracle 9i relatif aux tablespaces en SEGMENT SPACE MANAGEMENT AUTO.

OK c'est noté. Je te remercie de cette info...

Je vous remercie tous de votre participation... Je crois que mon problème est résolu...
A moins que vous ayez encore du grain à moudre à me fournir !!!

A bientôt,
Christophe