Re: Freelists and Freelist Groups

>>>CLASS COUNT TIMEx
>>>------------------ ---------- ----------
>>>data block 680909 384949
>>>sort block 0 0
>>>save undo block 0 0
>>>segment header 114 98
>>>save undo header 0 0
>>>free list 0 0
>>>extent map 0 0
>>>bitmap block 6 0
>>>bitmap index block 4 0
>>>unused 0 0
>>>system undo header 0 0
>>>system undo block 0 0
>>>undo header 82 41
>>>undo block 3923 672
>>>
>>>And here I am in fact. I've tried to retrieve the guilty tables/indexes
>>>using v$session_wait, but it wasn't relevant. Looking at the storage
>>>options of the tables made me think it could come from there. Basically
>>>all the tables are created in the same way. Here's a sample :
>>>
>>>TABLE_NAME PCT_FREEP PCT_USEDI INI_TRANS
>>>MAX_TRANS
>>>------------------------------ ---------- ---------- ---------- ----------
>>>NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASEF FREELISTS
>>>FREELIST_GROUPS
>>>----------- ----------- ----------- ------------- ---------- ---------------
>>>T_COLIS 10 40 2
>>>255
>>> 10485760 1 2147483645 0 1 1
>>>
>>>I noticed also, and I think it's a bad thing, that almost all the
>>>tablespaces (except one non-SYSTEM) are Dictionary Managed.
>>>I'm not experienced enough to notice quickly the problems, so I'm basing
>>>my diagnosis on what I can read from my different searches on Internet.
>>>I'm therefore suspecting PCT_FREE, PCT_USED and FREELISTS parameters.
>>>
>>>Could someone give me a hand on this ?
>>>
>>>Thanks
>>>
>>>Emmanuel
>>
>>
>> You have a very high amount of data block waits. This could point to
>> "hot" objects like tables or indexes where a high number of sessions try
>> to concurrently access the same data block. Do you have such tables (for
>> example a trace or audit table) ? I would suggest you rebuild those
>> tables/indexes with a higher setting for INITRANS and PCTFREE.
>>
>> Matthias

>
> Why PCTFREE?
> -- 
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

Higher PCTFREE = less rows in 1 block = less data block contention ? Received on Fri Nov 18 2005 - 19:29:44 CST