SEO

running 9.2.0.5 on AIX5 with compatible parameter set to 8.1.7 and
using DMT.
Everything I've read so far was that dbms_stats was the best thing next
to sliced bread. I just read in a manual though that it doesn't update
freelist block information. So what do you do? revert back to analyze
until you can start using LMT? I know that I have a large amount of
blocks on the freelist and I suspect that we have a bad freelist
contention problem with all of our tables set to 1 freelist with 1
group on an system that is a combination oltp with large batch jobs. Is
there a better way to get freelist block information? Is it true that
dbms_stats doesn't gather freelist info?

For accurate freelist block counts,
you can use the dbms_space package
There is an example of this on my website

http://www.jlcomp.demon.co.uk/freespac.html

However I note that the script was written in 1998,
and the article was last updated in 2001, with references
to "new" types appearing in 8.0. The package may
have been enhanced since then. Note that in the
example there is an explicit limit on any freelist walk
that might take place, and the scripts only addresses
the first freelist group.

If you think you have trouble with freelists, then you
can check v$waitstat for waits for class "segment header"
(if you had multiple freelist groups, you would also
have to check for waits for class "free list")
and then cross check with v$segstat to see
which segments are responsible for most of
them (there is a statistic in v$segstat called
"buffer busy waits" - do not use v$segment_statistics
as it is an expensive way to get the name, owner
and tablespace of the object).


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

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

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


"Ben" <balvey@comcast.net> wrote in message
news:1143691305.349112.299420@i39g2000cwa.googlegr oups.com...
> running 9.2.0.5 on AIX5 with compatible parameter set to 8.1.7 and
> using DMT.
> Everything I've read so far was that dbms_stats was the best thing next
> to sliced bread. I just read in a manual though that it doesn't update
> freelist block information. So what do you do? revert back to analyze
> until you can start using LMT? I know that I have a large amount of
> blocks on the freelist and I suspect that we have a bad freelist
> contention problem with all of our tables set to 1 freelist with 1
> group on an system that is a combination oltp with large batch jobs. Is
> there a better way to get freelist block information? Is it true that
> dbms_stats doesn't gather freelist info?
>

Jonathan, Thank you for the information. I have used the dbms_space
package before. I just wondered if there was a way to gather info for
the entire schema all at once instead of table by table.

I do suspect that we have serious performance problems and I think some
of it is due to freelist contention. I checked out the stats tables
that you mention above. Here is what I found from the v$segstat. What
does the value column quantify? milliseconds or number of waits? This
is just the first three, there were over 600 total lines.
TbspName, ObjName, STATISTIC_NAME, VALUE
PRODDTAT, F55PTRQ, buffer busy waits, 10647244
PRODDTAT, F4801, buffer busy waits, 9479677
PRODDTAT, F31122, buffer busy waits, 7851222

also in looking at the v$waitstat this is what I found
CLASS, COUNT, TIME
data block, 42546555, 65329459
undo header, 13182, 35959
undo block, 12464, 11769
extent map, 1675, 2441
segment header, 1646, 3796

definitely some segment header contention there, but quite a lot more
data block. What does that tell me other than I've got some serious
work cut out for me?
Thanks again for the help..

"Ben" <balvey@comcast.net> wrote in message
news:1143734489.205450.222610@i40g2000cwc.googlegr oups.com...
> Jonathan, Thank you for the information. I have used the dbms_space
> package before. I just wondered if there was a way to gather info for
> the entire schema all at once instead of table by table.
>
> I do suspect that we have serious performance problems and I think some
> of it is due to freelist contention. I checked out the stats tables
> that you mention above. Here is what I found from the v$segstat. What
> does the value column quantify? milliseconds or number of waits? This
> is just the first three, there were over 600 total lines.
> TbspName, ObjName, STATISTIC_NAME, VALUE
> PRODDTAT, F55PTRQ, buffer busy waits, 10647244
> PRODDTAT, F4801, buffer busy waits, 9479677
> PRODDTAT, F31122, buffer busy waits, 7851222
>
> also in looking at the v$waitstat this is what I found
> CLASS, COUNT, TIME
> data block, 42546555, 65329459
> undo header, 13182, 35959
> undo block, 12464, 11769
> extent map, 1675, 2441
> segment header, 1646, 3796
>
> definitely some segment header contention there, but quite a lot more
> data block. What does that tell me other than I've got some serious
> work cut out for me?
> Thanks again for the help..
>


