read the documentation on the INITRANS, PCTFREE and PCTUSED
parameters. Even if you go to LMT,
these parameters still play a role {LMT and UNIFORM/AUTOALLOCATE take away
the effort of sizing
INITIAL and NEXT and PCTINCREASE only}.
Here's some very old notes I maintained in the Oracle7 days :
Definition : The STORAGE clause of a CREATE statement defines the
various storage parameters.
INITIAL is the size of the initial extent to be allocated
NEXT is the size of the next extent to be allocated
MINEXTENTS is the minimum number of extents to be allocated when
creating the Object itself (the defaults are 1 for Tables and Indexes
and 2 for Rollback Segments)
MAXEXTENTS is the maximum number of extents the Object's Segment
can grow to (on UNIX the default values are 121 with a 2K
DB_BLOCK_SIZE and 249 with a 4K DB_BLOCK_SIZE).
PCTINCREASE is the percentage increment in the size of every
subsequent extent after the second one (the default is 50).
Other equally relevant parameters before the STORAGE clause in the
CREATE statement are :
PCTFREE which defines the percentage space in each Block which is to be
kept free for UPDATEs to field values which increase the size(s) of the
Rows within the Block (the default is 10). Thus, INSERTs will not be
allowed into the Block if the free-space falls below PCTFREE.
PCTUSED which is the lower threshold to be used below which only
will fresh INSERTs be allowed, once the PCTFREE parameter has been
exceeded, inspite of deletes making more space than PCTFREE available.
INITRANS defines the initial number of Transaction Entries to be
allowed within the Block (the default is 1 for Tables and 2 for
Clusters and Indexes). (Every Transaction working on row(s) in the Block
makes an entry specifying the type(s) of lock(s) it is acquiring).
MAXTRANS defines the maximum number of entries to be allowed. (the
default is a function of the Block size). Where this limit is reached, a
fresh transaction has to wait till an existing one is completed.
Usage and Performance : All these parameters are defined at CREATE
time. All (except INITIAL) can be modified later with an ALTER <Object>
statement. However, if altered, the new values are effective only for
Extents and Blocks created/allocated subsequent to the alteration.
INITIAL and NEXT sizes of All Objects in a Tablespace should be a
multiple of the DB_BLOCK_SIZE and the lowest value within the Tablespace
and they should all be a factor of the Tablespace size. This is to avoid
fragmentation of the Tablespace. Also, the DEFAULT STORAGE parameters for
the Tablespace should also follow this rule. Note that there is an
overhead in that every Object Segment takes an additional Block.
PCTINCREASE should generally be set to 0 (explicitly) to avoid
fragmentation of the Tablespace into odd-sized extents.
PCTFREE is to be low for Tables with very few UPDATEs. It results in :
i) less room for updates
ii) allowing INSERTs to fill the Block more completely
iii) saving in total space utilized
iv) more CPU overhead as the Blocks are more frequently reorganized
v) increase in processing cost if UPDATEs cause Row-Spanning or
Row-Chaining.
Indexes need a low PCTFREE (for example, 5 or lower).
PCTFREE is to be high for Tables with many UPDATEs that result
in the size of a row increasing. It results in :
i) more room for UPDATEs
ii) fewer rows per Block
iii) increase in total space needed
iv) improvement in performance with less Row-Spanning or Row-Chaining
A low PCTUSED results in :
i) keeping block less full
ii) reducing processing cost during UPDATE and DELETE statements as the
Block is less likely to be moved to the Free List
iii) increasing the unused space in the database
A high PCTUSED results in : i) keeping blocks more full ii) increasing processing cost iii) improving space efficiency
Some of our tables have very heavy inserts and a couple of tables among them have heavy updates as well. What are the good storage parameters for these tables. Right now I have initial = 1024, next=initial and pct_increase=0. We still uses DMT TBS and our version is 8.1.7.4. We are starting new season, so we have truncated all the old data and starting a fresh. I would like to have some good storage parameters settings for those tables.
Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 25-March-04} With computerisation, too many times, "GIGO" becomes "Garbage In, Gospel Out". {paraphrasing Steven Feuerstein}.
Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 25-March-04} With computerisation, too many times, "GIGO" becomes "Garbage In, Gospel Out". {paraphrasing Steven Feuerstein}.
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------