DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: initrans and freelist.

  1. #1
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    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?


  2. #2
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    solution

    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.

    Understanding INITTRANS
    =================
    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.

    Understanding FREELIST
    ================
    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.

    Conclusion
    =======

    As per the above v$ views

    1)
    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.

    cheers

    padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width