Re: Freelists and Freelist Groups

Matthias Hoys wrote:
>>Well, I haven't run the statspack yet, mostly because I'm really not aware
>>of the impact on the DB. So I've been through the basic v$ views.
>>
>>The DB is a 8.1.7.4.1 Oracle DB running on a Windows 2000 Server which I'm
>>discovering (I'm a brand new DBA).
>>Here's a piece of v$system_event (I've removed SQL*Net message from
>>client, rdbms ipc message, pmon timer and smon timer which came in first
>>positions) :
>>
>>
>>>SELECT
>>
>>EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS,
>> TIME_WAITED, AVERAGE_WAIT
>>FROM SYS.V_$SYSTEM_EVENT Vw
>>ORDER BY TIME_WAITED DESC;
>>
>>SELECT
>>EVENT
>>TOTAL_WAITS
>>---------------------------------------------------------------- -----------
>>TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAITx
>>-------------- ----------- -------------
>>db file sequential read
>>35620543
>> 0 17278123 .485060629
>>
>>db file scattered read
>>12259065
>> 0 2801865 .228554543
>>
>>
>>buffer busy waits
>>640026
>> 0 359692 .561995919
>>
>>
>>enqueue
>>938
>> 650 202598 215.989339
>>
>>
>>direct path read
>>281251
>> 0 91242 .324414847
>>
>>Now considering that the instance has been started 4 days ago, the number
>>of "db file sequential/scattered read" waits seems enormous (even if the
>>average wait's not so huge). The v$waitstat view confirms that there's
>>somewhat contentions :
>>
>>
>>>SELECT
>>
>> CLASS, COUNT, TIME
>>FROM SYS.V_$WAITSTAT Vw;
>>
>>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)