Re: Freelists and Freelist Groups

Emmanuel wrote:
>
> First of all, thanks for the amazingly quick answer !
>
> Mark D Powell a écrit :
> > Freelists groups is an OPS/RAC parameters where each database instance
> > pulls its freelist from different groups to reduce the likelihood that
> > updates done on one instance will go to the same block as updates done
> > from another instance.
>
> Ok, I got it. As my DBs are accessed only with a single instance,
> keeping 1 freelist group is just fine.
>
> >
> > What makes you think you need more freelists? We have run OPS from
> > version 7.0 and have found that most tables work fine with only 1
> > freelist on 1 freelist group even in RAC.
> >
> > You should post your full version, platform, the queries, and the
> > results that you are basing your conclusion on so that board readers
> > can determine if what you think is a problem is the problem.
> >
> > Have you ran a short duration statspack during peak time that you can
> > post numbers from?
> >
> > HTH -- Mark D Powell --
> >
>
> 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

freelists / groups et al will be related to improving your situation with the buffer busy waits (which came in third). Improving scattered / sequential waits comes down the good old fashioned tuning mantra: look for poor SQL, where "poor" = badly written, running at wrong time, or running against the wrong design

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------