RE: locally managed with autoallocation
- From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Feb 2004 12:12:23 -0500
Hi Matt,
Maybe you have a different take on what I am seeing.
facd-SYSTEM>create tablespace test datafile size 1000m
2 uniform size 100m;
Tablespace created.
facd-SYSTEM>@ts
TS_NAME TS_MGMT ALLOC_TYP BLKSZ MB_USED MB_FREE
TOTAL_ALLOC MAX_AUTO
--------------- ---------- --------- ----- ---------- ----------
----------- ----------
SMALL_INDEX_A LOCAL SYSTEM 16K 140 1
141 65535
SMALL_INDEX_B LOCAL SYSTEM 16K 169 125
294 65535
SMALL_TABLE_A LOCAL SYSTEM 16K 582 322
904 65535
SYSTEM LOCAL SYSTEM 16K 158 41
200 500
TEMP LOCAL UNIFORM 16K 392 0
392 65535
TEST LOCAL UNIFORM 16K 100 900
1000 1000
TOOLS LOCAL SYSTEM 16K 0 99
100 65535
UNDO_A LOCAL SYSTEM 16K 1 4677
4678 65535
USERS LOCAL SYSTEM 16K 320 3
324 65535
31 rows selected.
facd-SYSTEM>create table testme (col1 date) tablespace test;
Table created.
facd-SYSTEM>@ts
TS_NAME TS_MGMT ALLOC_TYP BLKSZ MB_USED MB_FREE
TOTAL_ALLOC MAX_AUTO
--------------- ---------- --------- ----- ---------- ----------
----------- ----------
SMALL_INDEX_A LOCAL SYSTEM 16K 140 1
141 65535
SMALL_INDEX_B LOCAL SYSTEM 16K 169 125
294 65535
SMALL_TABLE_A LOCAL SYSTEM 16K 582 322
904 65535
SYSTEM LOCAL SYSTEM 16K 158 41
200 500
TEMP LOCAL UNIFORM 16K 392 0
392 65535
TEST LOCAL UNIFORM 16K 200 800
1000 1000
TOOLS LOCAL SYSTEM 16K 0 99
100 65535
UNDO_A LOCAL SYSTEM 16K 1 4677
4678 65535
USERS LOCAL SYSTEM 16K 320 3
324 65535
31 rows selected.
facd-SYSTEM>
Looks to me like the new table created caused another extent allocation,
suggesting the first extent isn't usable.
-----Original Message-----
From: Adams, Matthew (GE Consumer & Industrial)
[mailto:MATT.ADAMS@xxxxxxxxxxx]
Sent: Friday, February 27, 2004 12:02 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: locally managed with autoallocation
>, autoallocation helps me because when you set allocation type to
uniform, the first extent is used for the bitmap.
> So, for example I create a tablespace and set allocation type
uniform=200mb.
> The tablespace will immediately display with 200MB already used.
Are you sure of this? This is not what I'm seeing in my testing. As
long as I add
64K to a multiple of the extent size to hold the bitmap, it's fine.
using a size of 200m + 64K
create tablespace rma datafile '/db1_tst3/oracle/ts/data/rma.dbf' size
209780736
extent management local uniform size 50M;
Tablespace created
SQL> select bytes from sys.dba_free_space where tablespace_name = 'RMA';
BYTES
--------------
209715200
200 Meg available for use
----
Matt Adams - GE Appliances - matt.adams@xxxxxxxxxxx
When I'm around hard-core computer geeks, I want
to say, 'Come outside -- the graphics are great!'
- Matt Weinhold
-----Original Message-----
From: Koivu, Lisa [mailto:Lisa.Koivu@xxxxxxxxxxxxxxx]
Sent: Friday, February 27, 2004 11:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: locally managed with autoallocation
Hi Matt,
Well, for someone like me who doesn't have disk space to spare,
autoallocation helps me because when you set allocation type to uniform,
the first extent is used for the bitmap. So, for example I create a
tablespace and set allocation type uniform=200mb. The tablespace will
immediately display with 200MB already used.
Besides, the autoallocate extent sizes are all multiples of each other.
I use autoallocate and I am very happy with it. Wasted space is kept to
a minimum and the old rule of small/med/large objects in separate
tablespaces is out the window, really. Having small and large objects
in the same tablespace ensures that the small extents will indeed be
used.
If you have the time to do the proper analysis to determine the correct
extent size and categorize all your objects... well, more power to you.
It just seems to me doing this is extra effort for very little benefit.
Just my two cents... fwiw...
Lisa Koivu
Monkey Mama
Orlando, FL, USA
-----Original Message-----
From: Adams, Matthew (GE Consumer & Industrial)
[mailto:MATT.ADAMS@xxxxxxxxxxx]
Sent: Friday, February 27, 2004 11:37 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: locally managed with autoallocation
I know we are WAY behind the times here, still using
dictionary managed tablespaces for everything. I'm starting to
evaluate methods for setting up tablespaces and have to
ask this:
Why would anyone use autoallocation with locally managed
tablespaces when it appears to be open to the same kind
of honeycombing fragmentation problems that exist with
dictionary tablespaces that don't use uniform extent sizes?
Natt
----
Matt Adams - GE Appliances - matt.adams@xxxxxxxxxxx
When I'm around hard-core computer geeks, I want
to say, 'Come outside -- the graphics are great!'
- Matt Weinhold
"The sender believes that this E-Mail and any attachments were free of
any virus, worm, Trojan horse, and/or malicious code when sent. This
message and its attachments could have been infected during
transmission. By reading the message and opening any attachments, the
recipient accepts full responsibility for taking proactive and remedial
action about viruses and other defects. The sender's business entity is
not liable for any loss or damage arising in any way from this message
or its attachments."
"The sender believes that this E-Mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading the
message and opening any attachments, the recipient accepts full responsibility
for taking proactive and remedial action about viruses and other defects. The
sender's business entity is not liable for any loss or damage arising in any
way from this message or its attachments."
Other related posts:
