SEO

Hello everybody,

I'm experiencing "db file sequential/scattered waits" problems on a
8.1.7 database on a Windows server. I'm pretty new to Oracle
Administration (and to DB Administration too), however it seems altering
the number of "freelists" on specific tables and indexes could help.
I've come to understand the use of the "freelists" parameter, but I
can't find any further explanation on Freelist groups than "number of
groups of freelists".

What's the "freelist groups" parameter used for ? What's the use of
grouping freelists anyway ?

Thanks all,

Emmanuel

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.

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 --

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

> 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

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@x.washington.edu
(replace x with u to respond)

>>>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@x.washington.edu
> (replace x with u to respond)

Higher PCTFREE = less rows in 1 block = less data block contention ?

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@yahoo.com


"Semper in excremento, sole profundum qui variat."

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

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@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@x.washington.edu
(replace x with u to respond)

"Emmanuel" <mars@tacks.com> wrote in message
news:dlkr0h$mt4$1@s1.news.oleane.net...
>
> 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 :
>
>
> Could someone give me a hand on this ?
>
> Thanks
>
> Emmanuel



The time lost on buffer busy waits is a small fraction
of the time lost on physical I/O; moreover, one of
the causes of buffer busy waits is excessive I/O.
Consequently, you should be addressing the I/O
problem first, as a reduction in I/O may result in
the elimination of the buffer busy waits.

Check v$sql for any queries where disk_reads is high,
especially if it is also a significant fraction of the buffer_gets,
and see if you can find ways to make them more efficient.


--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

Jonathan Lewis a écrit :
> The time lost on buffer busy waits is a small fraction
> of the time lost on physical I/O; moreover, one of
> the causes of buffer busy waits is excessive I/O.
> Consequently, you should be addressing the I/O
> problem first, as a reduction in I/O may result in
> the elimination of the buffer busy waits.
>
> Check v$sql for any queries where disk_reads is high,
> especially if it is also a significant fraction of the buffer_gets,
> and see if you can find ways to make them more efficient.
>
>

Thanks for all the answers. I'll look after v$sql to find out which
queries and which tables/indexes cause the most trouble and try to make
it work efficiently.

Emmanuel