Home » RDBMS Server » Performance Tuning » Sizing tablespaces
Sizing tablespaces [message #303525] Fri, 29 February 2008 13:13 Go to next message
lotusdeva
Messages: 198
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 #303526 is a reply to message #303525] Fri, 29 February 2008 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use one tablespace locally managed autoallocate and don't care of anything.

Regards
Michel
Re: Sizing tablespaces [message #303528 is a reply to message #303525] Fri, 29 February 2008 13:24 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 #303539 is a reply to message #303528] Fri, 29 February 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use initial if you want or let Oracle allocates extents when it needs them. If you have an estimation of initial size (that is size after first load) then tell it to Oracle.

It is totally unrelated with segment space management, use manual or automatic as you want. Automiatic allows more possible actions on segments, it may better use allocated blocks.

Regards
Michel
Re: Sizing tablespaces [message #303544 is a reply to message #303539] Fri, 29 February 2008 13:52 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 #303552 is a reply to message #303544] Fri, 29 February 2008 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To reuse a file previously created (maybe coming from a previous tablespace you dropped or the like).
It must then have the exact size you give in the statement.
Without this clause, if the file exists then Oracle returns an error.

Regards
Michel
Re: Sizing tablespaces [message #303553 is a reply to message #303552] Fri, 29 February 2008 14:19 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
thank you!
Re: Sizing tablespaces [message #303568 is a reply to message #303525] Fri, 29 February 2008 15:57 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 #303598 is a reply to message #303568] Sat, 01 March 2008 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next is ignored in LMT (unless you specify a minextents > 1).

Regards
Michel
Re: Sizing tablespaces [message #303789 is a reply to message #303598] Sun, 02 March 2008 20:23 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
thank you!
Re: Sizing tablespaces [message #304026 is a reply to message #303525] Mon, 03 March 2008 14:57 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Smile ) - 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 #304239 is a reply to message #304237] Tue, 04 March 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So AUTOALLOCATE will allocate extents as needed?

Yes. This is the conclusion.
Use LMT autoallocate and you just have to specify INITIAL (if you want to).

Regards
Michel
Re: Sizing tablespaces [message #304249 is a reply to message #304239] Tue, 04 March 2008 12:00 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Smile
Re: Sizing tablespaces [message #304259 is a reply to message #304249] Tue, 04 March 2008 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 #304262 is a reply to message #304259] Tue, 04 March 2008 12:36 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Thank you very much for making this clear!

p.s. one last thing - INITTRANS and MAXTRANS are part of segment management (ASSM or MSSM) and hence ,should be specified for MSSM?

[Updated on: Tue, 04 March 2008 12:38]

Report message to a moderator

Re: Sizing tablespaces [message #304269 is a reply to message #304262] Tue, 04 March 2008 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MAXTRANS is ignored in 10.2 and always set to 255.
INITRANS can be given in any segment management type, it gives the number of entries in ITL (Interested Transaction List) that are preallocated in each block.

Regards
Michel
Re: Sizing tablespaces [message #304276 is a reply to message #304269] Tue, 04 March 2008 13:07 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Sizing tablespaces [message #304284 is a reply to message #304282] Tue, 04 March 2008 13:39 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
thank you!
Previous Topic: Slow query
Next Topic: Can you fix this query?
Goto Forum:
  


Current Time: Fri Dec 02 16:53:31 CST 2016

Total time taken to generate the page: 0.46204 seconds