The figures may well be confirming your
suspicion that you have a contention problem
due to having one freelist per object for some
very busy objects.

The counts you see in v$segstat are number of
waits for each segment. Technically you ought
to spend a little time checking that the specific
reason for the wait (p3 in v$session_wait) is
an update collision - but your knowledge of the
application suggests that this is highly probable,
so you might want to address the issue it straight
away.

For each table that is reporting a large fraction
of the waits, anything over 500,000, say, change
the table to multiple freelists, which is easy in
your version of Oracle e.g.
alter table t1 storage (freelists 5)

Before making the change, look for the
most popular SQL statements, and take
a note of their execution paths as you
may find that as time passes, the execution
plans change as a side-effect of the
multiple freelists. This is low probability
in an OLTP system, but one you want to
watch out for.

You may find that increasing the number of
freelists allows data insertion to progress
more rapidly - which MAY lead to contention
appearing in other places - for example in
redo log allocation requests and log buffer
space requests. The problem is that if you
can insert data faster, you generate redo and
undo faster - so you may simply move the
critical point of contention.


Unless you are doing lots of updates that
affect indexes, you probably shouldn't be
seeing the same problem with indexes
apart from indexes which cover sequence-
based primary keys. If you see lots of waits
from those, you may have to rebuild them as
reverse key indexes - but I would postpone
that for the moment as it does have some
side-effects and can take some time to do.


You do have some other contention issues -
for example, the undo segment and undo
block waits might suggest that you need more
undo segments. The segment header and
extent map waits might suggest you have
a problem with tablescans, object growth
and related issues on tables (or indexes)
with lots of very small extents. But at the
moment I'd address the very large time
loss, and worry about the rest later.


Just before you do anything else, though,
take a few statspack snapshots to check
that this lost time (over a fifteen minute period,
say) really accounts for a significant fraction
of your time. Possibly your are burning vast
amounts of CPU doing lots of tablescans and
it is those that are the underlying cause of the
buffer busy waits.



I had jumped the gun slightly with my comment
about "segment header" waits being a symptom of
freelist contention.

