| Storage clause & Performance (Oracle 8i) [message #60148] |
Mon, 19 January 2004 05:35  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I would like to have some tips and documentation about the strategy to use for configuring my STORAGE clause at table creation (and Tablespace).
Which INITIAL, NEXT, PCTINCREASE should I configure in which case?
For example for a table with only heavy INSERT operations on it and no update/delete: which values for INITIAL, NEST and PCTINCREASE are recommended?
A table with a lot of SELECT (heavy or not) statment on it: which values are recommended?
A table with heavy UPDATE/DELETE/INSERT activities: which values are recommended?
storage
(
initial xxxxK
next xxxK
minextents 1
maxextents unlimited
pctincrease 0
);
Thank you very much for your help!
Best regards,
Patrick Tahiri.
|
|
|
|
| Re: Storage clause & Performance (Oracle 8i) [message #60149 is a reply to message #60148] |
Mon, 19 January 2004 06:02  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Its better not to specify the INITIAL,NEXT,PCTINCREASE storage parameters at the segment level(ie TABLE). Create multiple tablespaces to cater to SMALL,MEDIUM and LARGE segments and specify the appropriate storage parameters at the tablespace level , so as to have uniform extent sizes for all the tables/indexes in that tablespace . I always set PCTINCREASE=0 for any/all segments.
That will eliminate any tablespace level fragmentation.
Secondly,you should be tuning PCTUSED,PCTFREE,FREELISTS,INITRANS for different transaction loads and not INITIAL,NEXT & PCTINCREASE (these control only the size & growth of the extents).
For eg, a table that is subjected to no updates can have a PCTFREE=1 ,as this parameter controls the percentage of block space reserved for future updates.
A table that is likely to be inserted by 5 different sessions at the same time,should have FREELISTS=5 so as not to have any freelist contention on the segment header.
and a table that has minimal deletes should have a higher PCTUSED(say 70-80 ) so as to tightly pack the rows in the block.
and so on. Oracle documentation provides a detailed insight on these parameters and how to tune them based on your transaction patterns.
In Oracle 9i,Automatic Segment Space Management feature is available that relieves the manual work involved in tuning PCTUSED,FREELISTS for the segments.
HTH
Thiru
|
|
|
|
|
|
| Re: Storage clause & Performance (Oracle 8i) [message #60163 is a reply to message #60148] |
Tue, 20 January 2004 04:14 |
And
Messages: 10
Registered: December 2003
|
Junior Member |
|
|
You can create your tablespace with extent managemente local autoallocate and Oracle will set storage values automatically for you when creating tables, so, you can forget to specify those values.
Local tablespace management space is defaulted since Oracle9i, so, i think you will no longer care about that matter in the future.
Hope this helps.
A.V. Database Administrator.
www.oratools.com.ar
|
|
|
|
| Re: Storage clause & Performance (Oracle 8i) [message #60165 is a reply to message #60163] |
Tue, 20 January 2004 05:53 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi
Locally managed tablespaces are definitely the way to go,but 'autoallocate' tends to create extents of various sizes which again could result in tablespace fragmentation , although not to the extent when you are sizing the segments individually. I always prefer UNIFORM sized extents and only go for SYSTEM managed extents when even an approximate size/growth of the segments is unknown or cannot be predicted.
-Thiru
|
|
|
|
| Re: Storage clause & Performance (Oracle 8i) [message #60191 is a reply to message #60165] |
Thu, 22 January 2004 08:58 |
And
Messages: 10
Registered: December 2003
|
Junior Member |
|
|
It is OK...i agree uniform will simplify your admin task.
Your thought is perfectly valid.
I choose AUTO cos i trust Oracle algorithm..i think they know what are doing...so i believe AUTO is more intelligent mechanism, besides, i have big tables and small tables alltogheter in my DB, i don't think UNIFORM best suite to my DB.
Regards!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
