Thankx tom, this is very useful for us.

Very useful, thanks

perfect!

How do I find the datafiles attched to a temporary tablespace (locally managed)

Tom:
I have hardly seen anyone advocating for a non-zero value for PCTINCREASE parameter. Is there any specific situations which could benefit from a non zero pctincrease. Could you please explain the rationale of creation of this parameter in Oracle originally?

Thanks.

hi tom

In what circumstances a Locally managed table space
will an overhead. If you have a table which is having
millions of rows. but inserts and updates are very low(data downloads always through interfaces) compared to Query, then having LMT is advisable.

Locally managed tablespaces are without doubt one of the best inventions since sliced bread, and have saved me and my clients a lot of time and effort recently.

However, I have read on Metalink of a couple of bugs relating to placing rollback segments in locally managed tablespaces in 8i; sample text follows:

----------------Begin included text (Metalink Forum entry)--------
Also, be aware that there are some outstanding bugs with rollback segments in locally
managed tablespaces. Bug 1176609 was filed because the creation of rollback segments
in a locally managed tablespace fails with an ORA-1552 if another non-system RBS doesn't
exist/online in a dictionary managed tablespace. Actually if you do not have a non-system
rollback segment in a dictionary managed tablespace, you cannot create any new objects.
This bug is unresolved because there is a workaround available.

Bug 1021631 causes the
server session to crash when attempting to drop a rollback segment in a locally managed
tablespace. This bug is fixed in release 9i.

Melissa Holman
Oracle Support

-----------End included text---------------------

Now, the first bug is a bit of a trivial problem because there is a workaround for it (an annoying one, but a workaround, nonetheless).

The second one (1021631) is more concering, because I do not want to be in a position where I cannot drop a rollback segment for some reason. Therefore, I've recommended that we build our databases using dictionary management for rollback tablespaces. I always make sure that the storage parameters are sensible eg (INITIAL 1M NEXT 1M MINEXTENTS 20), so the segments are well behaved!

Do you have any views on this?

Tom,

Can you please explain when extents in the temp space are freed. I was under the impression that it was transaction based. Once a transaction is committed. That extent is not de-allocated but free to be used by another session.

We have a system that has a 6 GIG temp tablespace.
3-teir with 6 application servers (JDE)
500 connections
20-40 active sessions at a time.
Oracle 8.1.7

With the temp tablespace as type temporary. The space
gradually fills up till we get the error Unable to allocate extent in temp.

With type permanent, its almost always empty. The most I have seen in a one time is ~ 800 meg.

Which leads me to think that the temp segments stay locked for that session, not the transaction.

Could you clarify, Thanks for all the help you provide!!

Thank you for the detailed description, very useful.

Hi Tom,

You have been advocating a 0 pctincrease on dictionary managed tablespaces. I have a friend who once said "keep intial=next and pctincrease of 1. It is as good as zero and also makes sure that freespace coalesce happens automatically". Is this true ? Please clarify.

Thanks

Bharath

Excellent advice, Tom -- I've had the pctincrease/extent size debate with others, and I believe you are 100% correct.
I too have learned to love LMTs; took us over 2 hours one night to start an instance (after having to abort it to get it down) -- the vendor-installed db had tiny extent size in the dictionary-managed temp ts -- grew to over 49K extents! Since the sort_segment is dropped on clean shutdown or on startup after crash or shutdown abort, the dictionary had to mark those 49K extents as free, and it just couldn't deal with it. We even tried to offline drop the ts -- no luck; TAR analyst said we'd just have to wait. We switched to LMT, and don't have to worry about repeating that experience.

1. Only in LM temporary tablespace you can use temfiles. So, you can add/drop files without the tablespace being recreated. This feature is very useful.
2. RMAN doesn't backup temp files. Im my case it's about ~100Gb and it saves me time and tapes.

Tom, If I set maxextents unlimited on all of my tables in LMT and manage space at tablespace level, will there be any performance implication with regard to extent space management at the bitmap level? because the bitmap does not know max number of extents.
Even though for extent allocation and deallocation, data dictionary tables are not updated for LMT, data dictionary has to keep track of how many extents a table has at any point of time so some base tables need to be updated. still some overhead in using LMT? Thanks.

hi Sir ..
what is reason behind this ....

-----------------------------------------------------
When the SYSTEM tablespace is locally managed, you must define a default temporary
tablespace when creating a database.
A locally managed SYSTEM tablespace cannot be used for default temporary storage.
------------------------------------------
In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created.
It is possible to plug in a dictionary managed tablespace using the transportable feature, but
it cannot be made writable.

<quote>in an LMT you have no choice, maxextents is unlimited, period <quote>

so, one cannot specify maxextents 300 for example for tables in LMT? I did not know this. By default maxextents is unlimited for segments created in LMT? Thanks.

<b>well, to be picky about it -- you can specify it:</b>

ops$tkyte@ORA920> create table t ( x int ) storage ( maxextents 300 );

Table created.

<b>but it is totally ignored</b>


ops$tkyte@ORA920> select max_extents from user_tables where table_name = 'T';

MAX_EXTENTS
-----------
 2147483645

 

Tom,

My segments in autoallocate LMTs are set to:

MAX_EXTENTS
-----------
2147483645

I try to set them to UNLIMITED by I get:
ORA-25150: ALTERING of extent parameters not permitted

Database limits in the Reference Guide say segments can be UNLIMITED.

You mean they are unlimited, although max_extents is set as above ? Why is this number there ?

Now, I tried to set the LMT tablespace's datafile autoextensibility to unlimited and I got (pay attention to MXBYTES):

SQL> exec print_table ('select * from dba_data_files where tablespace_name=''USERS''')
------------------------------
FILE_NAME                     : /ora/92/oradata/oraprd/users01.dbf
FILE_ID                       : 9
TABLESPACE_NAME               : USERS
BYTES                         : 2092957696
BLOCKS                        : 255488
STATUS                        : AVAILABLE
RELATIVE_FNO                  : 9
AUTOEXTENSIBLE                : YES
MAXBYTES                      : 34359721984
MAXBLOCKS                     : 4194302
INCREMENT_BY                  : 1280
USER_BYTES                    : 2092892160
USER_BLOCKS                   : 255480
------------------------------

