RE: locally managed with autoallocation
- From: "Adams, Matthew (GE Consumer & Industrial)" <MATT.ADAMS@xxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Feb 2004 12:25:54 -0500
rerun the same test with data file size being 1073807360
(1000M + 64K). and you will see 1000M available
----
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 12:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: locally managed with autoallocation
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:
