Re: Freelists and Freelist Groups

Matthias Hoys wrote:
>>>>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 ? 

More wasted disk space. More wasted i/o.

And I don't see this as a cause of disk contention in systems I've observed.

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