| Estimate the UNIFORM SIZE tips (Locally Managed Tablespace - Oracle 8i). [message #60515] |
Wed, 11 February 2004 22:48  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I'm using Oracle 8i with Locally Managed Tablespaces (LMT) and I want to know more about some "tricks" and strategies about the value of UNIFORM clause choice (AUTOALLOCATE is not available before Oracle 9i..).
I understood that if one have 3 types of tables (segments): small, medium and large it is best to have 3 types tablespaces with different UNIFORM SIZE!
I know also that it's recommended to set the UNIFORM extent size to be the same as your SORT_AREA_SIZE or multiples of it!
Is there any other recommendations and tips to set the UNIFORM SIZE to fit best to a tablespace with small table size, another tablespace with medium tables size and another with large table size? Which will be the standard or classic values of UNIFORM SIZE X[[K|M]] in each of these 3 cases?
Is there any special recommendations and tips to set the value of the UNIFORM SIZE for a tablespace containing:
1. a table with a predominance of large amount of inserts?
2. a table with a predominance of large amount of updates?
3. a table with a predominance of heavy select activities on it?
Thank you very much in advance for your advises and tips!
Regards,
Patrick Tahiri.<BR style="mso-special-character: line-break"><BR style="mso-special-character: line-break">
|
|
|
|
| Re: Estimate the UNIFORM SIZE tips (Locally Managed Tablespace - Oracle 8i). [message #60527 is a reply to message #60515] |
Thu, 12 February 2004 06:28  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
Quote "I understood that if one have 3 types of tables (segments): small, medium and large it is best to have 3 types tablespaces with different UNIFORM SIZE!"
is not a rule. It is to say , if you have widely varying segment sizes(tables or indexes), then you could group them by their sizes/growth into say 3 tablespaces . One for say extent sizes 256k , 2nd one for say 4m , 3rd one for say 64m . Again the exact values differ depending on your segment sizes/growth.
Quote "I know also that it's recommended to set the UNIFORM extent size to be the same as your SORT_AREA_SIZE or multiples of it!"
only for TEMPORARY tablespaces, not for your data/index/rollback tablespaces.
For UNIFORM extent size, pick up a value that is a multiple of your dbfile_multiblock_read_count and one that will not cause your segments to have 1000s and 1000s of extents ( I know I have heard others say million extents are OK in LMT, but I dont think so).
Note this extent size is not dependent on your inserts/updates directly. In 8i,you'll be tuning your PCTUSED,FREELISTS,INITRANS,PCTFREE etc to cater to these transaction activities. In 9i, you can use ASSM to let Oracle manage the space within your segments/blocks( but you still need to take care of PCTFREE, if required).
Hope this helps
Thiru
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
