| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Freelists and Freelist Groups
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 Received on Fri Nov 18 2005 - 09:12:48 CST
 |
 |