Skip to content

oracle table parameters pctused and pctfree – notes for the new oracle dba





you are a new oracle dba or developer  and you are creating or managing  tables  and would like  to know what the terms pctused and pctfree are and how they impact the space in a table block. This article explains how locally managed tablespaces ignore these parameters.

 

Subject:

PCTUSED, FREELISTS usage in LMT tablespaces with ASSM
 

Doc ID:

271166.1

Type:

BULLETIN
 

Modified Date:

05-JUN-2008

Status:

PUBLISHED

Checked for relevance on 05-Jun-2008


PURPOSE
-------

Overview of PCTUSED usage in locally managed tablespace with Automatic segment
space management(ASSM) set.



SCOPE & APPLICATION
-------------------

For users specifying PCTUSED clauses when creating tables in locally managed
tablespaces with Automatic segment space management(ASSM) set.


Locally managed tablespaces track all extent information in the tablespace
itself using bitmaps.  In these tablespaces, space allocations and
deallocations predominantly modify the locally managed resources (bitmaps
stored in header files). All the necessary information is stored in file
headers and bitmap blocks thus reducing any reliance on the data dictionary.

With the SEGMENT SPACE MANAGEMENT clause set to AUTO in the CREATE TABLESPACE
statement, you implement the ASSM feature for the tablespace. This feature
allows Oracle to use bitmaps to manage the free space within segments. That is
the bitmap describes the status of each data block within a segment with respect
to the amount of space in the block available for inserting rows.  The current
status of the space available in a data block is reflected in the bitmap
allowing for Oracle to manage free space automatically with ASSM.

ASSM tablespaces automate freelist management and remove the ability to specify
PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables
and indexes created in these tablespaces

Eg : create tablespace test
     datafile ''h:\test.dbf'
     size 5m
     EXTENT MANAGEMENT LOCAL       -- For LMT
     SEGMENT SPACE MANAGEMENT AUTO -- For ASSM


     create table test_table
     (c1 number)
     tablespace test
     pctused 20
     storage ( freelists 23 next 5m ) ;

Table created.


The values for PCTUSED and other parameters for objects created as above will
be ignored and Oracle will automatically manage the space for the tables and
indexes inside the tablespace using bitmaps. PCTFREE can still be specified and
it used with ASSM.

Please note that even though Oracle does not give an error message when the
above parameters are specified in the table definition, however these parameters
are ignored by Oracle with locally managed tablespaces using ASSM.

 

 

 

 

 

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

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.

Hemant

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.

 

 

 

 

Subject:

Storage Parameters PCTUSED and PCTFREE
 

Doc ID:

107090.1

Type:

BULLETIN
 

Modified Date:

04-JUN-2008

Status:

PUBLISHED

"Checked for relevance on 06-Jun-2008"


PURPOSE
-------

This document explains in detail the usage of PCTFREE and PCTUSED parameters
for database objects.


SCOPE & APPLICATION
-------------------

This article will be useful for Oracle DBSs, Developers, and Support Analysts.


STORAGE PARAMETERS PCTUSED and PCTFREE
--------------------------------------

The performance can be improved by properly using PCTUSED and PCTFREE storage
parameters in the DDL statements like CREATE TABLE, CREATE INDEX, CREATE
CLUSTER commands. These parameters are space related and have control over the
data blocks.

Theoretically, PCTUSED can be considered as low water mark and PCTFREE as the
high water mark. If the space in a data block is such that there is less space
left than PCTFREE, no new rows can be added in that block until the amount of
space in the table is less than PCTUSED. The total of PCTUSED and PCTFREE
cannot be over 100. (Default values for PCTFREE and PCTUSED are 10 and 40.)


Example:

Consider having following values for a DDL statement storage parameters:

PCTFREE = 30
PCTUSED = 50

Then, new rows can be added to the data block until the data block becomes 70%
FULL (100 PERCENT - PCTFREE). When this occurs, no new rows can be added to
this data block. The space is reserved for growth of existing rows.

PCTFREE

· A high value for PCTFREE may improve performance because blocks have to be
  reorganized less frequently and chaining is also reduced. There is more space
  for growth of existing rows.
· A low value for PCTFREE may reduce performance since reorganization becomes
  more often and chaining would be increased. For further information on how to
  resolve and detect chaining/migration please refer to Note 122020.1

PCTUSED

· A high value for PCTUSED may decrease performance because more migrated and
  chained rows are present. But this reduces space wastage by filling the data
  block more completely.
· A low value for PCTUSED may increase performance because of less migrated
  and chained rows. But the space usage is not efficient due to unused space
  in data blocks.

Tips for PCTUSED and PCTFREE

· If the application frequently performs UPDATES that alter sizes of rows
  greatly, then PCTFREE can be set high and PCTUSED can be set low. This would
  allow for large amount of space in data blocks for row size growth.
· If there is more INSERT activity with less UPDATES, the PCTFREE can be set
  low with average value for PCTUSED to avoid chaining of rows.
· If the main concern is performance and more space is available, then PCTFREE
  can be set very high and PCTUSED very low.
· If the main concern in space and not performance, then PCTFREE can set very
  low and PCTUSED very high.


RELATED DOCUMENTS
-----------------

Note 1029850.6  Freelists and Freelist Groups
Note 122020.1   Row Chaining and M

.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.