Re: Q:Good preformance

The settings for PCTFREE and PCTUSED are used to try optimise the way in which Oracle accesses a tables data and are related to Oracle reading a block at a time. Both can take values from 0 - 99 % but the sum of the two must not exceed 100.

The PCTFREE parameter reserves free space within a block to enable rows to expand without chaining. Rows will expand if a varchar2 field that had a null value say, was update dwith a 30 character string for example. If there was insufficient space left in the block to hold the expanded field the data must migrate to a new block and chaining will occur with its resulting loss in database performance. Therefoere having a large PCTFREE will reduce chaining.
On the downside having a large PCTFREE will reduce the number of rows that are stored in a block and may increse the number of I/O needed for selects (and updates or deletes actually) As a rule of thumb the setting for PCTFREE should be low ( ~5) for a table that has few updates and many reads and highish (~25) for a table with many updates.

The PCTUSED parameter is the minimum amount of space Oracle will try to use within a block. Once PCTUSED of a block has been used Oracle will no longer attempt to insert rows into the block. Therefore a low PCTUSED may increase insert performance as the inserts are likely not to have to try and fill up existing blocks but can all go into a new block together. A high PCTUSED has the opposite effect, but increases select performance as more rows will be stored in a block potentially reducing the number of I/O required.

If you have not already read it, most of this is covered on 4-4 to 4-7 of the Server Application developers guide.

Hope this helps,

Ian Cary Received on Tue Sep 23 1997 - 00:00:00 CDT