one book i read said that
set INITRANS at least equivalent to FREELISTS.
but i think the oppose.
freelist is at segment level
initrans is at block level.
so freelist >= initrans make sense to me
can somebody elaborate this for me?
Hi, 25th June 2001 14:19 hrs chennai
Before concluding a solution for your problem i would like to brief you little on INITTRANS and FREELIST which would mean the solution for your problem.
Transaction entry settings for the data blocks allocated for a table, cluster, or index should be set individually for each object based on the following criteria:
the space you would like to reserve for transaction entries compared to the space you would reserve for database data
the number of concurrent transactions that are likely to touch the same data blocks at any given time
For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access to the same data block is low. Therefore, INITRANS can be set low, especially if space is at a premium in the database.
Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider preallocating transaction entry space by using a high INITRANS (to eliminate the overhead of having to allocate transaction entry space, as required when the object is in use) and allowing a higher MAXTRANS so that no user has to wait to access necessary data blocks.
INITRANS specify the initial number of transaction slots which are created in an index or data block.
The transaction slots are used to used to store information about transactions that are making changes to the block at a point in time.A transaction slot only uses one transaction slot even if it is changing more than one row or index entry.
Specifies the number of DML transaction entries for which space should be initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated data or index segment.
The default value is 1 for tables and 2 for clusters and index segment gurantess min level of concurrency. For eg if set to 3 INITRANS ensures that three transactions can concurrently make changes to the block.If needed additional transaction slots can be allocated from the free space in the block to permit more concurrent transactions to modify rows in the block.
When an insert operation on an object occurs, the free list is used to determine what blocks are available for inserts.Many server process can contend for the same free list if MANY INSERTS are occuring.This results in free list CONTENTION as server process incurs WAITS.
since the NUMBER of FREELIST for a object cannot be set dynamically it should be ensured that object is created INITIALLY with a SUFFICIENT number of FREE LISTS.
Single CPU system do not benefit greatly from multiple free lists because CPU manages one process at a time .Also adding a fee list even in a single CPU system may ensure that a processor is used more EFFECTIVELY but proper care should be taken when adding free lists.
The main aim for FREE LISTS is to ensure that there are a sufficient number of them to minimize contention between many server processes.
For FREELIST there is no initilaisation parameter to set for MINIMIZING free list contention.This is used with SEGMENT level.This value cannot be set dynamically and requires that an object be dropped and recreated in order to change it.
The default value for FREELISTS is 1.If you specify a FREELISTS value that is too large, Oracle returns an error indicating the maximum value.
Restriction: You can specify FREELISTS in the storage_clause of any statement except when creating or altering a tablespace or rollback segment
You can Query V$WAITSTAT and V$SYSTEM_EVENT to determine if there is a free list contention exist.If there is high numbers returned then the particular object should be identified.
As per the above v$ views
Selct class ,count,time from V$waitstat where class ='segment header' ;
2)select event ,total_waits from v$system_event where event='buffer bust waits';
To reduce buffer busy waits on:
data blocks:change pctfree and pctused.Check for 'right handed indexes'(indexes that get inserted into at the same point by many process).Increase INITRANS.Reduce the number of rows per block.
segment header:use freelist or increase the number of freelists.Use free list groups(even in a single instance environment, this can make a difference).
freelist blocks:Add more freelist.In case of parallel make sure that each instance has its own freelist group.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
Click Here to Expand Forum to Full Width