Sould I also understand it as "unlimited" ? 

I understood that the datafile size should be
64K + size of the extent *N like
If you create a locally managed tablespace with uniform size
5m -- make sure every datafile is sized to be:

64k + 5m * N

If we used Auto segment management with uniform extent what is the additional overhead assococited with the auto segment management? It Should be 64K for extenet management + 64K for segmant management?

"you should set the extent size on your temporary tablespaces to be in line
with your SORT_AREA_SIZE setting"
Tom, if sort_area_size = 1048576 (1M)
then the uniform extent size should be 1024K (1M)
or 1024K+db_block_size(8K)=1032K ?
This formula is on Metalink:
temp extent size =(n*sort_area_size)+db_block_size
Thanks.

Hi Tom,

I had a snapshot created in dictionary managed tablespace with 100 million rows. The performance was good.
Last week I rebuilt this snapshot in a locally managed tablespace , uniform size and even I reduced the records number (from 100 million to 30 million). I was sure that after these 2 major changes (LMT + reducing 2/3 of the table) I will gain performance but I have to admit that the performance is now even worst! Could be a problem related to LMT and snapshots?

Many thanks in advance!

Hi Tom,
I am revewing database one of our vandor created for testing. I found they using table storage clause and also using LMT. Somewhere i saw in your article , you said no need to use storage clause if you using LMT. Plase explain.

Thanks
Nick

exp:
,JOB_NBR_LAST_2 NUMBER(4) DEFAULT 0 NOT NULL
NEXT 1M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 120)
,CONSTRAINT UK_WOY_KEY UNIQUE (JOB_NBR
,WADJ_NBR_KEY)
USING INDEX TABLESPACE IN05
STORAGE(INITIAL 1M
NEXT 1M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 120)
,CONSTRAINT UK_WO_WJNBR UNIQUE (JOB_1ST_7
,JOB_NBR2
,ADJ_KEY)
USING INDEX TABLESPACE IND05
STORAGE(INITIAL 1M
NEXT 1M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 120))
TABLESPACE US05
STORAGE(INITIAL 24M
NEXT 2M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 120);

One more thing . One of the table WOF is 1GB in size in our test database . How i can calculat for production WOF table storage size when i am using LMT. You said its start with initial size. Can you please give any tips.

Thanks

Yes , You are right this is a table. So my understanding is just create table without any storage clause like below,

create table dept(deptno number primary key,dname varchar2(14),loc varchar2(13)) tablespace user01;

The second thing is the table size which i already calculated its just for keeping free space in our user01 tablespace for future growth. am i right?

thanks

Yes , You are right this is a table. So my understanding is just create table without any storage clause like below,

create table dept(deptno number primary key,dname varchar2(14),loc varchar2(13)) tablespace user01;

The second thing is the table size which i already calculated its just for keeping free space in our user01 tablespace for future growth. am i right?

thanks

Thanks again

Another little question is if you not using LMT and you need to calculate table storages sizes and you know the table size like my case 1gig per year , do you have any procedure to run and get all storages clause of this (wof) table or any thing else? means how you calculate tables storage like initial , next extents and min extents and maxextents , pctfree and pctused

Thanks in adv.

Nono i am asking if i using DMT then how you calculate table storage parameter and you know the table size like my case 1gig per year , do you
have any procedure to run and get all storages clause of this (wof) table or any
thing else? means how you calculate tables storage like initial , next extents
and min extents and maxextents , pctfree and pctused

Thanks in adv.

Thank
One thing just let you know if you using LMT and define table clause with next extant, oracle ignore it.

please commands

thanks

One more thing if you create table like you said "create table without storage clause in LMT". I did it but When i am seen this table through TOAD its showes all storage parameter of the table with default storage like below.
So my question is If I have big table like 1gig, what you thing still i will create table without storage clasue? or create table with small , mediam and large initial extents.

This is toad output.

CREATE TABLE TEST1 (
ACCT_NBR NUMBER (9) NOT NULL,
ACCT_NBR NUMBER (9) NOT NULL,
CONSTRAINT PK_TEST1
PRIMARY KEY ( ACCT_NBR, ACCT_NBR )
USING INDEX
TABLESPACE INDEX02 PCTFREE 10
STORAGE ( INITIAL 65536 ))
TABLESPACE USERS02
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
Here is my simple scripts.
CREATE TABLE test1(
ACCT_NBR NUMBER(9)
,ACCT_NBR NUMBER(9)
,CONSTRAINT pk_TEST1 PRIMARY KEY (ACCT_NBR
,ACCT_NBR)
USING INDEX TABLESPACE INDEX02)
TABLESPACE USERS02;

Thanks in advance for commands.

I am waiting your response. Thanks

Now I am going to create table only with initial extends like below.

,CONSTRAINT PK_ISR PRIMARY KEY (PU
,ISE
,POSN
,TYPE
,COLOR
,TION
,SEC
,CLAS
,STAT)
USING INDEX TABLESPACE INDEX01
STORAGE(INITIAL 2M))
TABLESPACE USERS01
STORAGE(INITIAL 6M);

Thanks for comments

Just last question.

Should i define storage clause when creating LMT tablespaces? or leave that with default 64K?
Thanks

Tom,

Can you explain the following?  I thought the overhead for each file in a locally managed tablespace with uniform extents was supposed to be 64k.  Thanks.

- Jack


SQL> select *
  2  from (
  3       select substr(a.tablespace_name,2,2) tablespace_name,
  4              b.extent_management,
  5              b.allocation_type,
  6              a.bytes,
  7              a.user_bytes,
  8              (a.bytes - a.user_bytes) overhead
  9       from dba_data_files a,
 10            dba_tablespaces b
 11       where a.tablespace_name = b.tablespace_name
 12       and b.extent_management = 'LOCAL'
 13       and b.allocation_type = 'UNIFORM'
 14       order by dbms_random.value
 15       )
 16  where rownum <= 10
 17  ;

