Re: how to get correct info on freelists?

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 Received on Thu Mar 30 2006 - 14:18:21 CST