Oracle 10g = bloatware?

Matthias Hoys

unread,

Apr 11, 2012, 10:37:49 AM4/11/12

to

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

Kay Kanekowski

unread,

Apr 11, 2012, 11:42:58 AM4/11/12

to

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

ddf

unread,

Apr 11, 2012, 5:41:46 PM4/11/12

to

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

onedbguru

unread,

Apr 11, 2012, 9:39:47 PM4/11/12

to

OR, they have have had a LOT of inserts/deletes over time and never did a shrink space to clean it up (assuming ASSM).

Matthias Hoys

unread,

Apr 12, 2012, 4:00:53 AM4/12/12

to

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

Matthias

Matthias Hoys

unread,

Apr 12, 2012, 4:10:35 AM4/12/12

to

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

Matthias

Matthias Hoys

unread,

Apr 12, 2012, 4:06:45 AM4/12/12

to

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

Matthias

Mladen Gogala

unread,

Apr 12, 2012, 2:28:16 PM4/12/12

to

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.

--
http://mgogala.byethost5.com

Matthias Hoys

unread,

Apr 13, 2012, 4:19:33 AM4/13/12

to

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;

WWV_FLOW_FILE_OBJ_PK
SEGMENT_NAME,MB
WWV_FLOW_FILE_OBJ_PK,0.3125
WWV_FLOW_FILES_USER_IDX,0.375
SYS_C004982,0.5
WWV_FLOW_FILES_FILE_IDX,0.5625
SYS_IL0000050125C00017$$,3
WWV_FLOW_FILE_OBJECTS$,10
SYS_LOB0000050125C00017$$,931

Matthias

Mladen Gogala

unread,

Apr 13, 2012, 8:52:35 AM4/13/12

to

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.

--
http://mgogala.byethost5.com

Jonathan Lewis

unread,

Apr 29, 2012, 5:43:11 AM4/29/12

to

"Mladen Gogala" <gogala...@gmail.com> wrote in message
news:jm76s0$pq1$2...@solani.org...

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.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543