The first contention point appears as "data block"
waits when you don't have enough freelists -
(although there are other reasons for "data
block" waits).

The second contention point appears if you
have multiple freelists in the segment header,
but they are still under too much pressure,
demanding a switch to multiple freelist groups.


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

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

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

Once again, Jonathan, thank you for the information. I appreciate the
fact that you pointed out that there could be repercussions from adding
the freelists.

After looking at the p3 values in v$session_wait, they are all 0. What
would they be if there was update collision? The majority of the events
are "SQL*Net message from client".

The most popular SQL statement from looking at several statspack
snapshots is a statement that executes every few seconds and monitors
the table that is listed at the top of my v$segstat snippet above,
(F55PTRQ). The script that executes that sql looks for a column to be
of a certain value and updates it to a different value when finding
one. We use it as a label printing queue table, so it probably has over
7000 or so records pass through it per day. By pass through I mean
inserted, updated, and deleted.

As far as the extent map and segment header waits. I could see that
this could be caused by the fact that we do have a LOT of small
extents. The previous dba didn't really do much to keep the extent
sizes uniform. We are using DMT and some the tables (and indexes for
that matter) within the tablespaces have large, 200M, initial and next
extents, and others have 56K extents. So our maps don't really have any
kind of uniformity to them.

With the snapshots, what can I look at to determine if these waits are
a significant fraction of our time? What values should I compare?

Finally, I know that this is such a broad span of things that could be
wrong or could not be wrong. When it comes to tuning, I don't really
have any experience. I am waiting on my copy of Cary Milsap's book on
Tuning, are there any other resources that I should be looking at to
teach myself how to get through these problems?

Thanks,

Ben

"Ben" <balvey@comcast.net> wrote in message
news:1143749901.727869.109820@v46g2000cwv.googlegr oups.com...
> Once again, Jonathan, thank you for the information. I appreciate the
> fact that you pointed out that there could be repercussions from adding
> the freelists.
>
> After looking at the p3 values in v$session_wait, they are all 0. What
> would they be if there was update collision? The majority of the events
> are "SQL*Net message from client".
>
> The most popular SQL statement from looking at several statspack
> snapshots is a statement that executes every few seconds and monitors
> the table that is listed at the top of my v$segstat snippet above,
> (F55PTRQ). The script that executes that sql looks for a column to be
> of a certain value and updates it to a different value when finding
> one. We use it as a label printing queue table, so it probably has over
> 7000 or so records pass through it per day. By pass through I mean
> inserted, updated, and deleted.
>
> As far as the extent map and segment header waits. I could see that
> this could be caused by the fact that we do have a LOT of small
> extents. The previous dba didn't really do much to keep the extent
> sizes uniform. We are using DMT and some the tables (and indexes for
> that matter) within the tablespaces have large, 200M, initial and next
> extents, and others have 56K extents. So our maps don't really have any
> kind of uniformity to them.
>
> With the snapshots, what can I look at to determine if these waits are
> a significant fraction of our time? What values should I compare?
>
> Finally, I know that this is such a broad span of things that could be
> wrong or could not be wrong. When it comes to tuning, I don't really
> have any experience. I am waiting on my copy of Cary Milsap's book on
> Tuning, are there any other resources that I should be looking at to
> teach myself how to get through these problems?
>
> Thanks,
>
> Ben
>



As a quick guide, look at the top five
timed events in the interval. It's not perfect
but it can give you a feel for time that might
be worth reclaiming.

Compare CPU time with the length of the
snapshot times the number of CPUs.

Compare the wait times with each other
to see if there is any wait that is particularly
responsible for lots of lost time.

Note - when I said check the p3 values,
this was intended to be specifically when
you see a wait called "buffer busy waits".
p3 = 130 is one of the commonest one
and means one session is waiting
for another to finish reading from disc,
p3=220 is another common one which
I think is the one you will see if you have
competing processes trying to update the buffer.


For a rapid entry to trouble-shooting, I think the best
book is probably the Richmond Shee et. al. on the
Oracle Wait Interface.

http://www.amazon.com/gp/product/007...lance&n=283155

Apart from that it's always worth reading the
Oracle manuals at http://tahiti.oracle.com .
possibly the Performance Tuning Guide as a
start in your case.


But as a CRITICAL guideline - don't look at
particular statistics as an abstract exercise.
Your numbers of buffer busy waits look bad -
but what was the time interval, and how much time
was spent (for example) waiting for disk reads
in the same interval. Don't look at numbers -
look for jobs which are taking too much time
and causing complaints.


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

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

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

Jonathan,
I've been running your see_space.sql script and before I run it on my
prod schema, I was wondering how big of an impact it has on the system.
Should I wait until off hours to run it?
Thanks
Ben

"Ben" <balvey@comcast.net> wrote in message
news:1144078276.420335.13990@i39g2000cwa.googlegro ups.com...
> Jonathan,
> I've been running your see_space.sql script and before I run it on my
> prod schema, I was wondering how big of an impact it has on the system.
> Should I wait until off hours to run it?
> Thanks
> Ben
>

I've just run a quick test on 9.2.
The procedure seems to walk the freelist
one block at a time. So if you think you
have lots of objects with very large freelists,
then you might want to wait for a quiet time
when the I/O load is low.

Alternative strategy:
select segment_name, owner, header_file, header_block
from dba_segments;

For each interesting segment

alter system dump datafile {header_file} {header_block};

then look at the trace file, which will have a section like:

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 511
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0240041a ext#: 3 blk#: 17 ext size: 128
#blocks in seg. hdr's freelists: 400
#blocks below: 400
mapblk 0x00000000 offset: 3
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 42458 flag: 0x40000000
Extent Map
-----------------------------------------------------------------

The line you want is:
#blocks in seg. hdr's freelists: 400

The presence of this line is what made me think
that the dbms_space call no longer walked the
list.



--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

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

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