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-2008PURPOSE-------Overview of PCTUSED usage in locally managed tablespace with Automatic segmentspace management(ASSM) set.SCOPE & APPLICATION-------------------For users specifying PCTUSED clauses when creating tables in locally managedtablespaces with Automatic segment space management(ASSM) set.Locally managed tablespaces track all extent information in the tablespaceitself using bitmaps. In these tablespaces, space allocations anddeallocations predominantly modify the locally managed resources (bitmapsstored in header files). All the necessary information is stored in fileheaders and bitmap blocks thus reducing any reliance on the data dictionary.With the SEGMENT SPACE MANAGEMENT clause set to AUTO in the CREATE TABLESPACEstatement, you implement the ASSM feature for the tablespace. This featureallows Oracle to use bitmaps to manage the free space within segments. That isthe bitmap describes the status of each data block within a segment with respectto the amount of space in the block available for inserting rows. The currentstatus of the space available in a data block is reflected in the bitmapallowing for Oracle to manage free space automatically with ASSM.ASSM tablespaces automate freelist management and remove the ability to specifyPCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tablesand indexes created in these tablespacesEg : create tablespace testdatafile ''h:\test.dbf'size 5mEXTENT MANAGEMENT LOCAL -- For LMTSEGMENT SPACE MANAGEMENT AUTO -- For ASSMcreate table test_table(c1 number)tablespace testpctused 20storage ( freelists 23 next 5m ) ;Table created.The values for PCTUSED and other parameters for objects created as above willbe ignored and Oracle will automatically manage the space for the tables andindexes inside the tablespace using bitmaps. PCTFREE can still be specified andit used with ASSM.Please note that even though Oracle does not give an error message when theabove parameters are specified in the table definition, however these parametersare 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-ChainingA 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 databaseA high PCTUSED results in :i) keeping blocks more fullii) increasing processing costiii) improving space efficiencyPCTFREE + 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 parametersfor 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 storageparameters in the DDL statements like CREATE TABLE, CREATE INDEX, CREATECLUSTER commands. These parameters are space related and have control over thedata blocks.Theoretically, PCTUSED can be considered as low water mark and PCTFREE as thehigh water mark. If the space in a data block is such that there is less spaceleft than PCTFREE, no new rows can be added in that block until the amount ofspace in the table is less than PCTUSED. The total of PCTUSED and PCTFREEcannot 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 = 30PCTUSED = 50Then, 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 tothis data block. The space is reserved for growth of existing rows.PCTFREE· A high value for PCTFREE may improve performance because blocks have to bereorganized less frequently and chaining is also reduced. There is more spacefor growth of existing rows.· A low value for PCTFREE may reduce performance since reorganization becomesmore often and chaining would be increased. For further information on how toresolve and detect chaining/migration please refer to Note 122020.1PCTUSED· A high value for PCTUSED may decrease performance because more migrated andchained rows are present. But this reduces space wastage by filling the datablock more completely.· A low value for PCTUSED may increase performance because of less migratedand chained rows. But the space usage is not efficient due to unused spacein data blocks.Tips for PCTUSED and PCTFREE· If the application frequently performs UPDATES that alter sizes of rowsgreatly, then PCTFREE can be set high and PCTUSED can be set low. This wouldallow 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 setlow with average value for PCTUSED to avoid chaining of rows.· If the main concern is performance and more space is available, then PCTFREEcan be set very high and PCTUSED very low.· If the main concern in space and not performance, then PCTFREE can set verylow and PCTUSED very high.RELATED DOCUMENTS-----------------Note 1029850.6 Freelists and Freelist GroupsNote 122020.1 Row Chaining and M.
