Re: Freelists and Freelist Groups

> 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 Received on Fri Nov 18 2005 - 14:58:11 CST