Re: how to get correct info on freelists?

"Ben" <balvey_at_comcast.net> wrote in message news:1143734489.205450.222610_at_i40g2000cwc.googlegroups.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/community/oracle_ace/ace1.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