Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: locally managed with autoallocation

RE: locally managed with autoallocation

From: Adams, Matthew (GE Consumer & Industrial) <MATT.ADAMS_at_APPL.GE.COM>
Date: Fri, 27 Feb 2004 12:25:54 -0500
Message-ID: <F2AE82A3FD89334EA21F6C761C541A70678FAB@ap3expr06applge.appl.ge.com> 





rerun the same test with data file size being 1073807360
(1000M + 64K). and you will see 1000M available
 





Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
When I'm around hard-core computer geeks, I want
to say, 'Come outside -- the graphics are great!'




-----Original Message-----


From: Koivu, Lisa [mailto:Lisa.Koivu_at_Cendant-TRG.com]
Sent: Friday, February 27, 2004 12:12 PM
To: oracle-l_at_freelists.org


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_at_APPL.GE.COM] 


Sent: Friday, February 27, 2004 12:02 PM
To: 'oracle-l_at_freelists.org'


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_at_appl.ge.com
When I'm around hard-core computer geeks, I want
to say, 'Come outside -- the graphics are great!'




-----Original Message-----


From: Koivu, Lisa [mailto:Lisa.Koivu_at_Cendant-TRG.com]
Sent: Friday, February 27, 2004 11:50 AM
To: oracle-l_at_freelists.org


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_at_APPL.GE.COM] 


Sent: Friday, February 27, 2004 11:37 AM
To: 'oracle-l_at_freelists.org'


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_at_appl.ge.com 
When I'm around hard-core computer geeks, I want 
to say, 'Come outside -- the graphics are great!' 




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








Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Fri Feb 27 2004 - 11:24:25 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US