-
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.
-
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.
-
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.
-
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
-
-
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.
-
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.
-
the NEXT sizes for the file should be 1024MB, shouldn't they, not 1025?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|