Re: how to get correct info on freelists?

"Ben" <balvey_at_comcast.net> wrote in message news:1143749901.727869.109820_at_v46g2000cwv.googlegroups.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/007222729X/ref=pd_sim_b_4/104-1516922-1097512?%5Fencoding=UTF8&v=glance&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/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