Home » RDBMS Server » Performance Tuning » Sizing tablespaces
| Sizing tablespaces [message #303525] |
Fri, 29 February 2008 13:13  |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Hello, I have a question about sizing of tablespaces in Oracle 10g (we are on 10.2.0.3.0 version). I have a small database which among other things is going to have about 50 tables. Most of these tables would have about the same definitions in terms of storage. For example the same tables in a different database look as follows:
CREATE TABLE table_a
(
a_CODE VARCHAR2(4) NOT NULL,
b_CODE VARCHAR2(4) NOT NULL,
c_code NUMBER(2)
)
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 155648 NEXT 7782
MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 100)
TABLESPACE TABLESPACE_A;
So these tables are not very big. This database also has two very big tables, which are about 15G, with millions of records. These tables are expected to grow. Right now these are defined as follows (again, in another database, not this one which I am trying to build):
CREATE TABLE BIG_TABLE
(
id NUMBER(8) NOT NULL,
name VARCHAR2(20),
nw_val VARCHAR2(4000)
)
PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 272637952 NEXT 204800
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 100)
TABLESPACE TABLESPACE_A;
Taken the above into consideration would you say that the following tablespaces are sized properly? TABLESPACE_A would hold these tables. TABLESPACE_B would hold its indexes.
create tablespace TABLESPACE_A datafile '/prod/oradata/data01.dbf'
SIZE 15000M
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TABLESPACE_B datafile '/prod/oradata/index01.dbf'
SIZE 10000M
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SEGMENT SPACE MANAGEMENT AUTO;
I am not sure about several things here.
a)
Is the size big enough?
b)
Since I have two types of segments here - two big tables and rest of small tables, should I put them in separate tablespaces?
any suggestions would be appreciated. thank you!
|
|
|
|
|
|
| Re: Sizing tablespaces [message #303528 is a reply to message #303525] |
Fri, 29 February 2008 13:24  |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
But you still have to give it initial size though, right? So my tablespace is locally managed now and it uses autoallocate. Are you saying that no matter what the intial size is I should not care because Oracle will autoallocate it if needed? Thank you!
|
|
|
|
| Re: Sizing tablespaces [message #303531 is a reply to message #303525] |
Fri, 29 February 2008 13:27 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
Also, my understanding is that in Oracle 10g default for segment space management is MANUAL, so if you want it AUTO , you gots to include it..Am I wrong?
|
|
|
|
|
|
| Re: Sizing tablespaces [message #303544 is a reply to message #303539] |
Fri, 29 February 2008 13:52 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Thank you! I forgot to ask one more thing - I noticed that in some scripts they use REUSE after the size as follows:
create tablespace tablespace_a
datafile '/prod/oradata/data01.dbf'
SIZE 15000M REUSE
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SEGMENT SPACE MANAGEMENT AUTO;
What is this clause for?
|
|
|
|
|
|
|
|
| Re: Sizing tablespaces [message #303568 is a reply to message #303525] |
Fri, 29 February 2008 15:57 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
I was wondering if anyone knows, is the setting NEXT 100M too much for the above tablespace? Is it possible it can grow too big? What does Oracle recommend? Thank you in advance
|
|
|
|
|
|
|
|
| Re: Sizing tablespaces [message #304026 is a reply to message #303525] |
Mon, 03 March 2008 14:57 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
I was wondering about Automatic segment space management (we are on Oracle 10.2.0.3.0). By creating a locally managed tablespace with segment space management on, I have enabled it as follows:
create tablespace tablespace_1 datafile '/
prod/oradata/data01.dbf'
SIZE 15360M
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
My understanding is that by setting space management to AUTO, Oracle will ignore the values of PCTUSED, FREELISTS and FREELIST GROUPS. But what about PCFREE and the rest of the setings? So, if, for example, I wanted to define my table prior to ASSM, I would do this as follows:
CREATE TABLE my_table
(my_code VARCHAR2(4) NOT NULL,
cc_de VARCHAR2(4) NOT NULL,
days_delay NUMBER(2))
PCTFREE 20
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE development
STORAGE (
INITIAL 131072
NEXT 65536
PCTINCREASE 100
MINEXTENTS 1
MAXEXTENTS 99
)
Oracle will ignore PCTUSED, FREELISTS, FREELIST GROUPS and NEXT and it does need the INITIAL parameter. What about PCFREE, INITRANS, MAXTRANS, INITIAL, PCTINCREASE, MINEXTENTS and MAXEXTENTS? Also, I read somewhere (and I cant find it in Oracle documentation) that if you have high-volume concurrent inserts, they will take longer then if you had manually managed your extents. Did anyone have any issues with that? Thank you!
|
|
|
|
| Re: Sizing tablespaces [message #304108 is a reply to message #304026] |
Tue, 04 March 2008 01:19 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
PCTUSED, FREELISTS and FREELIST GROUPS are ignored in ASSM.
MAXEXTENTS is ignored in LMT.
NEXT and PCTINCREASE are ignored if MINEXTENTS is 1. It is only used to calculate the initial size.
MAXTRANS is ignored in any case in 10.2.
In LMT:
PCTFREE and INITIAL are the only parameters taken into account if MINEXTENTS is 1.
If MINEXTENTS is greater than 1 then NEXT and PCTINCREASE are also used to recalculate INITIAL. They actually don't say anything about the number and size of extents allocated.
PCTFREE is taken into account in all cases (LMT, DMT...).
Regards
Michel
[Updated on: Tue, 04 March 2008 01:20] Report message to a moderator |
|
|
|
| Re: Sizing tablespaces [message #304207 is a reply to message #303525] |
Tue, 04 March 2008 09:37 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Ok, this makes sense, thank you. I read Oracle 10.2 documentation and it states there the following:
In a tablespace that is specified as EXTENT MANAGEMENT LOCAL, the database uses the value of INITIAL with the extent size for the tablespace to determine the initial amount of space to reserve for the object. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then the database must allocate one 5M extent. If the extent size of the tablespace is smaller than the value of INITIAL, then the initial amount of space allocated will in fact be more than one extent.
If I am using AUTOALLOCATE for the tablespace creation, rather then UNIFORM, then does it mean that Oracle will allocate preciselly this INITIAL value that I specify during table creation?
And another thing (sorry for all these questions  ) - Oracle documentation states that MINEXTENTS is used to compute initial amount of space that is allocated (if LMT is used). It is equal to INITIAL * MINEXTENTS. So if INITIAL = 1, then MINEXTENTS also should = 1. So does it mean if my INITAIL is set to 155566, then MINEXTENTS should also be set to that number?
thank you!
[Updated on: Tue, 04 March 2008 09:47] Report message to a moderator |
|
|
|
| Re: Sizing tablespaces [message #304220 is a reply to message #304207] |
Tue, 04 March 2008 10:10 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1) if you use AUTOALLOCATE, this Oracle that chooses the initial size, at least the size you asked. There is no way to know which size it will take at this depends on version.
2) I don't understand "So if INITIAL = 1, then MINEXTENTS also should = 1". Did you take it from doc? If yes, post the link. If MINEXTENTS is greater than 1 then Oracle use INITIAL, NEXT, PCTINCREASE and MINEXTENTS to set an internal new value for INITIAL (unless the algorithm changed). Anyway, it does not matter in LMT, just use INITIAL to the size you want, I don't see any reason to use the other parameters.
Regards
Michel
|
|
|
|
| Re: Sizing tablespaces [message #304234 is a reply to message #304220] |
Tue, 04 March 2008 10:42 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Here is the link, there in the box they have a description of MINEXTENTS:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#CHDJCFFF
I think after reading Oracle 10.2 documentation I am more confused then ever. First of all, it states that:
The specification of storage parameters for objects in locally managed tablespaces is supported for backward compatibility. If you are using locally managed tablespaces, you can omit these storage parameter when creating objects in those tablespaces.
But if you do not choose to take ASSM route, then you HAVE to manually manage storate parameters. I read here that ASSM will cause performance issues - i.e. the inserts will be slower for hight volume concurrent inserts:
http://www.dba-oracle.com/art_builder_assm.htm
So, for example, I am moving some tables from Oracle 9i db to 10g, whereby in the latter I choose LMT for my tablespaces, but do not choose ASSM for my tables. I run some statistics to see how much storage my tables in 9i currently occupy and I see the following (I took CREATE TABLE statement and plugged CURRENT storage values in there):
CREATE TABLE table_1
(a_code VARCHAR2(4) NOT NULL,
c_code VARCHAR2(4) NOT NULL,
days_delay NUMBER(2))
PCTFREE 20
PCTUSED 40
INITRANS 1
MAXTRANS
TABLESPACE development
STORAGE (
INITIAL 155648
NEXT 7782
PCTINCREASE 100
MINEXTENTS 1
MAXEXTENTS 99
So, for storage parameteres:
1.
INITIAL - Oracle takes this value with extent size of the tablespace to determine how much space initally is needed. Oracle documentation states (first link):
In a tablespace that is specified as EXTENT MANAGEMENT LOCAL, the database uses the value of INITIAL with the extent size for the tablespace to determine the initial amount of space to reserve for the object. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then the database must allocate one 5M extent. If the extent size of the tablespace is smaller than the value of INITIAL, then the initial amount of space allocated will in fact be more than one extent.
Here is my tablespace:
create tablespace DEVELOPMENT datafile '/prod/oradata/data01.dbf'
SIZE 15360M
REUSE
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
I am not specifying extent size here. Hence, my confusion was - does it mean that Oracle will allocate space specified in INITIAL parameter and it will not take into consideration any of the tablespace parameters here?
2.
NEXT
If I specify it at 7782 bytes then next extent will be that. I dont know what Oracle recommends here.
3.
PCTINCREASE
This is the % by which the third and subsequent extents grow over the preceding extent. The default is 50%. I have it at 100%. However, the documentation also states:
If you want to keep all extents the same size, you can prevent the SMON background process from coalescing extents by setting the value of PCTINCREASE to 0. In general, Oracle recommends a setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing.
So I am not sure if I should set this to 0, to avoid fragmentation? But if its 0, then what happens if the table grows larger then what is specified in INITIAL and NEXT?
4.
MINEXTENTS. As Oracle documentation points out this is the total number of extents to allocate when the object is created. If it is > 1 then Oracle calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE storage parameters. So if I set it to 1 then Oracle ignores INITIAL, NEXT and PCTINCREASE? And if so, how does Oracle calculate these?
5.
MAXEXTENTS.
Documentation states that this parameter is ignored.
|
|
|
|
| Re: Sizing tablespaces [message #304237 is a reply to message #304220] |
Tue, 04 March 2008 10:51 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Ok, so are you saying that even if I dont use ASSM (i.e. by default Oracle 10.2. has extent management set to MANUAL, not ASSM). So even if I dont use ASSM and use AUTOALLOCATE in my tablespace, I can just set INITIAL to the size that I want and skip NEXT, PCTINCREASE, MINEXTENTS and MAXEXTENTS? So AUTOALLOCATE will allocate extents as needed?
CREATE TABLE table_1
(a_code VARCHAR2(4) NOT NULL,
c_code VARCHAR2(4) NOT NULL,
days_delay NUMBER(2))
PCTFREE 20
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE development
STORAGE ( INITIAL 155648)
create tablespace DEVELOPMENT datafile '/prod/oradata/data01.dbf'
SIZE 15360M
REUSE
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
|
|
|
|
|
|
| Re: Sizing tablespaces [message #304249 is a reply to message #304239] |
Tue, 04 March 2008 12:00 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
Ok. But by default Oracle 10.2. sets extent management to MANUAL (not ASSM). So then how can you manually manage it if you just specify INITIAL extents on the table? Isn't it the same as ASSM then?
|
|
|
|
| Re: Sizing tablespaces [message #304254 is a reply to message #303525] |
Tue, 04 March 2008 12:20 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
I think I know where I was confused now. When we create LMT, the storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not required.
An LMT can have either uniform or variable extent sizes. Variable extents are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed:
So, because I have LMT, I will specify INITIAL only. And because I choose not to use ASSM instead choosing to manage segmnets manually, I will have to specify PCTFREE and PCTUSED parameters. I hope thats it
|
|
|
|
| Re: Sizing tablespaces [message #304259 is a reply to message #304249] |
Tue, 04 March 2008 12:27 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | then how can you manually manage it if you just specify INITIAL extents on the table? Isn't it the same as ASSM then?
|
You confuse 2 things.
How extents are managed: locally or dictionary (LMT or DMT). this how space is managed inside tablespace.
How space is managed inside segments: manually or automatically (MSSM or ASSM)
Only the second one is related to ASSM and INITIAL is related to the first one and as no relation with ASSM.
Regards
Michel
Edit: OK, I didn't see your last post. Yes, this is that.
[Updated on: Tue, 04 March 2008 12:29] Report message to a moderator |
|
|
|
| Re: Sizing tablespaces [message #304261 is a reply to message #304259] |
Tue, 04 March 2008 12:35 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Ok, so ASSM controls PCFREE, PCUSED parameters and LMT controls storage parameters (which can be omitted in the CREATE TABLE statement, all except for INITIAL), correct? Hence, STORAGE clause defines how space is managed inside tablespace. I think that is where I was confused.
|
|
|
|
|
|
|
|
| Re: Sizing tablespaces [message #304276 is a reply to message #304269] |
Tue, 04 March 2008 13:07 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Ok, this is not getting any better for me:) Oracle 10.2 documentation states the following:
Manual Storage for LMT's (MSSM) - This specifies that you want to use free lists for managing free space within segments. This form of managing space within segments is called manual segment space management because of the need to specify and tune PCTUSED, FREELISTS and FREELIST GROUPS storage parameters for schema objects created in the tablespace
What about PCTFREE then? And how do you determine the size of FREELISTS and FREELIST GROUPS? Thank you!
|
|
|
|
| Re: Sizing tablespaces [message #304282 is a reply to message #304276] |
Tue, 04 March 2008 13:28 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
PCTFREE is the space reserved in each block for future updates (or ITL extension). It is available in any case.
PCTUSED, FREELIST and FREELIST GROUPS are used only in MSSM (and of course DMT) as stated in what you quoted.
Most of the time you let FREELIST parameters at their default values unless you get some wait events on free lists.
This may happen when many processes try to massively (but not directly) insert at the same time.
As a rule of thumb, FREELIST GROUPS should be set to the number of instances in RAC system.
Regards
Michel
[Updated on: Tue, 04 March 2008 13:47] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 01:21:59 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
