Space Usage Question
sp...@no-spam.com
I have a puzzeling question re space usage on a table. we use oracle
8.1.7.4 on Sparc Solaris 8
The tablespace is LMT with 64M extents with autoextend on
we have a table with
avg row len of 390 bytes and
no of rows is 6.7 million
table size 2.6 GB
Block size on the DB is 8K
The table defination is
TABLESPACE BHF
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
According to our stats the table span 223 extents, But that does not
make sense to me, because that mean each exten size is approx 11MB ?
Can any one in the know please explain. I am new to oracle, so please
do not flame me if the answer is ovious
sp...@no-spam.com
The tablespace has an autoallocate statement on it as well
Brian Peasland
This is the answer. When you create a LMT, you have two options, UNIFORM
extent sizes, or let Oracle AUTOALLOCATE extent sizes for you. With
AUTOALLOCATE, your extent sizes are typically 64K, 1MB, or 8MB (there
are situations where you can get other extent sizes, but that can muddy
up the conversation). The basic way it works is that Oracle initially
allocates a 64K extent. After you've allocated a number of 64K extents,
Oracle figures your table will be large so it starts allocating 1MB
extents. After you've allocated a number of 1MB extents, Oracle figures
your table will be really large, so it starts allocating 8MB extents.
With AUTOALLOCATE, you are not necessarily going to get 64K extents
across the board. If you really want to know how big your extents are
for this object, query DBA_EXTENTS.
HTH,
Brian
--
===================================================================
Brian Peasland
dba@remove_spam.peasland.com
Remove the "remove_spam." from the email address to email me.
"I can give it to you cheap, quick, and good. Now pick two out of
the three"
Daniel Morgan
>
> The table defination is
> TABLESPACE BHF
> PCTFREE 10
> PCTUSED 40
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 65536
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> FREELISTS 1 FREELIST GROUPS 1 )
>
> According to our stats the table span 223 extents, But that does not
> make sense to me, because that mean each exten size is approx 11MB ?
>
> Can any one in the know please explain. I am new to oracle, so please
> do not flame me if the answer is ovious
Go with Brian's response to your inquiry but I take a serious look at
your PCTFREE and PCTUSED. These are default values that likely have
absolutely nothing to do with your data.
Seriously reconsider these values ... you are likley wasting a lot of disk.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
rc@die@you@!spammers.sandworm.demon.co.uk
<damo...@x.washington.edu> wrote:
>sp...@no-spam.com wrote:
>>
>> The table defination is
>> TABLESPACE BHF
>> PCTFREE 10
>> PCTUSED 40
>> INITRANS 1
>> MAXTRANS 255
>> STORAGE (
>> INITIAL 65536
>> MINEXTENTS 1
>> MAXEXTENTS 2147483645
>> FREELISTS 1 FREELIST GROUPS 1 )
>>
>> According to our stats the table span 223 extents, But that does not
>> make sense to me, because that mean each exten size is approx 11MB ?
>>
>> Can any one in the know please explain. I am new to oracle, so please
>> do not flame me if the answer is ovious
>
>Go with Brian's response to your inquiry but I take a serious look at
>your PCTFREE and PCTUSED. These are default values that likely have
>absolutely nothing to do with your data.
>
>Seriously reconsider these values ... you are likley wasting a lot of disk.
For performance would I be better off with UNIFORM exten of 64M ?
Howard J. Rogers
<rc@die@you@!spammers.sandworm.demon.co.uk> wrote in message
news:nd4g10tge9pcan6fs...@4ax.com...
No, not at all. First, Daniel must have taken some pills or something this
morning, because there is absolutely nothing wrong with the default settings
for PCTFREE and PCTUSED, and if there were it would be a question of segment
header contention, row migration problems, or full table scan poor
performance, not disk space wastage. Those parameters are for performance
issues, not space saving ones.
Secondly, there is no performance impact whatsoever from allocating mixed
extent sizes versus uniform ones. None, nada, niet, rien, zilch. Extent
sizes and numbers have never had an impact on performance, except when they
got stupidly large in number, and your data dictionary had to cope with the
strain of dealing with them all. But in LMT, there is no difference
whatsoever to performance.
Stick with autoallocate. It is a nice algorithm, and thoroughly recommended.
And then try to stop worrying about numbers and sizes of extents altogether,
because those are the sorts of things DBAs used to worry about 6 or 7 years
ago. You have far more important things to do with your time these days!!
Regards
HJR
Richard Foote
news:40181d59$0$5862$afc3...@news.optusnet.com.au...
Just to add to what Howard has said, autoallocate behind the covers actually
uses a uniform size of 64K. Each bitmap corresponds to a "chunk" of 64K,
similar to how a uniform size of 64K is allocated, the only significant
difference being the autoallocate algorithm could request multiple
occurrences of 64K chunks as the segment grows.
Cheers
Richard
Richard Foote
news:rc6Sb.33415$Wa.1...@news-server.bigpond.net.au...
> "Howard J. Rogers" <h...@dizwell.com> wrote in message
> news:40181d59$0$5862$afc3...@news.optusnet.com.au...
> >
> > >
And just to add to what I had said.
There has always been an issue with suggestions that autoallocate could lead
to fragmentation due to the fact that it allocates extents of differing
sizes. Well, I had a little play with this today (Windows, Oracle 9.2)
I created an autoallocate LMT tablespace (200M), created two tables (A and
B) and allocated in turn extents to each table such that the extents for
each table were multiplexed within tablespace (eg. A,B,A,B,A,B etc).
Eventually both tables were creating 8M extents (3 each) before I got an
unable to extend table error for both tables.
I then created a new table (C) just to gobble up all remaining space. The
tablespace has now no remaining space.
I then dropped table B, creating a fragmented mess of free space of
differing sizes (64K, 1M and 3 extents of 8M).
I then tried to allocate a new extent for table A and it succeeded, using
the first 8m free chunk.
The next 2 allocations also succeeded using up the remaining 2 8M chunks to
free space.
Would the next allocation succeed as I now only had 64K and 1M areas of free
space ?
The answer is a resounding "YES" !!
It grabbed a 1M extent. As did the next allocation, and the next and the
next ...
So faced with kinda wanting 8M of free space but not being able to get it
"contiguously" within the tablespace, the autoallocate algorithm was quite
happy to accept the next biggest piece of free space available. In fact all
the remaining free space was able to be utilized by the table A.
Conclusion ?
That the so-called "disadvantage" of autoallocate causing fragmentation
issues is somewhat exaggerated and (as far as my little experiment showed)
is a non-issue when considering using autoallocate.
If anyone wants me to send/post the actual test, let me know (bedtime now
!!)
Cheers
Richard
Jonathan Lewis
I don't know if this has already been mentioned,
but another strategy that Oracle uses to minimise
holes is that an extent of size X is always allocated
on a boundary at size X. So an 8M boundary is
allocated on a 8M boundary in the file (allowing for
the 64K header), a 1M boundary is allocated on
a 1M boundary. So even in a messy cycle of
allocation and dropping, you tend to end up with
a small number of (temporarily) empty 64K extents,
a small number of 1M, and so on, rather than loads
of them all over the place. And, of course, as soon
as you have more than 30 consecutive empty 64K
chunks, that guarantees the existence of some
space that could go into a single 1M chunk.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Richard Foote" <richar...@bigpond.com> wrote in message
news:IUrTb.39847$Wa.3...@news-server.bigpond.net.au...
Niall Litchfield
> There has always been an issue with suggestions that autoallocate could
lead
> to fragmentation due to the fact that it allocates extents of differing
> sizes. Well, I had a little play with this today (Windows, Oracle 9.2)
> Would the next allocation succeed as I now only had 64K and 1M areas of
free
> space ?
>
> The answer is a resounding "YES" !!
>
> It grabbed a 1M extent. As did the next allocation, and the next and the
> next ...
>
> So faced with kinda wanting 8M of free space but not being able to get it
> "contiguously" within the tablespace, the autoallocate algorithm was quite
> happy to accept the next biggest piece of free space available. In fact
all
> the remaining free space was able to be utilized by the table A.
>
> Conclusion ?
>
> That the so-called "disadvantage" of autoallocate causing fragmentation
> issues is somewhat exaggerated and (as far as my little experiment showed)
> is a non-issue when considering using autoallocate.
>
> If anyone wants me to send/post the actual test, let me know (bedtime now
> !!)
Morning Richard :(
I have different results. I attempted to fill up an autoallocate tablespace
with 32 tables - 16 extents each, drop half the tables and then allocate a
17th extent to table1.
SQL> set echo on
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL>
SQL> create tablespace auto_alloc_test
2 datafile 'c:\oracle\oradata\nl9204\auto_alloc.dbf' size 32832k
3 extent management local;
Tablespace created.
SQL>
SQL> /*
DOC>create the tables
DOC>*/
SQL>
SQL> begin
2 for i in 1..32 loop
3 execute immediate 'create table table'||i||'(col1 number,col2 number)
tablespace auto_alloc_test';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sum(bytes)/1024 free_k from dba_free_space where
tablespace_name='AUTO_ALLOC_TEST';
FREE_K
----------
30720
SQL>
SQL> begin
2 for i in 1..15 loop
3 for j in 1..32 loop
4 execute immediate 'alter table table'||j||' allocate extent';
5 end loop;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where
tablespace_name='AUTO_ALLOC_TEST';
FREE_M
----------
SQL>
SQL> begin
2 for i in 1..32 loop
3 if i mod 2 = 0 then
4 execute immediate 'drop table table'||i;
5 end if;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where
tablespace_name='AUTO_ALLOC_TEST';
FREE_MB
----------
16
SQL>
SQL> alter table table1 allocate extent;
alter table table1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table NIALL.TABLE1 by 64 in tablespace
AUTO_ALLOC_TEST
SQL>
SQL> drop tablespace auto_alloc_test including contents and datafiles;
Tablespace dropped.
SQL>
SQL> spool off
Richard Foote
news:401e606c$0$9392$ed9e...@reading.news.pipex.net...
Hi Niall,
Thanks for the example.
I followed it and too got Oracle to fail to reuse the 64K chunks.
Little more digging to go ....
Cheers ;)
Richard
Niall Litchfield
> Hi Niall,
>
> Thanks for the example.
>
> I followed it and too got Oracle to fail to reuse the 64K chunks.
>
> Little more digging to go ....
>
> Cheers ;)
Hey, I showed you mine, will you show me yours? offline if you wish.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Richard Foote
news:40215d51$0$42$cc9e...@news.dial.pipex.com...
> "Richard Foote" <richar...@bigpond.com> wrote in message
> news:_U5Ub.42357$Wa.2...@news-server.bigpond.net.au...
> > "Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in
message
> <snip>
> > Hi Niall,
> >
> > Thanks for the example.
> >
> > I followed it and too got Oracle to fail to reuse the 64K chunks.
> >
> > Little more digging to go ....
> >
> > Cheers ;)
>
> Hey, I showed you mine, will you show me yours? offline if you wish.
Hi Niall
Sure !!
Here goes ....
SQL> create tablespace bowie_auto datafile 'c:\tmp\bowie_auto.dbf' size 200m
2 autoallocate;
Tablespace created.
SQL> create table ziggy (x number) tablespace bowie_auto;
Table created.
SQL> create table aladdin (x number) tablespace bowie_auto;
Table created.
SQL> begin
2 for i in 1..100 loop
3 execute immediate 'alter table ziggy allocate extent';
4 execute immediate 'alter table aladdin allocate extent';
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table BOWIE.ZIGGY by 1024 in tablespace
BOWIE_AUTO
ORA-06512: at line 3
SQL> select tablespace_name, blocks from dba_free_space where
tablespace_name =
'BOWIE_AUTO';
TABLESPACE_NAME BLOCKS
------------------------------ ----------
BOWIE_AUTO 1016
SQL> create table fill_rest (x number) storage (initial 7m) tablespace
bowie_aut
o;
Table created.
SQL> create table fill_rest_2 (x number) storage (initial 960k) tablespace
bowie
_auto;
Table created.
SQL> select tablespace_name, blocks from dba_free_space where
tablespace_name =
'BOWIE_AUTO';
no rows selected
**** I simply created the above two tables to fill up all remaining space in
the tablespace ****
SQL> select segment_name, blocks, count(*) from dba_extents where
tablespace_nam
e = 'BOWIE_AUTO' group by segment_name, blocks;
SEGMENT_NA BLOCKS COUNT(*)
---------- ---------- ----------
ZIGGY 8 16
ZIGGY 128 63
ZIGGY 1024 4
ALADDIN 8 16
ALADDIN 128 63
ALADDIN 1024 4
FILL_REST 128 7
FILL_REST_ 8 15
2
**** You can see that both tables ZIGGY and ALADDIN have reached the 8M
extent requirement ****
SQL> drop table aladdin;
Table dropped.
SQL> select tablespace_name, blocks, count(*) from dba_free_space where
tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;
TABLESPACE_NAME BLOCKS COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO 8 16
BOWIE_AUTO 128 63
BOWIE_AUTO 1024 4
SQL> alter table ziggy allocate extent;
Table altered.
SQL> alter table ziggy allocate extent;
Table altered.
SQL> alter table ziggy allocate extent;
Table altered.
SQL> alter table ziggy allocate extent;
Table altered.
SQL> select tablespace_name, blocks, count(*) from dba_free_space where
tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;
TABLESPACE_NAME BLOCKS COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO 8 16
BOWIE_AUTO 128 63
**** I've used up the four free 8m extents. The question now is will we be
able to continue now there are no more 8m areas of free space ???? ****
SQL> alter table ziggy allocate extent;
Table altered.
SQL> alter table ziggy allocate extent;
Table altered.
SQL> alter table ziggy allocate extent;
Table altered.
SQL> select tablespace_name, blocks, count(*) from dba_free_space where
tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;
TABLESPACE_NAME BLOCKS COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO 8 16
BOWIE_AUTO 128 60
*** And the answer is YES. The next three requested extents have gone back
to using just the 1M extents as they're the best we can do. ****
SQL> begin
2 for i in 1..60 loop
3 execute immediate 'alter table ziggy allocate extent';
4 end loop;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table BOWIE.ZIGGY by 1024 in tablespace
BOWIE_AUTO
ORA-06512: at line 3
SQL> select segment_name, blocks, count(*) from dba_extents where
tablespace_nam
e = 'BOWIE_AUTO' group by segment_name, blocks;
SEGMENT_NA BLOCKS COUNT(*)
---------- ---------- ----------
ZIGGY 8 16
ZIGGY 128 120
ZIGGY 1024 8
FILL_REST 128 7
FILL_REST_ 8 15
2
SQL> select tablespace_name, blocks, count(*) from dba_free_space where
tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;
TABLESPACE_NAME BLOCKS COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO 8 16
BOWIE_AUTO 128 6
**** Interestingly, once we hit 120 1M extents, Oracle decides enough is
enough and it really wants to go back to 8M extents again. Even though there
are a number of 1M chunks of storage still available ***
Like I said, a little more digging is required (on my part anyways) !!
Cheers
Richard
Mark D Powell
> "Niall Litchfield" <niall.li...@dial.pipex.com> wrote in message
> news:40215d51$0$42$cc9e...@news.dial.pipex.com...
> > "Richard Foote" <richar...@bigpond.com> wrote in message
> > news:_U5Ub.42357$Wa.2...@news-server.bigpond.net.au...
> > > "Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in
> message
> <snip>
> > > Hi Niall,
> > >
>
> enough and it really wants to go back to 8M extents again. Even though there
> are a number of 1M chunks of storage still available ***
>
> Like I said, a little more digging is required (on my part anyways) !!
>
> Cheers
>
> Richard
Excellent example, but what version of Oracle was this ran on?
-- Mark D Powell --
Niall Litchfield
news:2687bb95.04020...@posting.google.com...
reproduces for me on 9204 on windows.
Niall Litchfield
we have both demonstrated that you *can* encounter the unusable free space
issue as a result of fragmentation of free space when using auto-allocate.
Richard has also shown that the circumstances under which this will occur
are not readily apparent <g>.
I think Jonathan Lewis pointed out that the pattern of extent allocation
under auto-allocate was subject to change from version to version , not sure
that it has yet changed, so at least as far as the fragmentation issue goes
I'm happy to stick for now with a general preference for ULMTs with
differing uniform sizes if necessary for forecasting.
cheers