> This is on 10g:
> select sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES';
> MB
> 945.75
> This is on 11gR2, after export/import using Data Pump:
> select sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES';
> MB
> 211.75
> ??? The number of rows are the same, tablespace properties too...
> Matthias Hoys
Hi Matthias,
what is the right posting ? In your posting from 16:35 the 10g sum is 211 MB and 11g sum is 945 MB.
If the sums in this thread are real then i expect there will lot a of free space in the dba_extents. And the export/import is the old fashioned way to elimante this free space. Test it in the 10g database with some 'alter table ... move' statement. The sum of bytes in dba_extents will be smaller than now.
> > This is on 10g:
> > select sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES';
> > MB
> > 945.75
> > This is on 11gR2, after export/import using Data Pump:
> > select sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES';
> > MB
> > 211.75
> > ??? The number of rows are the same, tablespace properties too...
> > Matthias Hoys
> Hi Matthias,
> what is the right posting ? In your posting from 16:35 the 10g sum is
> 211 MB and 11g sum is 945 MB.
> If the sums in this thread are real then i expect there will lot a of
> free space in the dba_extents. And the export/import is the old
> fashioned way to elimante this free space. Test it in the 10g database
> with some 'alter table ... move' statement. The sum of bytes in
> dba_extents will be smaller than now.
> hth
> Kay
You have that backwards:
10g == 945 MB
11g == 211 MB
But I, too, am wondering why there is so much apparent free space in
the 10g copy of the table. Possibly someone did this in 10g:
alter table flow_files minimize records_per_block;
or maybe manual segment space management is in use in the 10g database
and the pctfree is set to a high value.
On Wednesday, April 11, 2012 5:41:46 PM UTC-4, ddf wrote:
> On Apr 11, 9:42 am, Kay Kanekowski <kay.kanekow...@web.de> wrote:
> > Am 11.04.2012 16:37, schrieb Matthias Hoys:
> > > This is on 10g:
> > > select sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES';
> > > MB
> > > 945.75
> > > This is on 11gR2, after export/import using Data Pump:
> > > select sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES';
> > > MB
> > > 211.75
> > > ??? The number of rows are the same, tablespace properties too...
> > > Matthias Hoys
> > Hi Matthias,
> > what is the right posting ? In your posting from 16:35 the 10g sum is
> > 211 MB and 11g sum is 945 MB.
> > If the sums in this thread are real then i expect there will lot a of
> > free space in the dba_extents. And the export/import is the old
> > fashioned way to elimante this free space. Test it in the 10g database
> > with some 'alter table ... move' statement. The sum of bytes in
> > dba_extents will be smaller than now.
> > hth
> > Kay
> You have that backwards:
> 10g == 945 MB
> 11g == 211 MB
> But I, too, am wondering why there is so much apparent free space in
> the 10g copy of the table. Possibly someone did this in 10g:
> alter table flow_files minimize records_per_block;
> or maybe manual segment space management is in use in the 10g database
> and the pctfree is set to a high value.
> David Fitzjarrell
OR, they have have had a LOT of inserts/deletes over time and never did a shrink space to clean it up (assuming ASSM).
On Wednesday, April 11, 2012 5:42:58 PM UTC+2, Kay Kanekowski wrote:
> Hi Matthias,
> what is the right posting ? In your posting from 16:35 the 10g sum is > 211 MB and 11g sum is 945 MB.
> If the sums in this thread are real then i expect there will lot a of > free space in the dba_extents. And the export/import is the old > fashioned way to elimante this free space. Test it in the 10g database > with some 'alter table ... move' statement. The sum of bytes in > dba_extents will be smaller than now.
> hth
> Kay
My second posting is the correct one - I did a reverse copy/paste in the first post, but I removed it immediately. alter table ... move might help indeed, but I wonder why it got so bloated in the first place. This is a table in a locally managed tablespace with automatic extent allocation and ASSM... lots of inserts, but almost no deletes or updates...
On Thursday, April 12, 2012 3:39:47 AM UTC+2, onedbguru wrote:
> OR, they have have had a LOT of inserts/deletes over time and never did a shrink space to clean it up (assuming ASSM).
This is a table from APEX - there are about 6800 records for 4 years of usage. Mostly inserts, no updates, some deletes... The tablespace is indeed using ASSM, with automatic extent allocation...
On Wednesday, April 11, 2012 11:41:46 PM UTC+2, ddf wrote:
> You have that backwards:
> 10g == 945 MB
> 11g == 211 MB
> But I, too, am wondering why there is so much apparent free space in
> the 10g copy of the table. Possibly someone did this in 10g:
> alter table flow_files minimize records_per_block;
> or maybe manual segment space management is in use in the 10g database
> and the pctfree is set to a high value.
> David Fitzjarrell
Yes, I got it backwards in my first post, sorry about that.
I'm almost sure no one executed that "alter table..." statement - I'm the only one who could have done it :-)
This is a table from APEX in a locally managed tablespace with automatic extent allocation and ASSM... there are lots of inserts on the table, but almost no deletes or updates...
On Thu, 12 Apr 2012 01:10:35 -0700, Matthias Hoys wrote:
> This is a table from APEX - there are about 6800 records for 4 years of
> usage. Mostly inserts, no updates, some deletes... The tablespace is
> indeed using ASSM, with automatic extent allocation...
> Matthias
Both 900MB and 200MB seem like an enormous allocation for 6800 records.
On Thursday, April 12, 2012 8:28:16 PM UTC+2, Mladen Gogala wrote:
> Both 900MB and 200MB seem like an enormous allocation for 6800 records.
I did some more research on this; the table itself is only 10MB. It's the "SYS_LOB0000050125C00017$$" segment which has all the air (the table has a BLOB column which stores uploaded images and scripts and such).
select segment_name,sum(bytes)/1024/1024 MB from dba_extents where owner = 'FLOWS_FILES' group by segment_name order by 2 asc;
On Fri, 13 Apr 2012 01:19:33 -0700, Matthias Hoys wrote:
> On Thursday, April 12, 2012 8:28:16 PM UTC+2, Mladen Gogala wrote:
>> Both 900MB and 200MB seem like an enormous allocation for 6800 records.
> I did some more research on this; the table itself is only 10MB. It's
> the "SYS_LOB0000050125C00017$$" segment which has all the air (the table
> has a BLOB column which stores uploaded images and scripts and such).
> select segment_name,sum(bytes)/1024/1024 MB from dba_extents where owner
> = 'FLOWS_FILES' group by segment_name order by 2 asc;
Mathias, Oracle doesn't manage space within the LOB segments the same way as the space in the table segments. LOB segments tend to grow and never to reuse space. You can compact them by using CASCADE option on the table, but not if they are stored as SECUREFILE. LOB segments are akin to file system directories and when row gets deleted or modified, the space seems to linger there, forever. One of the companies I was working for decided to keep CLOB documents in a PostgreSQL database and use Sphinx as the text indexing software. Unfortunately, Postgres has huge issues with the partitioning, optimizer and concurrency, so the decision was made to try MongoDB. Sadly, the company went bankrupt before the project was completed. The company that I am working for now is following the same path and does keep documents in MongoDB, but I am no longer in charge of the project so I can't tell you much about it. What I can tell you is that MongoDB also has space maintenance problems.
LOB columns are non-relational by their nature and are hard to maintain within the database which uses fixed size blocks for its free space maintenance. When you delete row within a table, the space within the block gets added to the free space within the block. The problem with having a LOB segment header and some kind of bitmap would be concurrency. Two transactions manipulating LOB columns would have to serialize on that header, rendering row locking useless. Oracle chose to leave it at that and not do anything for the space within the LOB segments. The right approach would be to provide a reorg tool that one could run from time to time to release space, but there is none as of yet. Hopefully, something will be available in 12c or 13f.
"Mladen Gogala" <gogala.mla...@gmail.com> wrote in message
news:jm76s0$pq1$2@solani.org...
| On Thu, 12 Apr 2012 01:10:35 -0700, Matthias Hoys wrote:
| >
| > This is a table from APEX - there are about 6800 records for 4 years of
| > usage. Mostly inserts, no updates, some deletes... The tablespace is
| > indeed using ASSM, with automatic extent allocation...
| >
| > Matthias
|
| Both 900MB and 200MB seem like an enormous allocation for 6800 records.
|
If the chunksize was set to 32KB then
6,800 * 32768 / 1048576 = 212.5
So 200MB would be perfectly reasonable - without getting the DDL for the object (or perhaps checking the block size, since the minimum chunksize is the block size) we can't say much about how unreasonable 200MB might be.
As for 900MB -
4 years usage with 6,800 rows seems to be a very small usage. It's not completely idiotic to imagine that at some point in the 4 years the number of rows climbed by a factor of 4.5 and then dropped - or that someone did something that updated all the rows a couple of times before committing or rolling back. If you really wanted to figure out whether or not this happened you could always dump the whole lobindex and take a close look at the key values.
