DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: LMT - Space Management

  1. #1
    Join Date
    Jan 2002
    Posts
    148

    Smile LMT - Space Management

    Hi All,
    After a long time i have to take dbasupport guru's advice, so i am here....

    Create tablespace t1 datafile 'C:\TEST.DBF'
    size 11M autoextend on next 1M maxsize unlimited
    extent management local uniform size 10M
    /
    Create tablespace t2 datafile 'C:\TEST2.DBF'
    size 3072M autoextend on next 201M maxsize unlimited
    extent management local uniform size 1024M
    /

    select tablespace_name,bytes from dba_free_space
    /
    TABLESPACE_NAME BYTES
    --------------- --------
    T1 (datafile_size - 1M)
    T2 (datafile_size - 1024M)

    I wonder, is this is normal behaviour ?
    I can understand with t1, but why T2 should freeze 1GB ?
    For a new table (lesser of 3GB , enough to give room for bitmap maintanance ) being created in T2 is resulting in extending the datafile instead of using current 3GB space.

    Is this is a bug or something in 9.2.0.2.1 or did i missed any basics.

    Thanx in advance
    Jr.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: LMT - Space Management

    Originally posted by Jr
    Create tablespace t1 datafile 'C:\TEST.DBF'
    size 11M autoextend on next 1M maxsize unlimited
    extent management local uniform size 1M
    /
    Create tablespace t2 datafile 'C:\TEST2.DBF'
    size 3072M autoextend on next 201M maxsize unlimited
    extent management local uniform size 10M
    /
    I think that you are confused about LMT's. The uniform size means that every extent created will be that size. So for t1 You gave it enough room for 1 table with one extent without it having to extend 9 times to allow the second extent. For t2 you gave it enough room for 3 extents before that tablespace needs to extend 5 times so that another extent can be created. If you have a table that is 1gb then it should be in a tablespace with a uniform extent size of 100mb, or possibly even 50mb. You want to set up your tablespaces so that the number of extents is less than 500 preferably less than 100.

  3. #3
    Join Date
    Jan 2002
    Posts
    148
    Thanx for the reply.

    The "QUOTE" in your reply has got deviated from the original posting.
    And your advice has no match with the original form and difficult to understand.

    Hold the Question to the first form.

    I am pasting a real time example here to make it more brief.

    Code:
    create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 1025M autoextend on next 101M maxsize unlimited 
    extent management local uniform size 1024M;
    
    Tablespace created.
    
    SQL> select bytes from dba_free_space where tablespace_name='TEST';
    
              BYTES
    ---------------
      1,073,741,824
    
    SQL> drop tablespace test including contents and datafiles;
    
    Tablespace dropped.
    
    SQL> create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 3072M autoextend on next 101M maxsize unlimited  
    extent management local uniform size 1024M;
    
    Tablespace created.
    
    SQL> select bytes from dba_free_space where tablespace_name='TEST';
    
              BYTES
    ---------------
      2,147,483,648
    The abnormal behaviour of freezing 1GB space is questionable.

    Thanx
    Jr.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    in an LMT you need an extra 64kb to hold the bitmap. create your file as (3*1024*1024)+64 kb = 3145792 kb. Your "one gig" file works because you added an extra 1mb on to it
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2002
    Posts
    148
    Thanx slimdave.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by Jr
    [code]
    create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 1025M autoextend on next 101M maxsize unlimited
    extent management local uniform size 1024M;

    SQL> create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 3072M autoextend on next 101M maxsize unlimited
    extent management local uniform size 1024M;

    If you are going to do this then you should change the next to reflect the size of your uniform extent. Like:

    [code]
    create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 1025M autoextend on next 1025M maxsize unlimited
    extent management local uniform size 1024M;

    SQL> create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 3072M autoextend on next 1025M maxsize unlimited
    extent management local uniform size 1024M;


    You should relize that anytime a table grows beyond 1gb or anytime you create a table you will allocate another 1gb of storage. Even for a 50K file. That is why my example specified 1mb or 10mb uniform extents. So that you can have many extents in the tablespaces without growing the table every time. But it really depends on how many tables you plan to store in this tablespace and how large each one will be. The way you have it setup the storage will not be very granular. Which may or may not be a problem. If you plan on storing a 1gb table in this tablespace you will not get any better performance with 1gb uniform extents as compared with eith 100mb or 10mb extents. Making a smaller uniform extent size will allow tables to grow by smaller chunks.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by gandolf989
    If you are going to do this then you should change the next to reflect the size of your uniform extent. Like:

    [code]
    create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 1025M autoextend on next 1025M maxsize unlimited
    extent management local uniform size 1024M;

    SQL> create tablespace test datafile 'F:\ORACLE\ORADATA\ASP2\TEST.DBF'
    size 3072M autoextend on next 1025M maxsize unlimited
    extent management local uniform size 1024M;


    You should relize that anytime a table grows beyond 1gb or anytime you create a table you will allocate another 1gb of storage. Even for a 50K file. That is why my example specified 1mb or 10mb uniform extents. So that you can have many extents in the tablespaces without growing the table every time. But it really depends on how many tables you plan to store in this tablespace and how large each one will be. The way you have it setup the storage will not be very granular. Which may or may not be a problem. If you plan on storing a 1gb table in this tablespace you will not get any better performance with 1gb uniform extents as compared with either 100mb or 10mb extents. Making a smaller uniform extent size will allow tables to grow by smaller chunks.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    the NEXT sizes for the file should be 1024MB, shouldn't they, not 1025?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by slimdave
    the NEXT sizes for the file should be 1024MB, shouldn't they, not 1025?
    You are probably right. Although I think that the uniform extent size for a 1gb tablespace should really be 10mb or less.

  10. #10
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448

    Re: LMT - Space Management

    Originally posted by Jr
    Hi All,
    extent management local uniform size 10M

    extent management local uniform size 1024M

    Jr.
    Hi,
    i was shocked reading the amount of size for unforme-size
    Even you have a big segments 1M should be enaugh.
    I had very bad expierience reorganizing semgments with parallel degrees > 1
    Because Oracle allocates for every parallel process in the new segment 1 Uniform-size. so if uniform -size is to big you have only air in your new segment.

    Orca

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width