Home » RDBMS Server » Server Administration » What extent size would your recommend here (uniform tablespace)? (10.2.0.3 solaris10)
What extent size would your recommend here (uniform tablespace)? [message #336216] Fri, 25 July 2008 04:20 Go to next message
salkawari
Messages: 16
Registered: April 2008
Junior Member
Hello,

1. Can you tell me how to calculate / estimate the extent allocation for the following tables please?

Let us assume uniform tablespace allocation is required.

I also assume a number requires 5 bytes and a date 8 bytes.

Here is an example table, it will have 500,000 rows initially.
table1:
col1 NUMBER(16) NOT NULL PK
col2 VARCHAR2(12 BYTE) NOT NULL,
col3 VARCHAR2(200 BYTE) NOT NULL,
col4 VARCHAR2(10 BYTE) NOT NULL,
col5 VARCHAR2(200 BYTE) NOT NULL,
col6 NUMBER(10) NOT NULL,
col7 VARCHAR2(3 BYTE) NOT NULL,
col8 DATE NOT NULL,
col9 DATE,
col10 DATE,
col11 NUMBER(3) NOT NULL DEFAULT 0,
col12 VARCHAR2(1 BYTE) ,
col13 VARCHAR2(80 BYTE),
col14 VARCHAR2(80 BYTE),
col15 NUMBER(10),
col16 VARCHAR2(200 BYTE) ,
col17 VARCHAR2(200 BYTE) ,
col18 CHAR(1 BYTE) NOT NULL,
space estimate =(5+12+200+10+200+5+3+8+8+8+5+1+80+80+5+200+1)*500000/1024/1024 = 397 MB

There are other tables with have the following sizes (I used the same calculation as described above)
1960MB, 138MB, 25MB, 16MB, 5MB (and 5 other tables around 1MB is size). They all have primary keys over the 1st or 1st and 2nd columns.

All the tables are expected to grow annually by 20% for the next 3 years. All tables will be updated and inserted into fairly regularily.

2. Would you recommend an extent size of 100M for the big table? (it will grow from 1960MB to 3390MB).
3. What about the other tables? Would you recommend putting the 138MB table into that same tablespace? (it will grow from 138MB to 239MB).
4. Would you put the other remaining tables into a tablespace with the default extent size of 1M?
5. What about the indexes, how do I calculate /estimate thier size? The will just be normal primary key indexes without any functions etc.

Thanks for your help!
Re: What extent size would your recommend here (uniform tablespace)? [message #336220 is a reply to message #336216] Fri, 25 July 2008 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't use uniform, use autoallocate/system.

Regards
Michel
Re: What extent size would your recommend here (uniform tablespace)? [message #336221 is a reply to message #336220] Fri, 25 July 2008 04:40 Go to previous messageGo to next message
salkawari
Messages: 16
Registered: April 2008
Junior Member
Hello,

can you tell me how to estimate / calculate the extent allocation for a uniform tablespace please?

Thanks!
Re: What extent size would your recommend here (uniform tablespace)? [message #336233 is a reply to message #336221] Fri, 25 July 2008 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 25 July 2008 11:35
Don't use uniform, use autoallocate/system.

Regards
Michel


Re: What extent size would your recommend here (uniform tablespace)? [message #336243 is a reply to message #336233] Fri, 25 July 2008 06:29 Go to previous messageGo to next message
salkawari
Messages: 16
Registered: April 2008
Junior Member
Hello,

As someone who doesnt know as much about this topic I would be interested to hear why.

Could you explain why we shouldnt used uniform please?

Thanks!
Re: What extent size would your recommend here (uniform tablespace)? [message #336245 is a reply to message #336243] Fri, 25 July 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Because it is hard to know which size is correct and thus your question, and if it is not correct you can't change it, you have to create a new tablespace and move all the objects and so on and finally because autoallocate works well, is simple and will be improved by Oracle in each version.

Regards
Michel
Re: What extent size would your recommend here (uniform tablespace)? [message #336249 is a reply to message #336245] Fri, 25 July 2008 06:43 Go to previous messageGo to next message
salkawari
Messages: 16
Registered: April 2008
Junior Member
Hello,

okay accepted, but can you atleast give us a rule of thumb about calculating the uniform extent size? There must be some sort of recommendation from Oracle itself.

Do you know what I mean? If Oracle offers uniform then they must also offer some way of calculating the extent size.

Thanks!
Re: What extent size would your recommend here (uniform tablespace)? [message #336254 is a reply to message #336249] Fri, 25 July 2008 07:34 Go to previous messageGo to next message
satheeshb
Messages: 5
Registered: July 2006
Junior Member
Hi,
Number of extents or size of extent is of no relevance in LMT. I would prefer 1mb. No particular reason though.


Regards,
Satheesh Babu S

[Updated on: Fri, 25 July 2008 08:22] by Moderator

Report message to a moderator

Re: What extent size would your recommend here (uniform tablespace)? [message #336266 is a reply to message #336249] Fri, 25 July 2008 08:23 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
If Oracle offers uniform then they must also offer some way of calculating the extent size.

Oracle also provided LONG and say to not use it.
Some things seem good at a time and bad later.
Now it is time to forget uniform size.

Regards
Michel
Previous Topic: finding fragmented tables.
Next Topic: does log switch take place at 1/3 full redo-log buffer ?
Goto Forum:
  


Current Time: Sun Dec 08 05:59:36 CST 2024