Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: optimal storage parameters for heavy inserts tables

Re: optimal storage parameters for heavy inserts tables

From: Hemant K Chitale <>
Date: Mon, 29 Mar 2004 22:30:18 +0800
Message-Id: <>

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

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

         PCTFREE + PCTUSED cannot exceed 100. If the sum is less than 100, the ideal compromise of space versus I/O performance is a sum that differs from 100 by the percentage of space in the available Block (i.e., Block size minus Overhead) that the Average Row occupies. If the sum equals 100, a maximum of PCTFREE is kept free and processing costs are high. The smaller the difference between 100 and the sum, the more efficient space usage is, at the cost of performance.

         A High UPDATEs Table should have a high PCTFREE (say 20) and a low (or default 40) PCTUSED. A High INSERT and DELETE with Low UPDATE Table should have a low PCTFREE (say 5) and a high PCTUSED (say 60). A Large Table should have a low PCTFREE (say 5) and a high PCTUSED (say 90) to improve space utilization.

         A Table which has few rows in each block can have a low MAXTRANS. Where the number of rows is high, the probability of multiple concurrent transactions increases and MAXTRANS may be increased. However, it would be advisable to increase INITTRANS also, at the outset, to pre-allocate enough space for concurrent transactions.


>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 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 {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 {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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Mar 29 2004 - 08:30:56 CST

Original text of this message