TABLESPACE_NAME EXTEN ALLOCAT           BYTES      USER_BYTES        OVERHEAD
--------------- ----- ------- --------------- --------------- ---------------
RE              LOCAL UNIFORM   8,862,564,352   8,860,467,200       2,097,152
S_              LOCAL UNIFORM   8,820,621,312   8,818,524,160       2,097,152
RO              LOCAL UNIFORM   6,710,951,936   6,700,400,640      10,551,296
IS              LOCAL UNIFORM  35,425,091,584  35,337,011,200      88,080,384
ES              LOCAL UNIFORM     660,668,416     650,117,120      10,551,296
TA              LOCAL UNIFORM  15,560,867,840  15,550,382,080      10,485,760
DD              LOCAL UNIFORM   4,294,967,296   4,194,304,000     100,663,296
ER              LOCAL UNIFORM   4,072,669,184   4,071,620,608       1,048,576
ES              LOCAL UNIFORM     660,668,416     650,117,120      10,551,296
XT              LOCAL UNIFORM  11,093,934,080  11,083,448,320      10,485,760 

Tom,

Thanks.  That makes sense.  It looks like Oracle is taking 128k on my box.  Your thoughts?

- Jack


SQL> -------------------------------
SQL> -- Create tablespace test_a_ts.
SQL> -- Extent size is 1024k
SQL> -- Goal is 3 extents.
SQL> -- Assume overhead is 64k.
SQL> -- (1024 x 3) + 64 = 3136k
SQL> -------------------------------
SQL> 
SQL> drop tablespace test_a_ts including contents and datafiles;

Tablespace dropped.

SQL> 
SQL> create tablespace test_a_ts
  2     datafile '/u30/oradata/xxxx/test_a_01.dbf' size 3136k autoextend off
  3     extent management local uniform size 1m
  4  ;

Tablespace created.

SQL> 
SQL> ---------------------------
SQL> -- If above is correct,
SQL> -- usable_k should be 3072,
SQL> -- overhead_k should be 64.
SQL> ---------------------------
SQL> 
SQL> select bytes/1024 total_k,
  2         user_bytes/1024 usable_k,
  3         (bytes - user_bytes)/1024 overhead_k
  4  from dba_data_files
  5  where tablespace_name = 'TEST_A_TS'
  6  ;

         TOTAL_K         USABLE_K       OVERHEAD_K
---------------- ---------------- ----------------
           3,136            2,048            1,088

SQL> 
SQL> -------------------------------
SQL> -- Create tablespace test_a_ts.
SQL> -- Extent size is 1024k
SQL> -- Goal is 3 extents.
SQL> -- Assume overhead is 128k.
SQL> -- (1024 x 3) + 128 = 3200k
SQL> -------------------------------
SQL> 
SQL> drop tablespace test_a_ts including contents and datafiles;

Tablespace dropped.

SQL> 
SQL> create tablespace test_a_ts
  2     datafile '/u30/oradata/xxxx/test_a_01.dbf' size 3200k autoextend off
  3     extent management local uniform size 1m
  4  ;

Tablespace created.

SQL> 
SQL> ----------------------------
SQL> -- If above is correct,
SQL> -- usable_k should be 3072,
SQL> -- overhead_k should be 128.
SQL> ----------------------------
SQL> 
SQL> select bytes/1024 total_k,
  2         user_bytes/1024 usable_k,
  3         (bytes - user_bytes)/1024 overhead_k
  4  from dba_data_files
  5  where tablespace_name = 'TEST_A_TS'
  6  ;

         TOTAL_K         USABLE_K       OVERHEAD_K
---------------- ---------------- ----------------
           3,200            3,072              128

SQL> 

version: 9.0.1.4.0
platform: SunOS 5.8 (Solaris 64-bit)
default blocksize: 32768

Hello Tom.

I need to change a script that I used for creating a 8.1.7 database in which tablespaces were dictionary managed into another in which they must be locally managed, but I'm getting the ORA-03214 error ("File Size specified is smaller than minimum required"), the strange thing is that it doesn't matter how large I make it, but (after having ended the execution of the script) if I try the creation of the temp tablespace it fails again (with the same size) but if I increase the size only little it works.

This is the script I'm launching:

create database CSR
character set "WE8ISO8859P15"
datafile '/home/oracle/create/CSR/system/sysCSR.dbf' size 75M
logfile '/home/oracle/create/CSR/redo/redo1CSR.log' SIZE 500k,
'/home/oracle/create/CSR/redo/redo2CSR.log' SIZE 500k,
'/home/oracle/create/CSR/redo/redo3CSR.log' SIZE 500k;

alter tablespace "SYSTEM" default
storage ( maxextents unlimited );

@/u02/app/oracle/product/8.1.7/rdbms/admin/catalog.sql
@/u02/app/oracle/product/8.1.7/rdbms/admin/catproc.sql

create tablespace tsp_roll_CSR datafile
'/home/oracle/create/CSR/rbs/rollCSR.dbf'
size 100M;

# Create temporary rollback segment
create rollback segment tmp_rbs;
alter rollback segment tmp_rbs online;

# Create real rollback segments
create rollback segment rbs1CSR tablespace tsp_roll_CSR;
create rollback segment rbs2CSR tablespace tsp_roll_CSR;
create rollback segment rbs3CSR tablespace tsp_roll_CSR;
create rollback segment rbs4CSR tablespace tsp_roll_CSR;
alter rollback segment rbs1CSR online;
alter rollback segment rbs2CSR online;
alter rollback segment rbs3CSR online;
alter rollback segment rbs4CSR online;

# Delete temporary rollback segment
alter rollback segment tmp_rbs offline;
drop rollback segment tmp_rbs;

create tablespace tsp_d_01 logging datafile
'/home/oracle/create/CSR/tables/csr_datos_01.dbf'
size 40M extent management local;

create tablespace tsp_i_01 logging datafile
'/home/oracle/create/CSR/indexes/csr_indices_01.dbf'
size 40M extent management local;

create tablespace tsp_d_02 logging datafile
'/home/oracle/create/CSR/tables/csr_datos_02.dbf'
size 30M extent management local;

create tablespace tsp_i_02 logging datafile
'/home/oracle/create/CSR/indexes/csr_indices_02.dbf'
size 30M extent management local;

create tablespace tsp_d_03 logging datafile
'/home/oracle/create/CSR/tables/csr_datos_03.dbf'
size 40M extent management local;

create tablespace tsp_i_03 logging datafile
'/home/oracle/create/CSR/indexes/csr_indices_03.dbf'
size 30M extent management local;

create temporary tablespace tsp_temp tempfile
'/home/oracle/create/CSR/temp/tempCSR.dbf'
size 301M extent management local
uniform size 301M;

create user csr_admin
identified by csr
default tablespace tsp_d_01
temporary tablespace tsp_temp;

grant unlimited tablespace to csr_admin;
grant dba to csr_admin;

grant create any sequence to csr_admin;

connect system/manager
@/u02/app/oracle/product/8.1.7/rdbms/admin/catdbsyn.sql
@/u02/app/oracle/product/8.1.7/sqlplus/admin/pupbld.sql

connect sys/change_on_install
@/u02/app/oracle/product/8.1.7/rdbms/admin/catproc.sql

COMMIT;

i have a 3G tablespace with 2 tables 1G and 10 littles tables of 100K. My uniform size is 64K (select initial_extent from dba_data_files where tablespace='TB_XXX';)

does the uniform size matter?
will i improve perfomance if i change from 64K to 1M in the tablespace definition?
can i change the tablespace definition without drop the tablespace?
does it matter to have more than 100 extents?

thanks in advance Tom, you really help us.

Hi Tom,

I have a question on creating tablespaces.

Consider the foll 2 ways:

1. create tablespace <tsname> datafile 'c:\test1.dbf' size 1024m extent management local;

2. create tablespace <tsname> datafile 'c:\test1.dbf'
size 1024m reuse autoextend on next 500m maxsize unlimited
extent management local uniform size 500m;

Now if a table is created in this tablespace <tsname> by either of the above methods, and if we do a lot of inserts into this table, will there be a difference in performance between methods 1 and 2. pls explain in detail.


Thanks.

Hi Tom,

Great thread this. From what I learnt here I think I can summarise that if I have locally managed tablespaces with
autoextend on the datafiles and heaps of space on my drive where the tablespace recides then I don't have to worry about anything. Even if the datafiles become full they automatically extend as long as they find space on the disk.
And no fragmentation and no need for coaleasing.
Am I right or am I missing something here?

Thanks in advance.
Regards,
RD.

Back in August you stated that "it's really OK to have lots of extents with LMTs".

I just got an e-mail from an Oracle On-site person that was discussing 'too-many' extents in LMTs.

<quote>
Some things that can affect the performance of inserts are the extent size of the segment. If you know you are going to insert 8-9 million rows, this is likely going to be several hundred megabytes of space required. If you have very small extents, then you will spend a lot of time in the allocation process. Just for example, if you end up inserting 512MB of rows, then you would need to allocate 4096 128k extents ( a lot! ), or only 128 4M extents, or only 4 128M extents. So, you should size your table segment appropriately. I am telling you this because in my experience, this has been a common problem (people creating tables with the default values, which are usually too low).
</quote>

How can I respond to this? I am trying to create a partitioned table with 12 partitions, each based on month of the year, each having 7-9 million 350 character (approx) records, in a tablespace with 1M extents.

The management here doesn't understand the benefit of letting Oracle manage the tablespace through auto-allocate, and thinks that a table that is less than 10,000 bytes will live happily in an LMT with 4M uniform extents, because 'disk space is cheap.'

Is there a downside I can point out to them about having many small tables living in a 4M uniform tablespace? I tried to sell them on moving these little tables (there's probably about 2 dozen tables that are less than 1M in size) to an auto-allocated tablespace, and they thought that the work involved wouldn't be worth the benefit.

Is there a negative performance hit to having to join to a table in this situation? I can't really benchmark it, because they won't authorize the auto-allocated tablespace.

Great info Tom!
Request you to guide me please w.r.t. tuning the tablespaces
This is how it looks
1 select TABLESPACE_NAME,STATUS,CONTENTS,EXTENT_MANAGEMENT,
2* ALLOCATION_TYPE from dba_tablespaces
3 /

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN ALLOCATIO
------------------------------ --------- --------- ---------- ---------
SYSTEM ONLINE PERMANENT DICTIONARY USER
TOOLS ONLINE PERMANENT DICTIONARY USER
DRSYS ONLINE PERMANENT DICTIONARY USER
USER_DATA ONLINE PERMANENT LOCAL SYSTEM
USER_INDEX ONLINE PERMANENT DICTIONARY USER
TEMPORARY_DATA1 ONLINE TEMPORARY LOCAL UNIFORM
ROLLBACK_DATA ONLINE PERMANENT LOCAL UNIFORM
HISTORY ONLINE PERMANENT DICTIONARY USER
TEMP_PROD ONLINE TEMPORARY LOCAL UNIFORM

9 rows selected.

Think I should migrate the tablespaces to local using dbms_space_admin?

Thanks for your advice in advance

Hi Tom,

This set of questions and your responses have been very educative indeed. I have one related question.

You say in your initial response in this question,
"Quote"
1) you should set the extent size on your temporary tablespaces to be in line
with your SORT_AREA_SIZE setting. We "page" to temp in that size.

500m should be sufficient for that query in isolation -- if you have 1,000 users
running it that would be different.

2) correct, temp tablespaces should have fixed sized extents. Autoallocate
would not make sense since extents, once allocated, are not freed -- the system
manages them internally. You would have alot of different sized extents being
managed and that would just not be a good thing. They should be the same size
and should be the sort_area_size.
"UnQuote"

SORT_AREA_SIZE is deprecated in Oracle9i in favor of PGA_AGGREGATE_TARGET. In such a situation, do you suggest that the extent size for a Temporary Tablespace should be equivalent to PGA_AGGREGATE_TARGET value ? Or do you suggest the PGA_AGGREGATE_TARGET value to be a multiple of the temporary tablespace extent size ?

Hi Tom

Can we change UNIFORM SIZE for a tablespace without dropping and recreating tablespace?

if your answer is Yes, then what are the steps?

Tom,
I have got a locally managed tablespace and i tried creating some objects in it to test how *initial* and *minextents* make difference to a segment. Can you please explain this behaviour.

hr@ORCL.US.ORACLE.COM> create table test( a number )
2 storage ( initial 64k );

Table created.

hr@ORCL.US.ORACLE.COM>
hr@ORCL.US.ORACLE.COM> select EXTENT_ID, BYTES, BLOCKS from user_extents where
2 SEGMENT_NAME = 'TEST'
3 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8

hr@ORCL.US.ORACLE.COM>
hr@ORCL.US.ORACLE.COM> drop table test;
hr@ORCL.US.ORACLE.COM> create table test( a number )
2 storage ( initial 64k
3 minextents 2);

Table created.

hr@ORCL.US.ORACLE.COM> select EXTENT_ID, BYTES, BLOCKS from user_extents where
2 SEGMENT_NAME = 'TEST'
3 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 1048576 128
1 1048576 128

hr@ORCL.US.ORACLE.COM> drop table test;

Table dropped.

hr@ORCL.US.ORACLE.COM> create table test( a number )
2 storage ( initial 64k
3 minextents 3);

Table created.

hr@ORCL.US.ORACLE.COM> select EXTENT_ID, BYTES, BLOCKS from user_extents where
2 SEGMENT_NAME = 'TEST'
3 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 1048576 128
1 1048576 128
2 1048576 128

hr@ORCL.US.ORACLE.COM>

further the Oracle concepts documentation says this about extents in a LMT.
<quote>
The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for extents that are managed locally.
</quote>

So why is it making a differnce in my case. The number and size of extents allocated to a table change as i modify the minextents parameter.

Thanks,
Khalid

Thanks a lot for your reply.
Since the tablespace has the initial set as 64k then why is the table being allocated 1 MB sized extents. This behaviour appears very odd. Isn't there some documentaion that can give an explanation on this

hr@ORCL.US.ORACLE.COM> create table test( a number )
2 storage ( initial 64k
3 minextents 2);

Table created.

hr@ORCL.US.ORACLE.COM> select EXTENT_ID, BYTES, BLOCKS from user_extents where
2 SEGMENT_NAME = 'TEST'
3 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 1048576 128
1 1048576 128

Thanks,
Khalid

If I have multiple datafiles in a tablespace, is it true or is it a myth that oracle would allocate extents in a round robin fashion? If so, do you recommend that when creating a tablespace, one should consider creating it with multiple datafiles? thanks.

I have a question on "bytes" in dba_free_space on a locally managed UNIFORM allocation type
and AUTO segment space management tablespace.


I the information below on a 10g RAC database:

SQL> select min(bytes)/1048576,max(bytes)/1048576
  2  from dba_free_space where tablespace_name='INDEX_POD';

MIN(BYTES)/1048576 MAX(BYTES)/1048576
------------------ ------------------
                 4               6872

SQL>  select tablespace_name,extent_management,allocation_type,segment_space_management
  2   from dba_tablespaces 
  3  where tablespace_name='INDEX_POD';

TABLESPACE_NAME      EXTENT_MAN ALLOCATIO SEGMEN
-------------------- ---------- --------- ------
INDEX_POD            LOCAL      UNIFORM   AUTO

SQL> select block_size,initial_extent,next_extent,min_extents from dba_tablespaces
  2  where tablespace_name='INDEX_POD';

BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
---------- -------------- ----------- -----------
     16384        4194304     4194304           1
     

     
TSNAME                         EXT_MGMT         SIZE_MB       FREE_MB  MAX_FRAG FREE_PCT     FRAGS#
------------------------------ ---------- ------------- ------------- --------- -------- ----------     
INDEX_POD                      LOCAL         124,168.00     71,968.00   6872.00    57.96       1381


I was expecting the min(bytes) and the max(bytes) would be same for a UNIFORM allocation type.

Can you explain me why there would be a diffence in this case?

Thanks, 

Hi Tom,

Marry Christmas !!

We manage 8-9 clients on Oracle HRMS. And it seems our DBA Folks managed the database in Dictionary Managed Tablespaces rather than LMT. The version is

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

Per them for raw slices (which dont have file read buffer) performance degrade while managing the same on LMT.

Please comment about your views and ideas and if you want to share something more.

Thanks
Tanmoy

Hi Tom

in your book "begining oracle programing " there is a line
"Tablespaces may have different block sizes"
what does this mean actually
1)can a tablespace contain blocks of different sizes
or
2)2 different tablespaces can have different block sizes.

Thanks
sachin

Tom,

I migrated DMT to LMT and now I am dropping objects resides in that tablespace and importing it back?

Will it give all benefits of LMT ot still I need to drop the tablespace and recreate it and then import?

Please suggest

I am doing a 200GB schema export and import it back.
export runs for 26 hours and Import for 38 hours.

Planning to change plan to that lets import it back to migrated Tablespace as It is already LMT.

So we should not do that and recreate the LMT tablespace.

Do you think it is risky to do export and import?

Neeraj,

Why do exp/imp? ALTER TABLE ... MOVE followed by ALTER INDEX ... REBUILD for all the indexes is the way to go.

You may consider using NOLOGGING to speed things up, but make sure you understand the implications of doing so, particularly as they relate to recovery.

In my view, exp/imp isn't particuarly risky, it's just easier to do the ALTER TABLE and ALTER INDEX commands.

Also, you could parallelize the work to some degree, either via a PARALLEL hint or by running multiple concurrent commands in separate sessions. (But, DO NOT attempt to do both PARALLEL hint and multiple concurrent commands and multiple sessions. You'll kill your box in a hurry.)

Hope that helps,

-Mark

Hi Tom

I have used the following to create a tablespace with min, max extents and a pct increase.

CREATE TABLESPACE "TEMP_DEVP"
NOLOGGING
DATAFILE '<path>\TEMP_DEVP.ora' SIZE 100M
AUTOEXTEND
ON MAXSIZE UNLIMITED DEFAULT
STORAGE ( MINEXTENTS 5 MAXEXTENTS UNLIMITED PCTINCREASE 50 ) TEMPORARY

In this case, we have the access to increase the extents manually (if necessary), which is not possible in autoextend mode (managed automatically). Will this have any impact on the performance of the database / application which is accessed by 40 concurrent users at a given point of time - updating / accessing data, retrieving reports?

Thanks and Regards
Raghav

Tom,

Looks like I am late now as difficult to convience manager.

Appreciate if you suggest on index export and import as I think I can save sometime if Donot import indexes and recreate later with parallel option.

Tom,

Is adding 64K to file size for LMT with uniform size is still relevant in Oracle 10g R2?

So in my 9i databases I used the following statement:

create tablespace XXX logging datafile YYY size 102464K autoextend on maxsize 2048064K extent management local uniform size 1M segment space management auto;

Will this work the same way on 10g as it worked on 9i?

Tom,
Our databse version is 9.2.0.4 and we use LMT (uniform) for datafiles. As I know, in LMT we don't need to worry about fragmentation as oracle old version. But one of our senior DBA persists in reorganizing tables for defragmentation as
a big project -- moving tables to new tablespace with large initial extent, recreating indexes. The thought of this DBA is -- once table moved to new tablespace with large initial extent, Oracle accesses the table's blocks in one place and it will be much faster. I doubt about it because the data blocks always spread out physically on disk and extent is just something logically like tablespace. But I can't
convince this senior DBA. Could you help us to go the right direction?

Thanks very much.

Hi Tom

We know that in oracle an extent consists of several continuous blocks, but if these blocks are physically continuous in hard disk?

Thanks

Hi Tom

So it means that extents are not physically continuous too, right?

Hi Tom

Then, if logfile is physically coutinuous?

HI. Tom

I ask these questions only because I want to clarify that the relationship between oracle sequence scatter read and physical sequence random reads. If the oracle files are not continuous, does it mean that oracle's sequence reads are actullay physically random reads?

Thanks

Thanks Tom

Hi Tom,
I need to plan a tablespace for 3 segments in newly datawarehouse database (9iR2)

The first segment size : 6.5GB,
The second segment size : 7GB
The threed segment size : 8.5GB

my question are:
1. Is ASSM is proper for datawarehouse use ?
2. Is uniform size of 128MB is to "high" for those
segments as demonstrate here:

create tablespace TEST datafile
'/TEST_01.dbf' SIZE 4001M AUTOEXTEND OFF,
'/TEST_02.dbf' SIZE 4001M AUTOEXTEND OFF,
'/TEST_03.dbf' SIZE 4001M AUTOEXTEND OFF,
'/TEST_04.dbf' SIZE 4001M AUTOEXTEND OFF,
'/TEST_05.dbf' SIZE 4001M AUTOEXTEND OFF,
'/TEST_06.dbf' SIZE 4001M AUTOEXTEND OFF,
extent management local
uniform size 128M
segment space management auto;

Also i would like to ask:
3. Is pga_aggregate_target is usfull for
datawarehouse database, or should i stick with
sort_area_* and hash_area_* ?
4. If yes , Do you recommand to use manual
pga_aggregate_target at loading time , and automatic
pga_aggregate_target after the loading is completed ?
Thanks Again.

Tom,

1. Is that mean that i need to determain the freelist,
pctused and ech in datawarehouse database?
2 . "You might have an extra 127.9mb of free space for
each segment " - Is that because the 64kb header
overhead ?
In your last book, you wrote that using system managed
tablespace should set when you dont know what is
going to be the segment size.
In my case i do know. so if i set the size to
4000m+64k is that change anything ?
Thanks Again.

Hi Tom ,
We are having LMT tablespace as extent managed as EXTENT MANAGEMENT LOCAL
and Segment management as SEGMENT SPACE MANAGEMENT MANUAL
then , Does Following parameters for any table underlying it will play role or not ?

For Ex:

CREATE TABLESPACE XXX
DATAFILE
'/apps/oradata/sss/dat/xxx.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;




1) PCTFREE
2) PCTUSED
3) FREELIST

Actually I am confused between Segment SPace Management as Auto and Manual then PCTUSED AND PCTFREE will have any importance or not ?

Correct me on below :
In MSSM : PCTFREE and PCTUSED Will play role
IN ASSM : FREELIST will play a role others will be managed by bitmap index .




Regards
MJani

Hi TOM,
Thanks for your neat response,it was needed.Through the documentation and books I come to below points. Actually we are havig some performance problems in our Production environment ,where we recently upgrede our whole Hardware and now we achive very high performance gain in night batch process but some problems in OLTP.(Deadlock,contention,server go slow..)
1) Only in One schema we get these problem thus I am thinkig to make all tables in this schema who participates in OLTP as ASSM. There is also one point that this tables were created well before and at that time no one consider PCTUSED,PCTFREE,FREELIST numbers into account ,they are just as default values .
2) Rightnow I am working on benchmrking these tables performance with either Segement Mgmt. Means with using ASSM I can achieve the performance for heavy OLTP or not .
Does it is OK or I need to take care something else also ?

Thanks Again...
MJani

Hi TOm ,
Rightnow I am having this Application as tablspace MSSM and OLTP behaviur ,and expericing poor performance .

For that :
1) Started to tune the sql where-ever seem possible.
2) And For such application , I am thinking Oracle 9i's ASSM shold be more suitable. Atleast it can give some performance benifit. For the deadlock is abdrupt thus we are trying to resolve it from Application . But can tell me that is it possible that due to wrong space-management (In terms of size,type or blocks)deadlock occur.
So I was asking you that after replacing my OLTP application to ASSM which type of advantage or disadvantage I can face .

Tom,
I have a dictionary managed tablespace that has 3 related datafiles each of approx. size 2G.

I am getting ready to convert to LMT. My question is should I create it with 3 datafiles as above or just one or perhaps BIGFILE?

Thanks

I am using Oracle 9i (9.2.0.1.0).
In my database i have created local Manage tablespace.
My question is this. I have check my database user which is production and working 24hrs.
I have run the following queries for checking the size of a user and their objects.

querry 1:
select owner,sum(bytes)/(1024*1024) size_mb
from dba_Segments
where owner ='WORKING'
group by owner;

OWNER SIZE_MB
-------------------- ----------
WORKING 1116

Query 2:
select owner,segment_name,segment_type,
sum(bytes)/(1024*1024) size_mb
from dba_segments
where owner ='WORKING'
group by owner,segment_name,segment_type
order by 3;

Output: size of 2nd quesry is queal to the first query 1116

When i saw a segment which type is table and its size around 55M and which i select this table, there are 5 columns and just 3 rows in it.
How is it possible that its size 55M.

There are many tables and indexes have same situation.
Can you please put light on it.

I have perform the same thing as you replied me.

Steps:
------
1.create new tablespace with local Manage (uniform size 1M)
2. create new user for testing
3. export specific table .....size was 51M
4. connect with the user and run this
5. alter table XYZ move;
6. connect with system and check the size
its size still 51MB.
There is no primary key or any index in it.

What should I do?

Hello Tom. Whether As you consider it is necessary to translate tabulared spaces operated by the dictionary in locally operated?

Tom,
Here is the Strcture & select output of the table

desc working
Name Null? Type
--------------------- -------- -----
ZONE_CD NUMBER(1)
ZONE_DESC VARCHAR2(20)
SECT_ST NUMBER(3)
SECT_ED NUMBER(3)
REC_DT DATE

sql> select * from working;

ZONE_CD ZONE_DESC SECT_ST SECT_ED REC_DT
------- ---------- ---------- ---------- ---------
1 ZONE 1 15 18-JUL-99
2 INNER ZONE 16 35 18-JUL-99
3 OUTER ZONE 36 62 18-JUL-99

DBA_segments
============
BYTES_MB INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
------ -------------- ----------- ----------- ----------
51 52469760 1048576 1 2147483645

I have many tables in which same problem exist.
Ur suggestion Please.

Perhaps that INITIAL_EXTENT size of 50M has something to do with the table size of 50M? Is that the tablespace default setting?

ah, yes, indeed.

that is why I want a test case.


ops$tkyte%ORA10GR2> create tablespace testing uniform size 1m default storage (initial 50m);
create tablespace testing uniform size 1m default storage (initial 50m)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation
policy


I've a feeling he is confused - he said before "it is a locally managed tablespace"

 

Not to beat this to death, but: he said that he created a new LMT, then exported/imported the table into it. If the table came from a DMT, wouldn't the extent parameters come with it? So - if he didn't pre-create the table and let the import create the table, the size info would be from the OLD tablespace.

I have resolved my problem with this

Alter table username. table move storage (initial 1k next 1k pctincrease 0 minextents 1 )

Now my object size is 1M.

I am trying to apply same thing on other objects.


Thanks.

I have perform same command with all tables as wel as indexes.

Now my user size is 510M before that It was 1116M.

for table
=========
alter table owner.table move storage (initial 1k next 1k pctincrease 0 minextents 1 );

for index
=========
alter index owner.index rebuild storage (initial 1k next 1k pctincrease 0 minextents 1 );

All indexes are valid.


Thanks alot for helping me.

Tom,

Like table 'all_tables', is there a table which has information about tablespaces. I want to find out which tables is eating how much space in a particular tablespace -- how can I do it using a SQL?

Thanks a lot

Thanks Tom!

I have been doing some testing in 10gR2 and have noticed some weird behavior. we are using a 32k block size. I created the table with extent management local and segment management auto.

When i created a table with initial of 128k next 1M, dba extents said the first extent was 6 blocks or 192k. when i created the table with 256k as the initial, i got 33 extents.

with ASSM turned off, I still got multiple extents, all sized at 2 blocks - 64k each.

what happened to oracle using 1 extent with the size specified? it used to be when you specified extent sizes like that, you got extent sizes of those sizes.

we create a table with 300M as the initial and it generated extents of 8M until it got to 300M. Same thing when we tried 800M -- that's a lot of extents.

so what's up with these extents? do we put the parameter in the create statement and just accept that oracle will use multiple extents anyway or is there something that i have overlooked? i have a table to load that is 1.5 terabytes -- will i be looking at 1000s of extents? in the create, we set the initial to 1G. is 8M a max? are we looking to see 125 extents at a minimum for each Gb?

I am really confused.

Hi Tom,

The information on ASSM allocating space was highly informative. After going through that, I got a few doubts:
1.Where will be the value obtained for the inital size and number of extents (as per the internal algorithm) stored?
2.How will be the space deallocated. To be more specific, while truncating, have read before, Oracle will deallocate space except the initial,min_sxtent value. I was under the assumption that the min_extent for locally managed tablespace will be always 1. If the tablespace is in ASSM(of course locally managed also), what will be the number of extents after a truncate on a table on this tablespace?

Thanks in advance,
Regards,

Hi Tom,

Thanks for your reply. Can you explain the point "the number of extents after a truncate will depend on the options used with truncate " a bit more?
Thanks and Regards,

ops$tkyte%ORA10GR2> create table t1
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> create table t2
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select segment_name, count(*), sum(bytes)
  2    from user_extents
  3   where segment_name in ('T1','T2')
  4   group by segment_name;

SEGMENT_NAME                     COUNT(*) SUM(BYTES)
------------------------------ ---------- ----------
T2                                     21    6291456
T1                                     21    6291456

ops$tkyte%ORA10GR2> truncate table t1 drop storage;

Table truncated.

ops$tkyte%ORA10GR2> truncate table t2 reuse storage;

Table truncated.

ops$tkyte%ORA10GR2> select segment_name, count(*), sum(bytes)
  2    from user_extents
  3   where segment_name in ('T1','T2')
  4   group by segment_name;

SEGMENT_NAME                     COUNT(*) SUM(BYTES)
------------------------------ ---------- ----------
T2                                     21    6291456
T1                                      1      65536

Thanks Tom.

Hi Tom,

from the posts above i summarize the following:
- pctincrease = 0 is good thing for LMT
- skip the storage clause and let auto-management handle it

But PCTINCREASE is set to 50 per default, so now i'm a bit confused about setting pctincrease or not setting it?

Thanks,
Jochen

Hi Tom,

Additional 64K should be per tablespace or per datafile?

Regards,

ops$tkyte%ORA10GR2> create tablespace testing
  2  datafile '/tmp/testing1.dbf' size 1m, '/tmp/testing2.dbf' size 1m
  3  extent management local
  4  uniform size 512k
  5  /

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select file_id, blocks from dba_free_space where tablespace_name = 'TESTING'
  2  /

   FILE_ID     BLOCKS
---------- ----------
        10         64
         9         64

ops$tkyte%ORA10GR2> alter database datafile '/tmp/testing1.dbf' resize 1114112
  2  /

Database altered.

ops$tkyte%ORA10GR2> select file_id, blocks from dba_free_space where tablespace_name = 'TESTING'
  2  /

   FILE_ID     BLOCKS
---------- ----------
        10         64
         9        128

ops$tkyte%ORA10GR2> alter database datafile '/tmp/testing2.dbf' resize 1114112
  2  /

Database altered.

ops$tkyte%ORA10GR2> select file_id, blocks from dba_free_space where tablespace_name = 'TESTING'
  2  /

   FILE_ID     BLOCKS
---------- ----------
        10        128
         9        128

and we said...
1) you should set the extent size on your temporary tablespaces to be in line with your
SORT_AREA_SIZE setting. We "page" to temp in that size.

with reference to the above follow up, isn't sort_area_size
parameter neglected when we use pga_aggregate_target and
workarea_size_policy set to auto.

Dear Tom ,

why oracle is going a head with locally managed tablespace,
with more number of datafiles would it not better to use extent managed dcit rather than local.

Hi Tom
Using LMT, each datafile in tablespace manages it's own free and used space within a bitmap structure stored in each data files. With large number of datafiles in a tablespace dont u think this could be better utlized by dictionary managed.

Hi Tom,

The space management you describe with LMT, will it manage tables/indexes also in the same manner. What I mean is if we define table with wrong storage parameter, stiil since we have LMT in place, will Oracle-10g manage space effectively.

Do we really need to bother about correctly calculating storage clauses in Oracle10g, if LMT is in place?

Shrikant

Why to set PCTFREE parameter, is it not managed by LMT ?

Thanks alot for your answer.

In our current project, we are migrating from Oracle 9i to Oracle 10g and is auto space managed. I have all the idea of how much the table can grow and about update frequency of each tables.

Should I go for import export in new environment, or I should first create tables after calculating exact PCTFREE component and then just import data.

Which method would be good in Oracle10g ?

Thanks.

We cant go for upgrade, as we are going for seperate unix server with following configuration

System Model: IBM,9117-570
Processor Type: PowerPC_POWER5
Number Of Processors: 4
Processor Clock Speed: 2198 MHz
CPU Type: 64-bit
Kernel Type: 64-bit

And hence we will be having altogether new Oracle 10g installation.

So are you saying creating tables first after calculating correct PCTFREE and then importing data would be good?

Hi,
I was going thru the Oracle's course material on 10g Admin 1. I have a query regarding the space management.

What i understood so far is that in LMT we have bitmaps and in DMT we have free lists plus FET$/UET$ being updated constantly.

Now , if I have LMT and I choose the Segment Space Management as "Manual" then isn't equivalent of DMT ??

or I'm so very confused ... with LMT, DMT ,extent allocation ,segment space management and all....

thx

Tom:

we have am 11gR2 database with 5 tablespaces (SYSTEM, SYSAUX, TEMP, UNDOTBS1, USERS) and all set to locally managed tablespaces.

I noticed SYSTEM, TEMP and UNDOTBS1 ASSM are set to MANUAL. Is it recommended to set all of these to AUTO? I have been reading different things about this an that this may improve and may not improve performance depending on the application.

The application is a SAP reporting analysis COTS package that purges data nighly and loads new data. It runs about 10 hours every night..

The database is only used for that so you can conside that as data warehouse database.

I also noticed that SYSTEM is sied at 3.5 GB abd 98% full and does not see to extends until it hits 100%. is this normal? is this filesize small.

BTW, my understanding is that a segment is not a block. It is a table or index which is a group of extents which is group of data blocks on the O/S.

Tom:

OK, I was confised becaue oracle documents say new 11g installs should default to "automatic segment management" but it seems this only applies to data tablespaces (not SYSTEM, UNDO, TEMP). right?

I am not sure why this database has SYSTEM tablespace changing unless maybe the main user application for this default TEMP space is set to SYSTEM instead of TEMP.

My understanding is that SYSTEM tablespace stores oracle tables and data dictionary and should be fairly stable.

Tom:

I was reading Effective Oracle by Design, Chapter #4 Some LMT Caveats, The Magic Number for Uniformly Sized Extents Is 64KB.

Can you explain me how you arrive this magic number 102464k, in the below quote to resize the Datafile?
is that 102400Kb + 64Kb (for bitmap used to manage the file) ?

<quote>
So, it would appear that 5MB of overhead was taken, but this is not really the case. Let’s increase that file by a measly 64KB.

ops$tkyte@ORA920> column file_name new_val f
ops$tkyte@ORA920> select file_name from dba_data_files
2 where tablespace_name = 'FIVE_MEG';
FILE_NAME
------------------------------
/usr/oracle/ora920/OraHome1/or
adata/ora920/o1_mf_five_meg_zc
54bj5l_.dbf

ops$tkyte@ORA920> alter database
2 datafile '&f' resize 102464k;
old 2: datafile '&f' resize 102464k
new 2: datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_five_meg_zc54bj5l_.dbf'
resize 102464k
Database altered.

ops$tkyte@ORA920> select sum(bytes/1024/1024) free_space
2 from dba_free_space
3 where tablespace_name = 'FIVE_MEG';
FREE_SPACE
----------
100

</quote>

I am totally confused about below 3 staffs, could you please help to explain more?
ALLOCATION TYPE:SYSTEM/AUTO
SEGMENT SPACE MANAGEMENT:MANUAL/AUTO
EXTENT MANAGEMENT LOCAL/DIC

from document, it said 'Automatic segment space management (ASSM, or bitmap freelists) is a simpler and more efficient way of managing space within a segment'
given it is managing space of segment and also segment is composed of extents, then
1. what the difference between 'extent management' and 'segment space management'?
2. I know all are about 'bitmap', why can not 'extent management' cover 'segment space management'?
3. is 'allocation type' about the 'size' of the extent? i noticed UNDO,TEMP and SYSTEM is using 'MANUAL', so 'manual' means fixed size?
why oracle prefers fixed size for them? easy to maintain or have to be fixed size?
4. what's difference of 'fixed size' and 'non-fixed size' in terms of performance?

thank you very much.
pretty clear now