LMT - Space Management

Printable View

02-07-2003, 01:49 PM
Jr

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.
02-07-2003, 02:03 PM
gandolf989

Re: LMT - Space Management

Quote:

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.
02-07-2003, 02:46 PM
Jr

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.
02-07-2003, 03:07 PM
slimdave

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
02-07-2003, 03:15 PM
Jr

Thanx slimdave.
02-07-2003, 04:24 PM
gandolf989

Quote:

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.
02-07-2003, 04:25 PM
gandolf989

Quote:

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.
02-07-2003, 05:36 PM
slimdave

the NEXT sizes for the file should be 1024MB, shouldn't they, not 1025?
02-07-2003, 05:39 PM
gandolf989

Quote:

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.
02-07-2003, 06:11 PM
Orca777

Re: LMT - Space Management

Quote:

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
:cool:
02-07-2003, 06:28 PM
jmodic

gandolf989, Orca777,

Can't you see the point in Jr.'s question????? Why are you stuck by the size of the extents he has used in his example? He only used an example with very exagerated extent sizes to make his point/confusion more obvious, isn't this eviden't?

Slimdave has given him a perfectly clear explanation what's causing his confusion.
02-08-2003, 02:56 PM
Orca777

Quote:

Originally posted by jmodic
gandolf989, Orca777,

Can't you see the point in Jr.'s question????? Why are you stuck by the size of the extents he has used in his example? He only used an example with very exagerated extent sizes to make his point/confusion more obvious, isn't this eviden't?

Slimdave has given him a perfectly clear explanation what's causing his confusion.

Sorry who can really read can do it!
Sorry

Orca
:confused:
02-10-2003, 10:11 AM
gandolf989

Quote:

Originally posted by jmodic
gandolf989, Orca777,

Can't you see the point in Jr.'s question????? Why are you stuck by the size of the extents he has used in his example? He only used an example with very exagerated extent sizes to make his point/confusion more obvious, isn't this eviden't?

Slimdave has given him a perfectly clear explanation what's causing his confusion.

Ok so his point was he didn't understand about the overhead of storing the bitmap in the datafile. However, his example led me to believe that he didn't understand the concept of LMT's. Maybe you are a better mind reader than me. :rolleyes: